Comprehensive Guide to MySQL Optimization: Philosophy, Tools, and Practical Steps

Before optimizing MySQL, understand its query execution process; most optimization work follows principles that guide the optimizer to behave as expected.

Optimization Philosophy – Optimization carries risk; changes must be made carefully. Problems may arise from complex production systems, and any solution can introduce new issues. Success means keeping problems within acceptable limits; maintaining the status quo or degrading performance is considered a failure.

Optimization Requirements – Stability and business continuity usually outweigh raw performance. Optimization inevitably involves changes, which bring risk. Performance improvements are probabilistic, and cross‑department collaboration is essential.

Who Should Participate – Database administrators, business representatives, application architects, developers, system and storage administrators should work together on database tuning.

Optimization Scope

Security: data sustainability.

Performance: high‑speed data access.

Optimization Dimensions

Hardware

System configuration

Database schema

SQL and indexes

Toolset (Database Layer)

MySQL client utilities: mysqladmin, mysqlshow SHOW SESSION/ GLOBAL variables & STATUS

information_schema

SHOW ENGINE INNODB STATUS

SHOW PROCESSLIST

EXPLAIN

SHOW INDEX

slow‑log and mysqldumpslow Additional Useful Tools

Zabbix (monitoring)

pt‑query‑digest, MySQL‑slap (slow‑log analysis)

sysbench (stress testing)

Performance Schema, MySQL profiling, Workbench

Emergency Tuning Steps

1) SHOW PROCESSLIST
2) EXPLAIN SELECT id, name FROM stu WHERE name='clsn';
3) SHOW INDEX FROM table;
4) SHOW STATUS LIKE '%lock%';
5) KILL SESSION_ID;

Regular Tuning Steps

1) Review slow‑log and identify slow queries.
2) Prioritize and investigate each slow query.
3) Use EXPLAIN to analyze execution plans.
4) Adjust indexes or rewrite SQL as needed.

System‑Level Optimization

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

Memory: free, ps‑aux

IO devices: iostat, ss, netstat, iptraf, iftop, lsof

vmstat Explanation

Procs – r (running), b (blocked on I/O)

Memory – swpd (swapped), free, buffers, cache

Swap – s1, s0 (should be 0)

IO – b1 (read), b0 (write)

System – interrupts and context switches

CPU – user, system, idle, iowait

iostat Usage

Device throughput: iostat -dk 1 5 Utilization (%util) and await time: iostat -d -k -x 5 Hardware Optimization

Choose CPU based on workload (CPU‑intensive vs IO‑intensive)

Allocate memory (OLTP: 2‑4× CPU cores; OLAP: more memory)

Select appropriate storage, RAID level, and ensure BBU is disabled

Use high‑speed network equipment when needed

System Optimization

Prefer not to use swap; set /proc/sys/vm/swappiness=0 (temporary) or add vm.swappiness=0 to /etc/sysctl.conf Enable O_DIRECT via innodb_flush_method Adjust I/O scheduler: echo deadline >/sys/block/sda/queue/scheduler (temporary) or edit /boot/grub/grub.conf to add elevator=deadline Kernel parameters (e.g., net.ipv4.ip_local_port_range, fs.file-max) and user limits ( /etc/security/limits.conf)

Application‑Level Optimization

Separate business and database workloads

Disable unnecessary services (iptables, selinux, graphical UI, etc.) using chkconfig --level 23456 SERVICE off Database Parameter Tuning

Instance‑wide settings: thread_concurrency, sort_buffer_size, read_buffer_size, key_buffer_size, thread_cache_size Connection settings: max_connections, max_connect_errors, connect_timeout, skip-name-resolve, wait_timeout SQL layer: query_cache_size (useful for OLAP)

InnoDB engine: innodb_buffer_pool_size (≈70% of RAM), innodb_flush_log_at_trx_commit, innodb_file_per_table, innodb_log_file_size, innodb_log_buffer_size, etc.

By following this structured approach—understanding the query process, assessing risks, involving the right stakeholders, selecting appropriate dimensions, using the right tools, and applying systematic hardware, system, application, and database tuning—you can effectively improve MySQL performance while maintaining stability and scalability.