MySQL Performance Tuning Guide: Architecture, System, MySQL Settings, and Application Optimizations

MySQL tuning can be approached from several aspects.

1. Architecture layer: Deploy read replicas to achieve read/write separation.

2. System layer: Increase server memory; configure disks with RAID0 or RAID5 to boost read/write speed; remount disks with the noatime option to reduce I/O overhead.

3. MySQL itself:

(1) If master‑slave replication is not needed, disable the binary log ( binlog ) to cut disk I/O.

(2) Add skip-name-resolve to my.cnf to avoid hostname resolution delays.

(3) Adjust key buffers and caches based on the server’s status variables; see the detailed parameter recommendations below.

4. Application layer: Review the slow‑query log and optimize SQL statements, such as adding appropriate indexes.

5. Key buffer and cache adjustments:

(1) key_buffer_size : Set according to total RAM (e.g., ≤1 GB → 128 MB; 2 GB → 256 MB; 4 GB → 384 MB; 8 GB → 1 GB; 16 GB → 2 GB). Verify with SHOW STATUS LIKE 'Key_read%' ; aim for a key_reads / key_read_requests ratio of ≤1:100, preferably 1:1000.

(2) table_open_cache : Start with 1024. Increase if Open_tables equals table_cache and Opened_tables keeps growing, but avoid setting it excessively high to prevent file‑descriptor exhaustion.

(3) sort_buffer_size : Per‑connection buffer for ORDER BY operations; on a 4 GB server, 4–8 MB is typical. Remember the total allocation equals sort_buffer_size × max_connections .

(4) read_buffer_size and (5) join_buffer_size : Also per‑connection buffers; size them according to workload and available memory.

(6) myisam_sort_buffer_size : Used during index creation or table repair; 64 MB is sufficient on a 4 GB machine.

(7) query_cache_size : Monitor with SHOW STATUS LIKE 'Qcache%' . If Qcache_lowmem_prunes is high, increase the cache; on a 4 GB server, 64 MB is usually enough.

(8) thread_cache_size : Reuse cached threads; typical values are 8 for 1 GB RAM, 16 for 2 GB, 32 for 3 GB, and 64 for >3 GB.

Additional important parameters:

(9) thread_concurrency : Set to twice the number of CPU cores.

(10) wait_timeout and interactive_timeout : Reduce both to around 10 seconds for idle connections.

(11) max_connect_errors : Increase (e.g., to 10 000) to avoid accidental lockouts; it is a security‑related counter, not a performance factor.

(12) max_connections : Adjust based on traffic; a value of 500 is often sufficient.

(13) max_user_connections : Set to 0 for unlimited or to a reasonable cap such as 100.