3 Aurora performance questions for Aurora database specialists

0

3 Aurora performance questions for Aurora database specialists:

  • For our production RDS MySQL instances, we were encouraged very strongly by one of AWS Principal Engineers to use provisioned IOPS instead of regular GP IOPS to ensure consistently good performance. Does Aurora also have a concept of different IOPS flavors, and if so which ones are appropriate for production workloads?

My thoughts: IOPS on Aurora is reported differently from standard RDS, as Aurora doesn’t use traditional block storage. Now i presume the primary instance must have some disk attached to it, but doubt that there is any provisioned IOPS, correct?

  • Some of our queries do complex math (haversine distance calculations, etc). What should we expect CPU-load-wise and performance-wise when executing these queries on Aurora vs stock MySQL?

My thoughts: i presume, given the de-coupled architecture of Aurora, that Aurora will perform better than standard MySQL RDs, but haven't been able to find any data to support this.

  • We want to test Aurora's performance under production load by adding an Aurora read replica underneath an existing MySQL primary. Something like this:
              Existing MySQL Primary
                    /     \
                   /       \
                  /         \ 
    Existing MySQL RR     New Aurora RR

Is a heterogeneous MySQL/Aurora mix of read replicas a good idea? What bad things might happen if we do this?

My thoughts: Given the way that Aurora is architected, this doesn’t seem like a good idea to me. Aurora is architected so that you have a primary instance that connects to shared storage. You can then add read replicas (up to 15) that connect to the same underlying shared storage. So, while I like the ‘out of the box’ thinking, I don’t think this really matches Aurora architectural patterns – but I will check and report back!

Any insights are much appreciated!

AWS
asked 5 years ago1462 views
1 Answer
0
Accepted Answer

For our production RDS MySQL instances, we were encouraged very strongly by one of AWS Principal Engineers to use provisioned IOPS instead of regular GP IOPS to ensure consistently good performance. Does Aurora also have a concept of different IOPS flavors, and if so which ones are appropriate for production workloads?

There are multiple considerations there, PIOPS will provide higher maximum achievable performance (IOPS) and better consistency in terms of performance. Since RDS storage is based on EBS, the characteristics of the volumes described here apply to RDS, too. But RDS will stripe larger storage sizes on multiple underlying EBS volumes. Also beware of this when customers use smaller instance types "* These instance types can support maximum performance for 30 minutes at least once every 24 hours. For example, c5.large instances can deliver 437.5 MB/s for 30 minutes at least once every 24 hours." - from: https://docs.aws.amazon.com/AWSEC2/latest/WindowsGuide/EBSOptimized.html

Aurora I/O is different, and can't be compared with block level I/O, writes are only redo logs, not full data pages, and we boxcar them, too. Reads are full data pages. In most cases the Aurora design is more beneficial from an I/O perspective. But there are exceptions - I/O heavy analytical queries that benefit from sequential block read operations on a filesystem, might be slower in Aurora.

Some of our queries do complex math (haversine distance calculations, etc). What should we expect CPU-load-wise and performance-wise when executing these queries on Aurora vs stock MySQL?

They will have to test in order to see what the performance is. There's no reliable way to anticipate. The degree to which data is cached in the buffer pool, will likely be the biggest influencer.

We want to test Aurora's performance under production load by adding an Aurora read replica underneath an existing MySQL primary. [...] Is a heterogeneous MySQL/Aurora mix of read replicas a good idea? What bad things might happen if we do this?

They can do that from the console. Amazon RDS support attaching an Aurora MySQL cluster as a managed read replica to an RDS MySQL DB instance. Attaching an additional binlog replica to a primary can have a performance impact on the primary. The impact is higher on MySQL 5.6 because of contention accessing the binlog between the reader and writer processes. The impact is decreased with 5.7.

From a testing under load perspective, binlogs will keep the Aurora replica up to date with changes, but won't reflect the concurrency of writes that happens on the primary. This may be a concern from a testing conditions perspective and the tests won't necessarily reflect the contention experienced in production.

AWS
EXPERT
answered 5 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions