- Version: 5.7.30-0ubuntu0.18.04.1
- 16 GB of RAM -- Is this correct?
- Uptime = 1d 02:44:27
- You are not running on Windows.
- Running 64-bit version
- You appear to be running entirely (or mostly) InnoDB.
The More Important Issues:
How many tables do you have? Apparently
table_open_cache = 2000 is not high enough. Set it to 4000; then see if
Table_open_cache_overflows / Uptime and
Table_open_cache_misses / Uptime drop below 1 per second.
If you are using SSDs, increase
innodb_io_capacity to 500.
For production servers, it is usally better to turn OFF the Query Cache.
Use the slowlog to discover the "worst" queries. There seem to be some naughty queries.
Why are you doing
SHOW VARIABLES twice a second? And why
CREATE INDEX a hundred times in a day?
Even in the 26 hours that the server has been up, you have hit
max_connections (151). Can you explain why this is happening? Yes, that setting could be increased, but that could make things worse. So, we should try to get the root cause.
thread_cache_size (from 8) to 20. (I don't know the optimal number for your server, but apparently 8 is too low.)
Details and other observations:
( Table_open_cache_overflows ) = 135,369 / 96267 = 1.4 /sec
-- May need to increase table_open_cache (now 2000)
( Table_open_cache_misses ) = 137,377 / 96267 = 1.4 /sec
-- May need to increase table_open_cache (now 2000)
( 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)
( innodb_lru_scan_depth ) = 1,024
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth
( 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_flush_method ) = innodb_flush_method = -- 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_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.
( 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.
( character_set_server ) = character_set_server = latin1
-- Charset problems may be helped by setting character_set_server (now latin1) to utf8mb4. That is the future default.
( local_infile ) = local_infile = ON
-- local_infile (now ON) = ON is a potential security issue
( Qcache_lowmem_prunes ) = 1,739,291 / 96267 = 18 /sec -- Running out of room in QC
-- increase query_cache_size (now 16777216)
( Qcache_lowmem_prunes/Qcache_inserts ) = 1,739,291/5746238 = 30.3% -- Removal Ratio (frequency of needing to prune due to not enough memory)
( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (16M - 6135552) / 4715 / 8192 = 0.276 -- query_alloc_block_size vs formula
-- Adjust query_alloc_block_size (now 8192)
( Created_tmp_disk_tables ) = 226,136 / 96267 = 2.3 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs
-- increase tmp_table_size (now 16777216) and max_heap_table_size (now 16777216).
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.
( Com_show_variables ) = 200,230 / 96267 = 2.1 /sec -- SHOW VARIABLES ...
-- Why are you requesting the VARIABLES so often?
( Select_scan ) = 1,067,468 / 96267 = 11 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)
( Select_scan / Com_select ) = 1,067,468 / 5869892 = 18.2% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries
( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries. (5.1.12)
( long_query_time ) = 10 -- 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 ) = 80 -- (Autosized as of 5.6.6; based on max_connections)
-- Raising to min(150, max_connections (now 151)) may help when doing lots of connections.
( Max_used_connections / max_connections ) = 152 / 151 = 100.7% -- Peak % of connections
-- increase max_connections (now 151) and/or decrease wait_timeout (now 28800)
( Connections ) = 911,023 / 96267 = 9.5 /sec -- Connections
-- Increase wait_timeout (now 28800); use pooling?
Open_files = 0
Com_create_index = 4.2 /HR
Innodb_buffer_pool_pages_misc = 143,234
Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 27.3%
Innodb_os_log_pending_fsyncs = 1
external_user = root
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