Opened_tables (etc) is useless without dividing by Uptime. I recommend no more than 2/sec. Perhaps 1/sec for Opened_table_definitions/Uptime. Those are under the control of these:
mysql> SHOW VARIABLES LIKE 'table%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| table_definition_cache | 1500 |
| table_open_cache | 1555 |
| table_open_cache_instances | 4 |
+----------------------------+-------+
And rethink the wisdom of having so many databases and so many tables.
open_files_limit and the OS limit are also involved.
Table_open_cache_hits and Table_open_cache_misses and Table_open_cache_overflows are useful to watch. Up to 1 miss or overflow/sec might be a good goal.
When increasing cache(s), be sure not to use up so much RAM that swapping occurs. If swapping does occur, the quick fix is to decrease innodb_buffer_pool_size a little.
If you would like more analysis, please provide the info requested here .
Tuning Analysis
Observations:
- Version: 5.7.20-0ubuntu0.16.04.1-log
- 16 GB of RAM
- Uptime = 21d 01:09:23
- You are not running on Windows.
- Running 64-bit version
- You appear to be running entirely (or mostly) InnoDB.
The More Important Issues:
Really bad: query_cache_size should not be more than about 50M. 2GB leads to a lot of overhead in purging entries. Consider turning off the QC.
Since MyISAM is not used much, decrease key_buffer_size to 50M.
open_file_limit is only 1024. This is probably because Ubuntu is restricting
it to 1024 -- see ulimit, and raise it. After that, increase table_open_cache to
at least 5K, and a few others.
innodb_buffer_pool_size (10G) is probably at a good size. I deduce that you have less than tha much data.
innodb_log_file_size is quite high for the amount of activity you have.
But it is not important to change it.
Why is SHOW CREATE TABLE (and similar things) being run so many times?
Why so many ALTER TABLEs?
It seems that a lot of SELECTs are inefficient. See this for what to do next.
LOCK TABLES and UNLOCK TABLES should rarely, if ever, be used with InnoDB. What is going on?
Details and other observations:
( (key_buffer_size - 1.2 * Key_blocks_used * 1024) / _ram ) = (512M - 1.2 * 32 * 1024) / 16384M = 3.1% -- Percent of RAM wasted in key_buffer.
-- Decrease key_buffer_size.
( Key_blocks_used * 1024 / key_buffer_size ) = 32 * 1024 / 512M = 0.01% -- Percent of key_buffer used. High-water-mark.
-- Lower key_buffer_size to avoid unnecessary memory usage.
( Opened_tables ) = 10,163,367 / 1818563 = 5.6 /sec -- Frequency of opening Tables
-- increase table_open_cache
( Opened_table_definitions ) = 2,479,464 / 1818563 = 1.4 /sec -- Frequency of opening .frm files
-- Increase table_definition_cache and/or table_open_cache.
( open_files_limit ) = 1,024 -- ulimit -n
-- To allow more files, change ulimit or /etc/security/limits.conf or in sysctl.conf (kern.maxfiles & kern.maxfilesperproc) or something else (OS dependent)
( Table_open_cache_overflows ) = 10,109,150 / 1818563 = 5.6 /sec
-- May need to increase table_open_cache
( Table_open_cache_misses ) = 10,163,360 / 1818563 = 5.6 /sec
-- May need to increase table_open_cache
( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 132,895 / 655280 = 20.3% -- Pct of buffer_pool currently not in use
-- innodb_buffer_pool_size to bigger than necessary?
( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 10,751,949,312 / (1818563 / 3600) / 2 / 256M = 0.0396 -- Ratio
-- (see minutes)
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 1,818,563 / 60 * 256M / 10751949312 = 756 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size. (Cannot change in AWS.)
( 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.
( (Com_show_create_table + Com_show_fields) / Questions ) = (1137006 + 1592424) / 249138897 = 1.1% -- Naughty framework -- spending a lot of effort rediscovering the schema.
-- Complain to the 3rd party vendor.
( local_infile ) = ON
-- local_infile = ON is a potential security issue
( query_cache_size ) = 2048M -- Size of QC
-- Too small = not of much use. Too large = too much overhead. Recommend either 0 or no more than 50M.
( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (2048M - 1143770840) / 560588 / 8192 = 0.219 -- query_alloc_block_size vs formula
-- Adjust query_alloc_block_size
( Created_tmp_disk_tables ) = 2,468,770 / 1818563 = 1.4 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs
-- increase tmp_table_size and max_heap_table_size.
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.
( Created_tmp_disk_tables / (Created_tmp_disk_tables + Created_tmp_tables) ) = 2,468,770 / (2468770 + 4016418) = 38.1% -- Percent of temp tables that spilled to disk
-- maybe increase tmp_table_size and max_heap_table_size; avoid blobs, etc.
( Com_rollback / Com_commit ) = 2,444 / 8577 = 28.5% -- Rollback : Commit ratio
-- Rollbacks are costly; change app logic
( Select_scan ) = 5,519,376 / 1818563 = 3 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)
( Select_scan / Com_select ) = 5,519,376 / 21974063 = 25.1% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries
( Com_alter_table ) = 85,985 / 1818563 = 0.047 /sec
-- Why so many ALTERs?
( back_log / max_connections ) = 92 / 214 = 43.0%
( Connections ) = 10,172,085 / 1818563 = 5.6 /sec -- Connections
-- Increase wait_timeout; use pooling?
( thread_cache_size ) = 100 -- How many extra processes to keep around (Not relevant when using thread pooling) (Autosized as of 5.6.8; based on max_connections)
-- 0 is good for Windows
0 is inefficient for non-Windows; 10 is probably fine
( thread_cache_size / max_connections ) = 100 / 214 = 46.7%
Abnormally large:
Com_create_db = 0.35 /HR
Com_create_table = 85 /HR
Com_create_trigger = 19 /HR
Com_create_user = 0.34 /HR
Com_drop_table = 85 /HR
Com_drop_user = 0.014 /HR
Com_grant = 0.34 /HR
Com_release_savepoint = 3.6 /HR
Com_rollback_to_savepoint = 0.25 /sec
Com_savepoint = 3.6 /HR
Com_show_create_db = 3.6 /HR
Com_show_create_func = 0.96 /HR
Com_show_create_table = 0.63 /sec
Com_show_create_trigger = 0.054 /sec
Com_show_events = 3.6 /HR
Com_show_fields = 0.88 /sec
Com_show_function_status = 3.6 /HR
Com_show_privileges = 0.002 /HR
Com_show_procedure_status = 3.6 /HR
Com_show_table_status = 0.25 /sec
Com_show_triggers = 0.25 /sec
Com_unlock_tables = 89 /HR
Handler_savepoint = 3.6 /HR
Handler_savepoint_rollback = 0.25 /sec
Performance_schema_digest_lost = 2.56e+7
Qcache_free_blocks = 342,300
Qcache_free_memory = 1.14e+9
Qcache_total_blocks = 1.49e+6
Select_full_range_join = 55,471
Threads_cached = 93
eq_range_index_dive_limit = 200
group_concat_max_len = 1.0e+6
innodb_purge_threads = 4
performance_schema_max_file_classes = 80
performance_schema_max_mutex_classes = 210
performance_schema_max_statement_classes = 193
query_cache_limit = 1.05e+8
Abnormal string values:
innodb_force_load_corrupted = OFF
innodb_undo_directory = ./
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-01.