Practical MySQL Optimization Guide: Philosophy, Tools, and Tuning Techniques

1. Introduction

MySQL tuning is often challenging for developers because the root causes of performance problems are not always clear; understanding the MySQL query execution process is essential for guiding the optimizer to choose efficient plans.

2. Optimization Philosophy

Risks: Optimization can affect complex production systems, may introduce new issues, and must be performed with caution.

Requirements: Stability and business continuity are usually more important than raw performance; optimization should be driven by business needs and involve cross‑department collaboration.

Participants: Database administrators, business representatives, application architects, developers, system and storage administrators should work together.

3. Optimization Ideas

What to optimize: Security (data sustainability) and performance (high‑speed data access).

Scope: Storage, host, OS, application, and database layers.

Dimensions: Hardware, system configuration, table structure, SQL and indexes.

Cost vs. Effectiveness: Hardware > system config > table structure > SQL > indexes (cost), while effectiveness is the reverse.

4. Optimization Tools

Database‑level tools (common):

mysql
mysqladmin
mysqlshow
show [SESSION|GLOBAL] variables
SHOW [SESSION|GLOBAL] STATUS
information_schema
SHOW ENGINE INNODB STATUS
SHOW PROCESSLIST
EXPLAIN
SHOW INDEX
slow‑log
mysqldumpslow

Less common but useful:

zabbix
pt‑query‑digest
mysqlslap
sysbench
mysql profiling
Performance Schema

4.2 Emergency Tuning Steps

1. SHOW PROCESSLIST
2. EXPLAIN SELECT id, name FROM stu WHERE name='clsn';
3. SELECT id, name FROM stu WHERE id=2-1;  -- function result > 30
4. SHOW INDEX FROM table;
5. SHOW STATUS LIKE '%lock%';
6. KILL SESSION_ID;

Regular tuning flow: Analyze slow‑log, prioritize slow queries, use EXPLAIN to examine execution plans, and adjust indexes or rewrite SQL.

4.3 System‑level Checks

CPU: vmstat, sar, top, htop, nmon, mpstat

Memory: free, ps -aux

IO/Network: iostat, ss, netstat, iptraf, iftop, lsof

Key vmstat fields – procs, memory, swap, io, system, cpu – help identify bottlenecks.

iostat examples: iostat -dk 1 5 , iostat -d -k -x 5

4.4 Load Analysis

Typical thresholds: CPU < 90% is acceptable. High CPU with low IO often points to memory shortage, disk performance, or SQL issues; high IO with low CPU suggests many small writes, autocommit, or inefficient queries.

5. Basic Optimization

5.1 Overall Approach

Identify problem layers in order: hardware → system → application → database → architecture (HA, read/write split, sharding).

Set clear performance vs. security trade‑offs.

5.2 Hardware Recommendations

Choose CPU cores and frequency based on workload (CPU‑intensive vs. IO‑intensive). Balance memory and disk, prefer SSD, configure RAID appropriately, and disable BBU on RAID cards.

5.3 Server Management

Use remote management cards (IPMI, iLO), monitor hardware health, and consider third‑party monitoring (SNMP, agents).

5.4 System Tuning

CPU tuning generally relies on proper hardware selection.

Memory tuning: avoid swap; set vm.swappiness=0 temporarily via /proc/sys/vm/swappiness or permanently in /etc/sysctl.conf .

IO scheduler: set elevator=deadline in GRUB.

5.5 Kernel Parameters

net.ipv4.ip_local_port_range = 1024 65535
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_fin_timeout = 30
fs.file-max = 65535
max_connections
max_connect_errors
connect_timeout
max_user_connections
skip-name-resolve
wait_timeout
back_log

5.6 SQL Layer

Adjust query cache size for OLAP workloads, but remember it can be invalidated by frequent writes; consider external caches like Redis or Memcached.

6. Database Optimization

6.1 Parameter Tuning (Instance Level)

thread_concurrency
sort_buffer_size
read_buffer_size
read_rnd_buffer_size
key_buffer_size
thread_cache_size

6.2 Connection Settings

max_connections
max_connect_errors
connect_timeout
max_user_connections
skip-name-resolve
wait_timeout
back_log

6.3 Storage Engine (InnoDB) Settings

innodb_buffer_pool_size   # ~50% of RAM, not exceeding 70%
innodb_file_per_table = 1|0
innodb_flush_log_at_trx_commit = 0|1|2
innodb_flush_method = O_DIRECT|fdatasync
innodb_log_buffer_size   # < 100M
innodb_log_file_size      # < 100M
innodb_log_files_in_group = 2-3
innodb_max_dirty_pages_pct = 75
log_bin
max_binlog_cache_size
max_binlog_size
innodb_additional_mem_pool_size = 20M (≤2G RAM) or 100M (>32G RAM)

These settings should be tuned according to the server’s hardware capacity and workload characteristics.

Conclusion

Effective MySQL optimization requires a holistic view of hardware, OS, application, and database layers, careful selection of tools, and systematic adjustment of configuration parameters while continuously monitoring performance metrics.