Analysis of GLOBAL STATUS and VARIABLES:
Observations:
- Version: 5.7.42-log
- 8 GB of RAM
- Uptime = 23:29:29; some GLOBAL STATUS values may not be meaningful yet.
- 813 QPS
The More Important Issues:
Suggested configuration changes:
table_open_cache = 5000
key_buffer_size = 20M
long_query_time = 1.0 -- and turn on the slowlog
What is going on with "savepoints"?
7.2 CREATE INDEXes per hour -- Why so often?
Handler_write = 12860 /sec -- Lots of temp table for complex queries?
How much disk space do the database(s) occupy? Are there other apps running and occupying memory?
24% ROLLBACK seems rather high. What's up?
Use the slolog to locate the queries using tmp disk tables and other things flagged below.
SlowLog
If changing max_heap_table_size and tmp_table_size caused memory to go up, then lower those values. But, also, the slowlog should help us find which queries are using those settings and maybe better indexes or query reformulation will decrease the temp table usage.
Details and other observations:
( Key_blocks_used * 1024 / key_buffer_size ) = 3 * 1024 / 128M = 0.00% -- Percent of key_buffer used. High-water-mark.
-- Lower key_buffer_size (now 134217728) to avoid unnecessary memory usage.
( Opened_tables ) = 9812983 / 84569 = 116 /sec -- Frequency of opening Tables
-- increase table_open_cache (now 2395)
( Table_open_cache_overflows ) = 9788244 / 84569 = 115 /sec
-- May need to increase table_open_cache (now 2395)
( Table_open_cache_misses ) = 9812766 / 84569 = 116 /sec
-- May need to increase table_open_cache (now 2395)
( Opened_tables / Uptime / table_open_cache_instances ) = 9,812,983 / 84569 / 16 = 7.25 -- 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 2395).
( Table_open_cache_misses / (Table_open_cache_hits + Table_open_cache_misses) ) = 9,812,766 / (69719036 + 9812766) = 12.3% -- Effectiveness of table_open_cache.
-- Increase table_open_cache (now 2395) and check table_open_cache_instances (now 16).
( innodb_buffer_pool_size ) = 3,072 / 8192M = 37.5% -- % of RAM used for InnoDB buffer_pool
-- Set to about 70% of available RAM. (To low is less efficient; too high risks swapping.)
( 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_lru_scan_depth ) = 1,024 -- innodb_lru_scan_depth is a very poorly named variable. A better name would be innodb_free_page_target_per_buffer_pool. It is a number of pages InnoDB tries to keep free in each buffer pool instance to speed up read and page creation operations.
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth
( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((2106801 + 9919043) ) / 84569 = 142 /sec -- InnoDB I/O
-- Increase innodb_buffer_pool_size (now 3221225472)?
( Innodb_buffer_pool_pages_flushed ) = 9919043 / 84569 = 117 /sec -- Writes (flushes)
-- Increase innodb_buffer_pool_size (now 3221225472)?
( Innodb_log_writes ) = 4002464 / 84569 = 47 /sec
( innodb_flush_method ) = innodb_flush_method = O_DIRECT_NO_FSYNC -- How InnoDB should ask the OS to write blocks. Suggest O_DIRECT or O_ALL_DIRECT (Percona) to avoid double buffering. (At least for Unix.) See chrischandler for caveat about O_ALL_DIRECT
( Innodb_dblwr_writes ) = 456541 / 84569 = 5.4 /sec -- "Doublewrite buffer" writes to disk. "Doublewrites" are a reliability feature. Some newer versions / configurations don't need them.
-- (Symptom of other issues)
( innodb_flush_neighbors ) = innodb_flush_neighbors = 1 -- A minor optimization when writing blocks to disk.
-- Use 0 for SSD drives; 1 for HDD.
( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON -- Whether to use the adapative hash (AHI).
-- ON for mostly readonly; OFF for DDL-heavy
( innodb_flush_log_at_trx_commit ) = 2 -- 1 = secure; 2 = faster
-- (You decide) Use 1, along with sync_binlog (now 1)=1 for the greatest level of fault tolerance. 0 is best for speed. 2 is a compromise between 0 and 1.
( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON -- Usually should be ON.
-- There are cases where OFF is better. See also innodb_adaptive_hash_index_parts (now 8) (after 5.7.9) and innodb_adaptive_hash_index_partitions (MariaDB and Percona). ON has been implicated in rare crashes (bug 73890). 10.5.0 decided to default OFF.
( 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.
( min( tmp_table_size, max_heap_table_size ) ) = (min( 82M, 82M )) / 8192M = 1.0% -- Percent of RAM to allocate when needing MEMORY table (per table), or temp table inside a SELECT (per temp table per some SELECTs). Too high may lead to swapping.
-- Decrease tmp_table_size (now 85983232) and max_heap_table_size (now 85983232) to, say, 1% of ram.
( innodb_ft_result_cache_limit ) = 2,000,000,000 / 8192M = 23.3% -- Byte limit on FULLTEXT resultset. (It grows as needed.)
-- Lower the setting.
( local_infile ) = local_infile = ON
-- local_infile (now ON) = ON is a potential security issue
( Created_tmp_tables ) = 5555402 / 84569 = 66 /sec -- Frequency of creating "temp" tables as part of complex SELECTs.
( Created_tmp_disk_tables ) = 1101804 / 84569 = 13 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs
-- increase tmp_table_size (now 85983232) and max_heap_table_size (now 85983232).
Check the rules for temp tables on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM.
Better indexes and reformulation of queries are more likely to help.
( tmp_table_size ) = 82M -- Limit on size of MEMORY temp tables used to support a SELECT
-- Decrease tmp_table_size (now 85983232) to avoid running out of RAM. Perhaps no more than 64M.
( Com_rollback / (Com_commit + Com_rollback) ) = 10,962 / (34366 + 10962) = 24.2% -- Rollback : Commit ratio
-- Rollbacks are costly; change app logic
( Com_show_variables ) = 147376 / 84569 = 1.7 /sec -- SHOW VARIABLES ...
-- Why are you requesting the VARIABLES so often?
( Sort_merge_passes ) = 35299 / 84569 = 0.42 /sec -- Heafty sorts
-- Increase sort_buffer_size (now 262144) and/or optimize complex queries.
( long_query_time ) = 5 -- 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 ) = 90 -- (Autosized as of 5.6.6; based on max_connections)
-- Raising to min(150, max_connections (now 200)) may help when doing lots of connections.
( Aborted_connects ) = 19,728 / 84569 = 0.23 /sec -- Could not get a connection, or hacker ? (Attempts to connect)
-- Raise max_connections (now 200)
( Connections ) = 436748 / 84569 = 5.2 /sec -- Connections
-- Increase wait_timeout (now 600); use pooling?
Abnormally small:
Open_files = 1
interactive_timeout = 600
Abnormally large:
Com_create_index = 7.2 /HR
Com_release_savepoint = 1 /HR
Com_rollback_to_savepoint = 0.095 /sec
Com_savepoint = 1 /HR
Com_show_create_db = 1 /HR
Com_show_create_func = 0.94 /HR
Com_show_create_trigger = 0.085 /HR
Com_show_events = 1 /HR
Com_show_table_status = 0.095 /sec
Com_show_triggers = 0.095 /sec
Created_tmp_files = 0.83 /sec
Handler_savepoint = 1 /HR
Handler_savepoint_rollback = 0.095 /sec
Handler_write = 12860 /sec
Open_table_definitions = 3,798
Performance_schema_digest_lost = 455,163
Select_range_check = 30 /HR
Ssl_session_cache_misses = 1
Abnormal strings:
innodb_fast_shutdown = 1
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN
sql_slave_skip_counter = 0
