Master MySQL Optimization: Proven Strategies for High Performance and Stability

Reading note: MySQL is a tricky problem for many Linux practitioners; most issues stem from unclear problem scenarios and handling ideas. Before optimizing MySQL you must understand its query process, and let the optimizer run in a predictable way.

1.2 Optimization Philosophy

Optimization carries risk; proceed with caution.

1.2.1 Risks of Optimization

Optimizing is rarely done on a simple environment; it often involves a complex, already‑deployed system.

Optimization methods inherently carry risk, which may be invisible until problems arise.

Any technical solution can solve one problem but may introduce another.

Successful optimization keeps any new problems within an acceptable range.

Maintaining the status quo or getting worse is considered a failure.

1.2.2 Optimization Needs

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

Optimization inevitably involves changes, and changes bring risk.

Performance improvements are probabilistic; they may succeed or degrade.

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

Optimization is driven by business requirements.

1.2.3 Who Should Participate

Database administrators, business representatives, application architects, developers, hardware and system administrators, storage administrators, and other business‑related personnel should jointly participate in database optimization.

1.3 Optimization Ideas

1.3.1 What to Optimize

Database optimization focuses on two main aspects: security and performance.

Security → data sustainability.

Performance → high‑performance data access.

1.3.2 Scope of Optimization

Storage, host, and operating system aspects:

Host architecture stability

I/O planning and configuration

Swap partition

OS kernel parameters and network issues

Application aspects:

Application stability

SQL statement performance

Serial resource access

Session management for poor performance

Whether the application is suitable for MySQL

Database aspects:

Memory

Database structure (physical & logical)

Instance configuration

Follow this order when designing, locating problems, or optimizing.

1.3.3 Optimization Dimensions

Four dimensions of database optimization: hardware, system configuration, table structure, SQL and indexes.

1.4 What Optimization Tools Exist?

1.4.1 Database‑Level Tools

Common diagnostic tools:

mysql
mysqladmin               # MySQL client for management operations
mysqlshow                # Powerful shell command for viewing
show [SESSION|GLOBAL] variables   # View database parameters
show [SESSION|GLOBAL] STATUS      # View database status
information_schema       # Access metadata
show ENGINE INNODB STATUS          # InnoDB engine status
show PROCESSLIST         # View all current sessions
explain                  # Get execution plan for a query
show index               # View table indexes
slow‑log                 # Record slow queries
mysqldumpslow            # Analyze slow‑log files

Less common but useful tools:

zabbix                 # Host, system, database monitoring platform
pt‑query‑digest        # Analyze slow log
mysqlslap              # Load testing tool
sysbench               # Stress testing tool
mysql profiling        # Overall database profiling
Performance Schema     # MySQL performance statistics
workbench              # Management, backup, monitoring, analysis, optimization (resource‑heavy)

1.4.2 Database‑Level Problem Solving

Emergency tuning steps for sudden business stalls:

1. show processlist 2. explain select id, name from stu where name='clsn'; select id, name from stu where id=2‑1; # result set >30 show index from table; 3. Use execution plan to judge index issues or statement problems. 4. show status like '%lock%'; # Check lock status 5. kill SESSION_ID; # Terminate problematic session

Regular tuning for periodic slow‑downs (e.g., daily 10‑11 am peak):

1. Examine slow‑log and identify slow statements. 2. Prioritize and investigate each slow statement. 3. Analyze top SQL with EXPLAIN and check execution time. 4. Adjust indexes or rewrite statements.

1.5 Basic Optimization

1.5.1 Optimization Thought Process

Problem location hierarchy: hardware → system → application → database → architecture (HA, read/write split, sharding).

Clear optimization goals, balance performance and security, and anticipate issues.

1.5.2 Hardware Optimization

Host considerations:

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

Balance memory and disk resources.

Random vs. sequential I/O.

Disable RAID controller BBU.

CPU selection:

Key factors: core count and clock speed.

CPU‑intensive (OLTP): high frequency and many cores.

I/O‑intensive (OLAP): many cores, frequency less critical.

Memory selection:

OLAP needs more memory, proportional to data volume.

