(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
