Essential MySQL Performance Tuning: Key Metrics & Optimization Tips

There are many articles on configuring MySQL servers, but hardware differences require tailored optimization after the server has been running stably.

View MySQL configuration variables

show variables;

View MySQL status values

show global status;

1. Slow query

show variables like '%slow%';
show global status like '%slow%';

Analyze the slow‑query log, keep the slow‑query timeout under five seconds; for microsecond‑level logging consider patching MySQL (e.g., Percona).

2. Connection count

The error “MySQL: ERROR 1040: Too many connections” can arise from excessive traffic or a too‑low max_connections setting.

show variables like 'max_connections';

show global status like 'max_used_connections';

Ideal ratio: max_used_connections / max_connections × 100% ≈ 85%. Below 10% suggests the limit is set too high.

3. key_buffer_size

This parameter most affects MyISAM tables; InnoDB uses other buffers.

show variables like 'key_buffer_size';

show global status like 'key_read%';

Calculate cache miss rate: key_cache_miss_rate = Key_reads / Key_read_requests × 100%. A rate below 0.1% is good; below 0.01% may indicate oversized key_buffer_size.

4. key_blocks_* parameters

show global status like 'key_blocks_u%';

Key_blocks_unused – number of unused cache blocks.

Key_blocks_used – maximum number of blocks ever used.

Ideal ratio:

Key_blocks_used / (Key_blocks_unused + Key_blocks_used) × 100% ≈ 80%

.

5. Temporary tables

show global status like 'created_tmp%';

Metrics include Created_tmp_tables, Created_tmp_disk_tables, and Created_tmp_files. Ideal disk‑based temporary table ratio: Created_tmp_disk_tables / Created_tmp_tables × 100% ≤ 25%.

show variables where variable_name in('tmp_table_size','max_heap_table_size');

6. Open tables

show global status like 'open%tables%';

open_tables

counts currently open tables; opened_tables counts tables opened since startup. A high opened_tables may indicate a too‑small table_open_cache. show variables like 'table_open_cache'; Ideal: open_tables / opened_tables × 100% ≥ 85% Ideal:

open_tables / table_open_cache × 100% ≤ 95%

7. Thread usage

If thread_cache_size is set, idle client threads are cached. Monitor Threads_created to avoid excessive thread creation.

show global status like 'thread%';

show variables like 'thread_cache_size';

8. Query cache

show variables like 'query_cache%';

query_cache_limit – queries larger than this are not cached.

query_cache_min_res_unit – minimum allocation unit.

query_cache_size – total cache size.

query_cache_type – cache mode.

query_cache_wlock_invalidate – whether writes to MyISAM invalidate reads. show global status like 'qcache%'; Qcache_free_blocks – number of free memory blocks (fragmentation indicator).

Qcache_free_memory – free memory in cache.

Qcache_hits – cache hit count.

Qcache_inserts – number of queries inserted.

Qcache_lowmem_prunes – times cache was cleared due to low memory.

Qcache_not_cached – queries not eligible for caching.

Qcache_queries_in_cache – current cached queries.

Qcache_total_blocks – total blocks in cache.

9. Sort buffer

show global status like 'sort%';

Increasing sort_buffer_size can reduce sort merges and temporary files, but blind increases may not improve speed.

10. Open files

show global status like 'open_files';

show variables like 'open_files_limit';

Ideal ratio: open_files / open_files_limit × 100% ≤ 75% to avoid server stalls.