Analysis of GLOBAL STATUS and VARIABLES:
Observations:
- Version: 10.3.32-MariaDB-0ubuntu0.20.04.1-log
- 16 GB of RAM
- Uptime = 3d 05:41:50
- 42.1 QPS
The More Important Issues:
Some setting suggestions for better memory utilization:
key_buffer_size = 20M
innodb_buffer_pool_size = 8G
table_open_cache = 300
innodb_open_files = 1000
query_cache_type = OFF
query_cache_size = 0
Some setting suggestions for other reasons:
eq_range_index_dive_limit = 20
log_queries_not_using_indexes = OFF
Recommend using the slowlog (with long_query_time = 1) to locate the naughty queries. Then we can discuss how to improve them.
http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog
Details and other observations:
( (key_buffer_size - 1.2 * Key_blocks_used * 1024) ) = ((512M - 1.2 * 8 * 1024)) / 16384M = 3.1% -- Percent of RAM wasted in key_buffer.
-- Decrease key_buffer_size (now 536870912).
( Key_blocks_used * 1024 / key_buffer_size ) = 8 * 1024 / 512M = 0.00% -- Percent of key_buffer used. High-water-mark.
-- Lower key_buffer_size (now 536870912) to avoid unnecessary memory usage.
( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((512M / 0.20 + 128M / 0.70)) / 16384M = 16.7% -- Most of available ram should be made available for caching.
-- http://mysql.rjweb.org/doc.php/memory
( table_open_cache ) = 16,293 -- Number of table descriptors to cache
-- Several hundred is usually good.
( innodb_buffer_pool_size ) = 128M -- InnoDB Data + Index cache
-- 128M (an old default) is woefully small.
( innodb_buffer_pool_size ) = 128 / 16384M = 0.78% -- % 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 ) = 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_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_buffer_pool_reads / Innodb_buffer_pool_read_requests ) = 3,565,561,783 / 162903322931 = 2.2% -- Read requests that had to hit disk
-- Increase innodb_buffer_pool_size (now 134217728) if you have enough RAM.
( Innodb_pages_read / Innodb_buffer_pool_read_requests ) = 3,602,479,499 / 162903322931 = 2.2% -- Read requests that had to hit disk
-- Increase innodb_buffer_pool_size (now 134217728) if you have enough RAM.
( Innodb_buffer_pool_reads ) = 3,565,561,783 / 279710 = 12747 /sec -- Cache misses in the buffer_pool.
-- Increase innodb_buffer_pool_size (now 134217728)? (~100 is limit for HDD, ~1000 is limit for SSDs.)
( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((3565561783 + 1105583) ) / 279710 = 12751 /sec -- InnoDB I/O
-- Increase innodb_buffer_pool_size (now 134217728)?
( Innodb_buffer_pool_read_ahead_evicted ) = 5,386,209 / 279710 = 19 /sec
( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 1,564,913,152 / (279710 / 3600) / 2 / 48M = 0.2 -- Ratio
-- (see minutes)
( innodb_flush_method ) = innodb_flush_method = 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
( default_tmp_storage_engine ) = default_tmp_storage_engine =
( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk.
-- Use 0 for SSD drives; 1 for HDD.
( ( Innodb_pages_read + Innodb_pages_written ) / Uptime / innodb_io_capacity ) = ( 3602479499 + 1115984 ) / 279710 / 200 = 6441.7% -- If > 100%, need more io_capacity.
-- Increase innodb_io_capacity (now 200) if the drives can handle it.
( 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_adaptive_hash_index ) = innodb_adaptive_hash_index = ON -- Whether to use the adapative hash (AHI).
-- ON for mostly readonly; OFF for DDL-heavy
( 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.
( innodb_ft_result_cache_limit ) = 2,000,000,000 / 16384M = 11.6% -- Byte limit on FULLTEXT resultset. (Possibly not preallocated, but grows?)
-- Lower the setting.
( local_infile ) = local_infile = ON
-- local_infile (now ON) = ON is a potential security issue
( Qcache_lowmem_prunes ) = 6,329,393 / 279710 = 23 /sec -- Running out of room in QC
-- increase query_cache_size (now 16777216)
( Qcache_lowmem_prunes/Qcache_inserts ) = 6,329,393/7792821 = 81.2% -- Removal Ratio (frequency of needing to prune due to not enough memory)
( Qcache_hits / Qcache_inserts ) = 1,619,341 / 7792821 = 0.208 -- Hit to insert ratio -- high is good
-- Consider turning off the query cache.
( Qcache_hits / (Qcache_hits + Com_select) ) = 1,619,341 / (1619341 + 9691638) = 14.3% -- Hit ratio -- SELECTs that used QC
-- Consider turning off the query cache.
( Qcache_hits / (Qcache_hits + Qcache_inserts + Qcache_not_cached) ) = 1,619,341 / (1619341 + 7792821 + 278272) = 16.7% -- Query cache hit rate
-- Probably best to turn off the QC.
( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (16M - 1272984) / 3058 / 16384 = 0.309 -- query_alloc_block_size vs formula
-- Adjust query_alloc_block_size (now 16384)
( Created_tmp_disk_tables ) = 1,667,989 / 279710 = 6 /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.
( Created_tmp_disk_tables / Questions ) = 1,667,989 / 11788712 = 14.1% -- Pct of queries that needed on-disk tmp table.
-- Better indexes / No blobs / etc.
( Created_tmp_disk_tables / Created_tmp_tables ) = 1,667,989 / 4165525 = 40.0% -- Percent of temp tables that spilled to disk
-- Maybe increase tmp_table_size (now 16777216) and max_heap_table_size (now 16777216); improve indexes; avoid blobs, etc.
( ( Com_stmt_prepare - Com_stmt_close ) / ( Com_stmt_prepare + Com_stmt_close ) ) = ( 473 - 0 ) / ( 473 + 0 ) = 100.0% -- Are you closing your prepared statements?
-- Add Closes.
( Com_stmt_close / Com_stmt_prepare ) = 0 / 473 = 0 -- Prepared statements should be Closed.
-- Check whether all Prepared statements are "Closed".
( binlog_format ) = binlog_format = MIXED -- STATEMENT/ROW/MIXED.
-- ROW is preferred by 5.7 (10.3)
( long_query_time ) = 5 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2
( Subquery_cache_hit / ( Subquery_cache_hit + Subquery_cache_miss ) ) = 0 / ( 0 + 1800 ) = 0 -- Subquery cache hit rate
( log_queries_not_using_indexes ) = log_queries_not_using_indexes = ON -- Whether to include such in slowlog.
-- This clutters the slowlog; turn it off so you can see the real slow queries. And decrease long_query_time (now 5) to catch most interesting queries.
( 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.
Abnormally small:
Delete_scan = 0.039 /HR
Handler_read_rnd_next / Handler_read_rnd = 2.06
Handler_write = 0.059 /sec
Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads ) = 97.9%
Table_locks_immediate = 2.6 /HR
eq_range_index_dive_limit = 0
Abnormally large:
( Innodb_pages_read + Innodb_pages_written ) / Uptime = 12,883
Com_release_savepoint = 5.5 /HR
Com_savepoint = 5.5 /HR
Handler_icp_attempts = 110666 /sec
Handler_icp_match = 110663 /sec
Handler_read_key = 62677 /sec
Handler_savepoint = 5.5 /HR
Handler_tmp_update = 1026 /sec
Handler_tmp_write = 40335 /sec
Innodb_buffer_pool_read_ahead = 131 /sec
Innodb_buffer_pool_reads * innodb_page_size / innodb_buffer_pool_size = 43524924.1%
Innodb_data_read = 211015030 /sec
Innodb_data_reads = 12879 /sec
Innodb_pages_read = 12879 /sec
Innodb_pages_read + Innodb_pages_written = 12883 /sec
Select_full_range_join = 1.1 /sec
Select_full_range_join / Com_select = 3.0%
Tc_log_page_size = 4,096
innodb_open_files = 16,293
log_slow_rate_limit = 1,000
query_cache_limit = 3.36e+7
table_open_cache / max_connections = 107
Abnormal strings:
Innodb_have_snappy = ON
Slave_heartbeat_period = 0
Slave_received_heartbeats = 0
aria_recover_options = BACKUP,QUICK
innodb_fast_shutdown = 1
log_output = FILE,TABLE
log_slow_admin_statements = ON
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT
sql_slave_skip_counter = 0
time_zone = +05:30
