1

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.

11
  • Have you rebuilt the indexes or otherwise optimized the tables since the upgrade? More often than not, this is the key issue (for my systems) after an upgrade.
    – matigo
    May 1 '21 at 0:59
  • Which model are you using for Aurora 5.7? RAM and SSD or NVME for data storage? May 1 '21 at 20:07
  • @matigo Unfortunately no change in performance.
    – Kisaragi
    May 3 '21 at 17:13
  • 1
    @WilsonHauck Question updated to contain instance class, for 5.6 param group setting: pastebin.com/A8dsyFaT. For 5.7: pastebin.com/AJwgwHCK
    – Kisaragi
    May 3 '21 at 17:56
  • 1
    @WilsonHauck Global status output: pastebin.com/5YFmnzLv
    – Kisaragi
    May 6 '21 at 18:41
0

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.

1
  • Any progress? Will be available by phone only, rest of the day. Thanks, Wilson May 7 '21 at 16:21
0

(Assuming the STATUS was for 5.7)

Analysis of GLOBAL STATUS and VARIABLES:
 

Observations:

  • Version: 5.7.12-log
  • 61 GB of RAM
  • Uptime = 1d 09:08:40
  • You are not running on Windows.
  • Running 64-bit version
  • You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

table_open_cache 10000
innidb_io_capacity 1000   -- if using SSD drive
innodb_change_buffering = all  -- unless there is a reason for "none"
query_cache_size = 50M

The Query cache is inefficient when a lot of RAM is given to it. Aurora has solved this issue, but I don't know if RDS has. So, I suggest lowering the size.

There seem to be a large number of ROLLBACKs.

Details and other observations:

( Opened_tables ) = 2,084,463 / 119320 = 17 /sec -- Frequency of opening Tables -- increase table_open_cache (now 6000)

( table_open_cache ) = 6,000 -- Number of table descriptors to cache -- Several hundred is usually good.

( Table_open_cache_overflows ) = 2,078,458 / 119320 = 17 /sec -- May need to increase table_open_cache (now 6000)

( Table_open_cache_misses ) = 2,084,463 / 119320 = 17 /sec -- May need to increase table_open_cache (now 6000)

( Opened_tables / Uptime / table_open_cache_instances ) = 2,084,463 / 119320 / 16 = 1.09 -- A metric for table_open_cache_instances -- Increase table_open_cache_instances (now 16) to cut back on contention for the table_open_cache (now 6000).

( innodb_buffer_pool_size / innodb_buffer_pool_instances ) = 47683M / 8 = 5,960.4MB -- Size of each buffer_pool instance. -- An instance should be at least 1GB. In very large RAM, have 16 instances.

( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096 -- Amount of work for page cleaners every second. -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixable by lowering lru_scan_depth: Consider 1000 / innodb_page_cleaners (now 4). Also check for swapping.

( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 8 = 0.5 -- innodb_page_cleaners -- Recommend setting innodb_page_cleaners (now 4) to innodb_buffer_pool_instances (now 8) (Beginning to go away in 10.5)

( innodb_lru_scan_depth ) = 1,024 -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( innodb_io_capacity ) = 200 -- When flushing, use this many IOPs. -- Reads could be slugghish or spiky.

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 3,755,104 / 5871616 = 64.0% -- Pct of buffer_pool currently not in use -- innodb_buffer_pool_size (now 49999249408) is bigger than necessary?

( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10 -- Capacity: max/plain -- Recommend 2. Max should be about equal to the IOPs your I/O subsystem can handle. (If the drive type is unknown 2000/200 may be a reasonable pair.)

( innodb_change_buffering ) = innodb_change_buffering = none -- Pre-5.6.11 / 5.5.31, there was a bug that made ="changes" a safer option.

( innodb_doublewrite ) = innodb_doublewrite = OFF -- Extra I/O, but extra safety in crash. -- OFF is OK for FusionIO, Galera, Replicas, ZFS.

( Handler_rollback ) = 5,533,615 / 119320 = 46 /sec -- Why so many rollbacks?

( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk. -- Use 0 for SSD drives; 1 for HDD.

( innodb_io_capacity ) = 200 -- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor.

( Handler_rollback/Questions ) = 5,533,615/19923532 = 27.8% -- Rollbacks/query -- Why so many ROLLBACKs?

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks. -- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( max_connections ) = 3,000 -- Maximum number of connections (threads). Impacts various allocations. -- If max_connections (now 3000) is too high and various memory settings are high, you could run out of RAM.

( local_infile ) = local_infile = ON -- local_infile (now ON) = ON is a potential security issue

( query_cache_size ) = 2,460,864,512 = 2,346.9MB -- Size of QC -- Too small = not of much use. Too large = too much overhead. Recommend either 0 or no more than 50M.

( Qcache_hits / Qcache_inserts ) = 6,303,912 / 6590660 = 0.956 -- Hit to insert ratio -- high is good -- Consider turning off the query cache.

( Created_tmp_tables ) = 18,657,920 / 119320 = 156 /sec -- Frequency of creating "temp" tables as part of complex SELECTs.

( Created_tmp_disk_tables ) = 982,228 / 119320 = 8.2 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs Better indexes and reformulation of queries are likely to help.

( Created_tmp_disk_tables / Questions ) = 982,228 / 19923532 = 4.9% -- Pct of queries that needed on-disk tmp table. -- Better indexes / No blobs / etc.

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (634834 + 494921 + 71954 + 0) / 433212 = 2.77 -- Statements per Commit (assuming all InnoDB) -- Low: Might help to group queries together in transactions; High: long transactions strain various things.

( Select_full_join / Com_select ) = 1,285,085 / 9113463 = 14.1% -- % of selects that are indexless joins -- Add suitable index(es) to tables used in JOINs.

( Sort_merge_passes ) = 152,110 / 119320 = 1.3 /sec -- Heafty sorts -- Increase sort_buffer_size (now 262144) and/or optimize complex queries.

( Com__biggest ) = Com__biggest = Com_stmt_execute -- Which of the "Com_" metrics is biggest. -- Normally it is Com_select (now 9113463). If something else, then it may be a sloppy platform, or may be something else.

( relay_log_space_limit ) = 1,000,000,000 = 953.7MB -- The max total size for relay logs on a Replica. (0=unlimited) -- Let's discuss the rationale for having a limit.

( long_query_time ) = 25 -- Cutoff (Seconds) for defining a "slow" query. -- Suggest 2

( log_slow_slave_statements ) = log_slow_slave_statements = OFF -- (5.6.11, 5.7.1) By default, replicated statements won't show up in the slowlog; this causes them to show. -- It can be helpful in the slowlog to see writes that could be interfering with Replica reads.

( back_log ) = 16,000 -- (Autosized as of 5.6.6; based on max_connections) -- Raising to min(150, max_connections (now 3000)) may help when doing lots of connections.

( Max_used_connections / max_connections ) = 110 / 3000 = 3.7% -- Peak % of connections -- Since several memory factors can expand based on max_connections (now 3000), it is good not to have that setting too high.

( Connections ) = 2,266,422 / 119320 = 19 /sec -- Connections -- Increase wait_timeout (now 28800); use pooling?

Abnormally small:

Com_show_fields = 0
Com_show_tables = 0
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 0.0005
Innodb_buffer_pool_pages_misc = 0
Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 0
Innodb_data_fsyncs = 0
Innodb_data_reads = 0
Innodb_data_writes = 0
Innodb_data_writes - Innodb_log_writes - Innodb_dblwr_writes = 0
Innodb_data_written = 0
Innodb_dblwr_pages_written = 0
Innodb_log_write_requests = 0
Innodb_os_log_fsyncs = 0
Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group = 0
Open_files = 2
Table_locks_immediate = 2.7 /HR
innodb_online_alter_log_max_size = 128MB
innodb_sort_buffer_size = 1.05e+6
performance_schema_max_cond_classes = 0
performance_schema_max_digest_length = 0
performance_schema_max_file_classes = 0
performance_schema_max_file_handles = 0
performance_schema_max_mutex_classes = 0
performance_schema_max_rwlock_classes = 0
performance_schema_max_socket_classes = 0
performance_schema_max_stage_classes = 0
performance_schema_max_statement_classes = 0
performance_schema_max_thread_classes = 0
query_cache_limit = 1.05e+6

Abnormally large:

(query_cache_size - Qcache_free_memory) / query_cache_size = 99.7%
1 - Qcache_free_memory / query_cache_size = 99.7%
Com_call_procedure = 1.3 /sec
Com_create_trigger = 0.24 /HR
Com_delete_multi = 44 /HR
Com_do = 0.78 /sec
Com_drop_procedure = 0.03 /HR
Com_drop_trigger = 0.24 /HR
Com_flush = 29 /HR
Com_insert_select = 0.44 /sec
Com_insert_select + Com_replace_select = 1.1 /sec
Com_purge_before_date = 12 /HR
Com_replace_select = 0.7 /sec
Com_stmt_close = 136 /sec
Com_stmt_execute = 136 /sec
Com_stmt_prepare = 136 /sec
Com_update_multi = 0.87 /sec
Created_tmp_files = 0.58 /sec
Handler_read_key = 113933 /sec
Handler_read_next = 352797 /sec
Handler_write = 122405 /sec
Innodb_buffer_pool_bytes_data = 290621 /sec
Innodb_buffer_pool_pages_dirty = 354,764
Innodb_buffer_pool_pages_free = 3.76e+6
Innodb_buffer_pool_pages_total = 5.87e+6
Innodb_buffer_pool_read_requests = 1273844 /sec
Innodb_buffer_pool_write_requests / Innodb_buffer_pool_pages_flushed = 2,807
Innodb_rows_deleted + Innodb_rows_inserted = 495 /sec
Innodb_rows_inserted = 494 /sec
Open_tables = 5,984
Prepared_stmt_count = 5
Qcache_total_blocks = 4.47e+6
Qcache_total_blocks * query_cache_min_res_unit / Qcache_queries_in_cache = 37,784
Select_full_range_join = 0.46 /sec
Select_full_range_join / Com_select = 0.60%
Select_range_check = 50 /HR
back_log / max_connections = 533.3%
innodb_buffer_pool_chunk_size = 5,960.4MB
innodb_purge_batch_size = 1,800
innodb_stats_persistent_sample_pages = 128
server_audit_query_log_limit = 65,536
table_definition_cache = 20,000

Abnormal strings:

core_file = ON
gtid_mode = OFF_PERMISSIVE
innodb_checksums = OFF
innodb_fast_shutdown = 1
innodb_use_native_aio = OFF
opt_s__derived_merge = off
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
relay_log_recovery = ON
show_compatibility_56 = ON
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN
thread_handling = multiple-connections-per-thread

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.