Database Specialty - Aurora

Question 1

Aurora compatible relational database

Answer 1

MySQL and PostgreSQL

Question 1

Aurora faster standard MySQL

Answer 1

5x faster

Question 2

Aurora faster standard PostgreSQL

Answer 2

3x faster

Question 3

Aurora quantity copies maintains

Answer 3

6 copies across 3 AZs

Question 4

Fast backtracking option for…

Answer 4

PITR

Question 5

Aurora DB cluster types

Answer 5

Writer Endpoint (master) and Reader Endpoint (Connection Load Balancing)

Question 6

Number Max Endpoints custom

Answer 6

5

Question 7

Automatic failovers from the

Answer 7

master instance

Question 8

Aurora Serverless automatically…

Answer 8

starts up, shuts down, scales up/down based on application needs

Question 9

Aurora serverless results compared to RDS

Answer 9

40% lesser overall

Question 10

What happens in case of cluster or AZ failure?

Answer 10

Creates the DB instance in another AZ

Question 11

Data API types used for run queries

Answer 11

Query Editor within RDS console, Command Line, AWS SDK

Question 12

To access VPC resources in relation for Lambda

Answer 12

No need to configure

Question 13

Aurora Multi-Master - typically results in…

Answer 13

zero downtime

Question 14

Global Aurora - Cross Region Read Replicas points principal

Answer 14

• useful for disaster recovery
• simple to put in place
• replica promotion can take a few minutes depending on worload

Question 15

Aurora Global Databases (recommended) - points principal

Answer 15

• Primary Region (read / write)
• up to 5 secondary (read-only) regions
• up to 16 read replicas per secondary region
• helps for decreasing latency
• promoting another region (for DR)

Question 16

Reliability features in Aurora - explain storage Auto-Repair principal points

Answer 16

• automatically detects and repairs disk volume failures in the cluster volume
• quorum model ensure that is no data loss due to disk failures

Question 17

Principal points about Survivable Warming

Answer 17

• Aurora page cache is managed in a separate process from the DB
• Page cache stores pages for known common queries
• Every time Aurora starts/restarts, it preloads the buffer pool cache from the page cache
• Eliminates the need to warm up the buffer cache

Question 18

Principal points Crash Recovery

Answer 18

• Designed to recover from crashes almost instantaneously
• DOES NOT need replay the redo log from DB checkpoint
• DOES NOT need binary logs for replication within cluster or for PITR
• Binary logging on Aurora directly affects the recovery time after a crash
• Higher the binlog data size, longer it takes for crash recovery
• Disable binary logging (binlog_format=OFF) to reduce recovery time

Question 19

Principal Points in cluster cache management CCM

Answer 19

> Buffer cache is to reduce disk IO in RDBMS
Cached content in primary and replica may be different
Post a failover from primary to a replica, promoted replica takes some time to warm up its cache
this causes slower response time post failover
CCM improves the performance of the promoted instance post failover
Replica preemptively reads frequently accessed buffers cached from the primary

Question 20

Simulating Fault Tolerance in Aurora points

Answer 20

• Two ways to test/simulate fault tolerance (Manual failover, Fault injection queries)
• Fault tolerance is synonymous to resiliency (or fault resiliency)
• You can use these options to simulate AZ Failure
• Can perform primary upgrade by force failover

Question 21

Simulating fault tolerance w/ manua failover points

Answer 21

> Select the master instance and choose Actions -> Failover (or use failover-db-cluster command)
Failover to the replica with highest failover priority will be triggered
The read replica with highest failover priority will be the new master
The master instance that failed over will become a replica when it comes online
As each instance has its own endpoint address
So you should clean up and re-establish any existing connections that use the old endpoints post a failover

Question 22

Simulating fault tolerance w/ fault injection
queries points

Answer 22

• Fault injection queries are issued as SQL commands
• You can schedule a simulated occurrence of
  different failure events
• writer/reader crash
• replica failure
• disk failure
• disk congestion

Question 23

Fault injection queries – writer / reader crash
ALTER SYSTEM CRASH
[ INSTANCE | DISPATCHER | NODE ];

Answer 23

• Instance = DB instance (default crash type)
• Dispatcher = writes updates to the cluster volume
• Node = Instance + Dispatcher

Question 24

Fault injection queries – replica failure ALTER SYSTEM SIMULATE percentage_of_failure PERCENT READ REPLICA FAILURE [ TO ALL | TO "replica name" ] FOR INTERVAL quantity { YEAR | QUARTER | MONTH | WEEK | DAY | HOUR | MINUTE | SECOND };

Answer 24

* percentage_of_failure = % of requests to block * TO ALL / TO = simulate failure of all or a specific replica * quantity = duration of replica failure

Question 25

Fault injection queries – disk failure ALTER SYSTEM SIMULATE percentage_of_failure PERCENT DISK FAILURE [ IN DISK index | NODE index ] FOR INTERVAL quantity { YEAR | QUARTER | MONTH | WEEK | DAY | HOUR | MINUTE | SECOND };

