Mysql – Performance issues from RDS Aurora 5.6 to Aurora 5.7

amazon-rdsamazon-rds-auroramysql-5.7

Recently upgraded one of my RDS instances to aurora mysql 5.7 from 5.6 and I've had to triple the instance class while I try to resolve this (cpu eventually pegs and never declines).

Most reads are slow now (some about 30% slower, others are up to 400% slower). Looking at explain output, the queries are still using proper indexes. innodb_buffer_pool_size us set to default RDS value (instance class*3/24 I think) and most other innodb_* settings are not modifiable.

Here's an example Mysql5.6:

| count(*) |
+----------+
| 20646739 |
+----------+
1 row in set (0.06 sec)

Mysql5.7:

mysql> select count(*) from nope;                                                                                                                                                                    +----------+
| count(*) |
+----------+
| 20646739 |
+----------+
1 row in set (3.77 sec)

Anyone else encounter this that can provide some insight into some setting I can tweak?

Both instances are db.r3.2xlarge.

Best Answer

Rate Per Second = RPS

Suggestions to consider for your 5.7.12 Aurora Parameters group

innodb_write_io_threads=16  # from 4 to enable higher IOPS to your SSD device
innodb_lru_scan_depth=100  # from 1024 to conserve 90% of CPU cycles used for function
innodb_max_dirty_pages_pct_lwm=.0001  # from 0 to enable pre-flushing
innodb_max_dirty_pages_pct=.0001  # from 75 % dirty tolerated to reduce innodb_buffer_pool_pages_dirty count of 354,764 - will take hours
net_buffer_length=98304  # from 16384 to reduce count of interruptions for send/receive pkts
innodb_fast_shutdown=0  # from 1 to avoid recovery cycle on restart

This is just the beginning of your journey to improve performance. Thank you for the opportunity to serve your team.

Not all these Global Variables are dynamic. Will require instance stop/start.

Related Question