OLTP typically 2‑4× CPU cores; no strict rule.

Storage considerations:

Choose devices based on data type.

Configure appropriate RAID level (raid5, raid10, hot‑spare).

For OS, use redundancy (raid1) with SSD, SAS, or SATA.

Disable RAID card BBU.

Network equipment:

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

1.5.3 Server Hardware Details

1. Physical status LEDs.

2. Remote management cards (FENCE devices: IPMI, iLO, iDRAC) for power control and monitoring.

3. Third‑party monitoring (SNMP, agents).

4. Storage monitoring platforms (EMC, Hitachi, IBM, Huawei).

1.5.4 System Optimization

CPU:

Generally no tuning needed; focus on proper hardware selection.

Memory:

Generally no tuning needed; focus on proper hardware selection.

Swap:

Avoid using swap for MySQL. In Alibaba Cloud the default swap is 0.

IO:

Use RAID, avoid LVM, choose ext4 or xfs, SSD, and appropriate I/O scheduler.

Swap adjustment (disable swap usage):

/proc/sys/vm/swappiness set to 0 (temporary) and add vm.swappiness=0 to /etc/sysctl.conf (permanent).

Modify MySQL innodb_flush_method to enable O_DIRECT, letting InnoDB buffer pool bypass the OS cache.

IO scheduler strategy (temporary):

# echo deadline > /sys/block/sda/queue/scheduler

Permanent change (GRUB):

vi /boot/grub/grub.conf
# add "elevator=deadline" to the kernel line

1.5.5 System Parameter Tuning

Linux kernel parameters:

vim /etc/sysctl.conf
net.ipv4.ip_local_port_range = 1024 65535   # User port range
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_fin_timeout = 30
fs.file-max = 65535   # Max file handles

User limits (MySQL can ignore):

vim /etc/security/limits.conf
* soft nproc 65535
* hard nproc 65535
* soft nofile 65535
* hard nofile 65535

1.5.6 Application Optimization

Separate business and database applications.

Disable unnecessary services (iptables, SELinux, graphical interface, etc.) using chkconfig.

Consider whether MySQL is truly needed; the ultimate optimization is to eliminate the database.

1.6 Database Optimization

1.6.1 MySQL Parameter Optimization

Instance‑wide (advanced) settings:

thread_concurrency          # Number of concurrent threads
sort_buffer_size           # Sort cache
read_buffer_size           # Sequential read cache
read_rnd_buffer_size       # Random read cache
key_buffer_size            # Index cache
thread_cache_size          # Thread cache (1G→8, 2G→16, 3G→32, >3G→64)

Connection layer (basic):

max_connections            # Max connections, set according to transaction volume
max_connect_errors        # Max error connections, set high
connect_timeout            # Connection timeout
max_user_connections       # Max connections per user
skip-name-resolve          # Skip DNS resolution
wait_timeout               # Wait timeout
back_log                   # Max pending connections in backlog

SQL layer (basic):

query_cache_size           # Query cache (increase for OLAP, usually < 1 GB)
# For frequently updated data, cache invalidates quickly; consider Redis or Memcached.

1.6.2 InnoDB Storage Engine Parameters

default-storage-engine
innodb_buffer_pool_size          # ~50% of RAM, not exceeding 70% of physical memory
innodb_file_per_table = 1|0
innodb_flush_log_at_trx_commit = 0|1|2   # 1 safest, 0 highest performance, 2 compromise
binlog_sync
innodb_flush_method = O_DIRECT|fdatasync
innodb_log_buffer_size          # < 100 M
innodb_log_file_size            # < 100 M
innodb_log_files_in_group       # ≤5, usually 2‑3
innodb_max_dirty_pages_pct      # Flush when dirty pages reach 75%
log_bin
max_binlog_cache_size           # Optional
max_binlog_size                 # Optional
innodb_additional_mem_pool_size # <20 M for <2 GB RAM, ~100 M for >32 GB RAM

1.7 References

[1] https://www.cnblogs.com/zishengY/p/6892345.html

[2] https://www.jianshu.com/p/d7665192aaaf