Answer 25

* percentage_of_failure = % of the disk to mark as faulting * DISK index = simulate failure of a specific logical block of data * NODE index = simulate failure of a specific storage node * quantity = duration of disk failure

Question 26

Fault injection queries – disk congestion ALTER SYSTEM SIMULATE percentage_of_failure PERCENT DISK CONGESTION BETWEEN minimum AND maximum MILLISECONDS [ IN DISK index | NODE index ] FOR INTERVAL quantity { YEAR | QUARTER | MONTH | WEEK | DAY | HOUR | MINUTE | SECOND };

Answer 26

* percentage_of_failure = % of the disk to mark as congested * DISK index / NODE index = simulate failure of a specific disk or node * minimum / maximum = min and max amount of congestion delay in milliseconds (a random number between the two will be used) * quantity = duration of disk congestion

Question 27

Fast failover in Aurora PostgreSQL

Answer 27

* Use CCM (apg_ccm_enabled=1) * Use cluster / reader / custom endpoint (instead of instance endpoints) * Cleanup / re-establish connections, if using instance endpoints * Add automatic retry capability to the application * Aggressively set TCP keepalives (=low values) - Quickly closes active connections if client is no longer able to connect to the DB * Reduce Java DNS Cache timeout value (low value for DNS TTL) * Reader endpoint cycles through available readers. If a client caches DNS info, requests might go to the old instance until DNS cache times out * Use separate connection objects for long and short running queries * Use Aurora PostgreSQL connection string with multiple hosts

Question 28

Fast failover in Aurora PostgreSQL (contd.)

Answer 28

* Use a list of hosts in your JDBC connection string * Or maintain a file containing cluster endpoints (reader / writer) * Your application can read this file to populate the host section of the connection string * JDBC connection driver will loop through all nodes on this list to find a valid connection * Set connection parameters (in red) aggressively so your app doesn’t wait too long on any host * Alternatively, you can also maintain a file containing instance endpoints * When you add/remove nodes, you must update this file

Question 29

Cluster Replication Options for Aurora MySQL

Answer 29

* Replication between clusters = can have more than 15 read replicas * Replication * between two Aurora MySQL DB clusters in different regions (Cross-Region Replication) * between two Aurora MySQL DB clusters in same region * between RDS MySQL DB instance and an Aurora MySQL DB cluster

Question 30

Cluster Replication Options for Aurora MySQL Cross-Region Replication b/w two Aurora MySQL DB clusters

Answer 30

* enable binary logging (binlog_format parameter) * then, create a cross-region read replica in another region * you can promote the replica to a standalone DB cluster (typically, for DR purposes)

Question 31

Cluster Replication Options for Aurora MySQL Replication b/w two Aurora MySQL DB clusters in same region

Answer 31

* enable binary logging (binlog_format parameter) on source * then, replicate using a snapshot of the replication master

Question 32

Cluster Replication Options for Aurora Replication b/w RDS DB instance and an Aurora DB cluster

Answer 32

* By creating an Aurora read replica of RDS DB instance * Typically used for migration to Aurora rather than ongoing replication * To migrate, stop the writes on master. After replication lag is zero, promote the Aurora replica as a standalone Aurora DB cluster

Question 33

Invoke Lambda functions from Aurora MySQL

Answer 33

* Give Aurora MySQL access to Lambda by setting DB cluster parameter aws_default_lambda_role = IAM role ARN * Option 1 – Using mysql.lambda_async procedure (deprecated) * Wrap calls in a stored procedure and call through triggers or application code * Option 2 – Using native functions lambda_sync and lambda_async * User must have INVOKE LAMBDA privilege * GRANT INVOKE LAMBDA ON *.* TO user@host

Question 34

Load data from S3 into Aurora MySQL

Answer 34

* Use SQL statements * LOAD DATA FROM S3, or * LOAD XML FROM S3 * Must give the Aurora cluster access to S3 by setting DB cluster parameter * aurora_load_from_s3_role = IAM role ARN, or * aws_default_s3_role = IAM role ARN * User must have LOAD FROM S3 privilege * GRANT LOAD FROM S3 ON *.* TO user@host

Question 35

RDS / Aurora – Good things to know

Answer 35

* Stopping an RDS DB * Can stop an RDS instance only if it does not have a replica * Cannot stop an RDS replica * Cannot stop RDS for SQL Server DB instance if it’s in Multi-AZ * Stopping an Aurora DB * Can stop the cluster, not the individual instances * Cannot manually stop Aurora Serverless * Cannot stop Aurora Multi-Master or Aurora Global DB cluster * Cannot stop a cluster if it uses parallel query * Cannot delete a stopped cluster without starting if first * If you don't manually start your DB instance/Cluster after seven days, it will be automatically started

Question 36

RDS / Aurora – Good things to know 2

Answer 36

* Maximum Connections in RDS or Aurora is controlled via parameter groups * Each DB engine has a specified formula for the default max connections value * You can override this value using a custom parameter group * Ideally, you’d want to scale the instanceto get higher max connections