Master MySQL Optimization: Strategies, Tools, and Best Practices

1 Introduction

Before optimizing MySQL you must understand its query execution process; most optimizations follow principles that guide the optimizer to run as expected.

2 Optimization Philosophy

2.1 Risks of Optimization

Optimization is rarely performed in a pure environment; it often involves a complex production system.

The techniques themselves carry significant risk that may be unnoticed.

Every technical solution can introduce new problems.

Successful optimization means the new problems stay within acceptable limits.

Maintaining the status quo or degrading performance is considered a failure.

2.2 Optimization Requirements

Stability and business continuity are usually more important than raw performance.

Any change brings risk.

Performance improvements are probabilistic; they may improve or degrade.

Optimization should be a collaborative effort across departments; no single team should act alone.

Optimization work is driven by business needs.

2.3 Who Should Participate

Database administrators, business representatives, application architects, designers, developers, hardware/system administrators, and storage administrators should all be involved.

3 Optimization Approach

3.1 What to Optimize

Two main aspects: security (data sustainability) and performance (high‑speed data access).

3.2 Scope of Optimization

Storage, host, and operating system:

Host architecture stability

I/O planning and configuration

Swap partition

OS kernel parameters and network issues

Application layer:

Application stability

SQL statement performance

Serial resource access

Session management for poor performance

Suitability of MySQL for the application

Database layer:

Memory

Physical & logical schema

Instance configuration

Note: The above order can be followed for system design, problem定位, and optimization.

3.3 Optimization Dimensions

Four dimensions: hardware, system configuration, database table structure, SQL & indexes.

Optimization cost: hardware > system configuration > database table structure > SQL & indexes Optimization effect: hardware < system configuration < database table structure

4 What Tools Are Available?

4.1 Database‑Level Tools

Commonly used commands:

mysql
mysqladmin               # MySQL client for management
mysqlshow                # Powerful shell command to view objects
show [SESSION|GLOBAL] variables   # View DB parameters
SHOW [SESSION|GLOBAL] STATUS        # View DB status
information_schema      # Access metadata
SHOW ENGINE INNODB STATUS          # InnoDB status
SHOW PROCESSLIST       # View active sessions
explain                # Get execution plan
show index              # View table indexes
slow‑log                # Record slow queries
mysqldumpslow           # Analyze slow‑log files

Less common but useful tools:

zabbix          # Monitor hosts, systems, databases
pt‑query‑digest # Analyze slow logs
mysqlslap       # Load testing
sysbench        # Stress testing
mysql profiling # Overall DB status statistics
Performance Schema # MySQL performance metrics
workbench       # Management, backup, monitoring, analysis, optimization (resource‑heavy)

4.2 Emergency Tuning Steps

Typical urgent scenario : Business is blocked and needs immediate resolution.

1. show processlist 2. explain select id, name from stu where name='clsn'; 3. Check indexes: show index from table; 4. Examine lock status: show status like '%lock%'; 5. Kill problematic session: kill SESSION_ID; Regular tuning flow for periodic slow‑downs (e.g., daily 10‑11 am spikes):

1. Review slow‑log and identify slow statements. 2. Prioritize and investigate each slow statement. 3. Analyze top SQL with explain to check execution time. 4. Adjust indexes or rewrite the query.

4.3 System‑Level Tools

CPU monitoring: vmstat, sar, top, htop, nmon, mpstat Memory monitoring: free, ps -aux I/O devices (disk, network): iostat, ss, netstat, iptraf, iftop, lsof Example vmstat output explanation:

Procs: r – processes waiting for CPU, b – uninterruptible sleep (I/O wait)
Memory: swpd – swapped out memory, free – free memory, buff – buffers, cache – cache
Swap: si – blocks swapped in, so – blocks swapped out (ideally 0)
IO: bi – blocks received, bo – blocks sent
System: in – interrupts per second, cs – context switches per second
CPU: us – user, sy – system, id – idle, wa – I/O wait

Example iostat usage:

iostat -dk 1 5          # Device stats every second, 5 times
iostat -d -k -x 5      # Show %util and await

5 Basic Optimization

5.1 General Idea

Locate problem points and work through the stack: hardware → system → application → database → architecture (HA, read/write split, sharding).

Balance performance and safety, and anticipate issues.

5.2 Hardware Optimization

Host considerations:

Select CPU, memory, and disk based on database type.

Balance memory and disk resources.

Prefer random I/O for OLTP, sequential I/O for OLAP.

Disable RAID controller BBU.

CPU core count and clock speed are key; choose high‑frequency many‑core CPUs for CPU‑intensive workloads and many‑core lower‑frequency CPUs for I/O‑intensive workloads.

Memory sizing:

OLAP workloads need larger memory proportional to data volume.

OLTP workloads typically allocate 2‑4× CPU core count.

Storage considerations:

Choose storage media (SSD, SAS, SATA) based on data characteristics.

Configure appropriate RAID level (RAID5, RAID10, hot‑spare).

Implement OS‑level redundancy (RAID1) and avoid unnecessary complexity.

Network equipment should support higher throughput (switches, routers, NICs, HBA cards).

5.3 System Optimization

CPU: generally no tuning needed beyond proper hardware selection.

Memory: same as CPU – rely on proper hardware.

Swap: MySQL should avoid using swap; set /proc/sys/vm/swappiness to 0 (temporary) and add vm.swappiness=0 to /etc/sysctl.conf for permanence.

IO: Use RAID, avoid LVM, use ext4 or xfs, SSDs, and choose an appropriate I/O scheduler (e.g., deadline).

# echo deadline > /sys/block/sda/queue/scheduler   # temporary change
vi /boot/grub/grub.conf   # add "elevator=deadline" for permanent change

5.4 System Parameter Tuning

Linux kernel parameters (example):

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

User limits (optional for MySQL):

* soft nproc 65535
* hard nproc 65535
* soft nofile 65535
* hard nofile 65535

5.5 Application Optimization

Separate business and database applications; disable unnecessary services and firewalls (iptables, selinux, etc.). Example commands to turn off services:

chkconfig --level 23456 acpid off
chkconfig --level 23456 anacron off
chkconfig --level 23456 autofs off
... (other services) ...

Avoid running a graphical UI on servers; use runlevel 3.

Consider whether MySQL is the right choice; sometimes the best architecture avoids a relational database altogether.

6 Database Optimization

SQL optimization focuses on execution plans, indexes, and query rewriting. Architecture optimization includes high‑availability, high‑performance designs, and sharding.

6.1 Database Parameter Tuning

Instance‑wide settings (advanced):

thread_concurrency
sort_buffer_size
read_buffer_size
read_rnd_buffer_size
key_buffer_size
thread_cache_size

Connection layer (basic):

max_connections
max_connect_errors
connect_timeout
max_user_connections
skip-name-resolve
wait_timeout
back_log

SQL layer (basic):

query_cache_size   # Increase for OLAP workloads; consider external caches like Redis or Memcached for frequently updated data.

6.2 InnoDB Engine Parameters

default-storage-engine
innodb_buffer_pool_size   # ~50% of RAM, not exceeding 70% of physical memory
innodb_file_per_table
innodb_flush_log_at_trx_commit   # 0 (fast), 1 (safe), 2 (balanced)
innodb_flush_method   # O_DIRECT or fdatasync
innodb_log_buffer_size
innodb_log_file_size
innodb_log_files_in_group
innodb_max_dirty_pages_pct
max_binlog_cache_size
max_binlog_size
innodb_additional_mem_pool_size

Source: Adapted from the public account “芋道源码”, author “惨绿少年”.