Master MySQL Optimization: Practical Strategies and Tools for High Performance

MySQL Optimization Overview

MySQL is a challenging component for many Linux professionals; understanding its query process is essential before any optimization.

Optimization Philosophy

Optimization carries risk; stability and business continuity are more important than raw performance, and changes must involve multiple departments.

Who Should Participate

Database administrators, business representatives, application architects, developers, system and storage administrators should collaborate.

Optimization Approach

Focus on security (data sustainability) and performance (fast data access). The scope includes hardware, OS, application, and database layers.

Key Dimensions

Hardware

System configuration

Database schema

SQL and indexes

Tools for Database Layer

Common commands: mysql, mysqladmin, show processlist, explain, slowlog, etc. Additional utilities: Zabbix, pt-query-digest, sysbench, Performance Schema, Workbench.

Typical Troubleshooting Steps

Run show processlist to identify blocking sessions.

Use explain to examine execution plans and index usage.

Check lock status with show status like '%lock%' and kill problematic sessions.

Analyze slow query log and adjust indexes or rewrite SQL.

System‑Level Optimizations

Monitor CPU with vmstat, top; memory with free; I/O with iostat. Adjust kernel parameters (e.g., net.ipv4.ip_local_port_range, fs.file-max) and disable unnecessary services.

Hardware Recommendations

Select CPU cores and frequency based on workload (OLTP vs OLAP), allocate sufficient RAM (2‑4× CPU cores for OLTP), choose appropriate storage (SSD, RAID levels) and network equipment.

Database Configuration Tweaks

Adjust InnoDB buffer pool, log file size, flush method, and connection limits such as max_connections, thread_cache_size, and query_cache_size according to workload.

Reference

Sources: cnblogs , Jianshu .