Practical MySQL Performance Tuning Using Status Variables

MySQL performance tuning requires analyzing runtime status and configuration variables rather than applying generic settings, because results vary with memory, workload, and data characteristics. Use SHOW GLOBAL STATUS; and SHOW VARIABLES; to gather metrics before adjusting parameters.

1. Slow Query Log

Enable slow query logging with a threshold of 2 seconds (or lower for micro‑second precision via Percona patches). The server shows 4,148 slow queries. Keep the threshold under 5 seconds; a higher value reduces usefulness.

2. Connection Count

Check max_connections (e.g., 256) and the peak usage via SHOW GLOBAL STATUS LIKE 'Max_used_connections';. A healthy utilization is around 85% of the limit; significantly lower usage suggests the limit is set too high.

3. Key Buffer Size (MyISAM)

For MyISAM tables, key_buffer_size is critical. Example: 512 MB allocated, with 27,813,678,764 read requests and 6,798,830 misses, yielding a cache miss rate of 0.0244% (well below 0.1%). Monitor key_blocks_unused and key_blocks_used; aim for about 80% usage.

4. Temporary Tables

Inspect Created_tmp_tables, Created_tmp_disk_tables, and Created_tmp_files. Keep the proportion of disk‑based temporary tables below 25% (e.g., 1.2% in the example). Adjust tmp_table_size and max_heap_table_size so that most temporary tables fit in memory (≤256 MB).

5. Open Tables

Metrics Open_tables and Opened_tables indicate table cache adequacy. Desired ratios: Open_tables / Opened_tables ≥ 85% and Open_tables / table_cache ≤ 95%. In the sample, table_cache is 2048.

6. Thread Cache

Review Threads_cached, Threads_created, etc. A high Threads_created count suggests increasing thread_cache_size (example value 64).

7. Query Cache

Query cache variables ( Qcache_*) reveal free blocks, memory, hits, inserts, low‑memory prunes, and overall utilization. Example metrics: 20.46% fragment rate, 62.26% utilization, 1.94% hit rate. Adjust query_cache_size, query_cache_min_res_unit, and consider flushing the cache if fragmentation exceeds 20%.

8. Sorting

Sort statistics ( Sort_merge_passes, Sort_range, etc.) show how often MySQL resorts to temporary files. Increase sort_buffer_size (and optionally read_rnd_buffer_size) to reduce merge passes, but avoid excessive memory allocation.

9. Open Files

Monitor Open_files versus open_files_limit. Keep usage ≤75% of the limit (example: 1,410 of 4,590).

10. Table Locks

Check Table_locks_immediate and Table_locks_waited. A ratio >5000 favors InnoDB (row‑level locking). In the example the ratio is 235, so MyISAM is acceptable.

11. Table Scans

Handler read counters and Com_select allow calculation of the table‑scan rate: Handler_read_rnd_next / Com_select. Values >4000 suggest missing indexes; increasing read_buffer_size can help, but keep it ≤8 MB.

By regularly checking these status variables and adjusting the corresponding configuration parameters, administrators can keep a MySQL server healthy and performant.