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

Before optimizing MySQL, understanding its query execution process is essential; most optimizations aim to guide the optimizer to follow a predictable, efficient path.

1. Optimization Philosophy

Risks: Optimizations often affect complex, production systems and can introduce new problems; any change must keep issues within acceptable limits, otherwise the effort is a failure.

Needs: Stability and business continuity usually outweigh raw performance; optimization requires cross‑department collaboration and cannot be the sole responsibility of a single team.

Participants: Database administrators, business representatives, application architects, developers, system and storage administrators should all be involved.

2. Optimization Approach

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

Scope:

Hardware/OS: host architecture, I/O planning, swap, kernel and network parameters.

Application: stability, SQL performance, serial resource access, session management, suitability of MySQL for the workload.

Database: memory, physical & logical schema, instance configuration.

Dimensions: hardware, system configuration, table structure, SQL & indexes.

Cost hierarchy (high‑to‑low): hardware > system config > table structure > SQL/indexes. Effectiveness hierarchy (low‑to‑high): hardware < system config < table structure < SQL/indexes.

3. Optimization Tools

Database‑level utilities:

mysql, mysqladmin, mysqlshow, SHOW VARIABLES, SHOW STATUS, information_schema, SHOW ENGINE INNODB STATUS, SHOW PROCESSLIST, EXPLAIN, SHOW INDEX, slow‑log, mysqldumpslow.

Additional tools: Zabbix, pt‑query‑digest, MySQL‑slap, sysbench, MySQL profiling, Performance Schema, MySQL Workbench.

4. System‑level Optimization

CPU: Choose based on workload (CPU‑intensive OLTP needs high frequency and many cores; I/O‑intensive OLAP favors many cores).

Memory: OLAP requires more memory; OLTP typically 2‑4× CPU cores.

Storage: Select devices per data type, configure appropriate RAID, prefer SSD, avoid LVM, use ext4/xfs.

Network: Use higher‑capacity switches, routers, NICs, HBA cards.

Swap: Avoid using swap; set vm.swappiness=0 (temporary via /proc/sys/vm/swappiness, permanent via /etc/sysctl.conf).

Linux kernel parameters: Adjust net.ipv4.ip_local_port_range, net.ipv4.tcp_max_syn_backlog, net.ipv4.tcp_fin_timeout, fs.file-max, etc.

User limits: Set high soft/hard values for nproc and nofile in /etc/security/limits.conf.

5. Application‑level Optimization

Separate business and database applications, disable unnecessary services (iptables, SELinux, graphical UI, etc.) using chkconfig commands, and consider whether MySQL is the right choice for the workload.

6. MySQL Configuration Tuning

Instance parameters (advanced): thread_concurrency, sort_buffer_size, read_buffer_size, read_rnd_buffer_size, key_buffer_size, thread_cache_size.

Connection settings: max_connections, max_connect_errors, connect_timeout, max_user_connections, skip-name-resolve, wait_timeout, back_log.

SQL layer: Adjust query_cache_size (useful for OLAP), consider external caches like Redis or Memcached.

InnoDB engine parameters: innodb_buffer_pool_size (≈50% of RAM, ≤70% of physical memory), innodb_file_per_table, innodb_flush_log_at_trx_commit, innodb_log_buffer_size, innodb_log_file_size, innodb_max_dirty_pages_pct, innodb_flush_method, etc.

7. SQL Optimization

Focus on execution plans, proper indexing, and query rewriting. Use EXPLAIN to identify bottlenecks, analyze slow‑log, and iteratively improve top‑slow queries.

References

https://www.cnblogs.com/zishengY/p/6892345.html

https://www.jianshu.com/p/d7665192aaaf