Master MySQL Optimization: Practical Strategies for High Performance

1.2 Optimization Philosophy

Optimization carries risk; proceed with caution. Stability and business continuity usually outweigh raw performance, and any change introduces potential new issues. Successful optimization balances improvements against acceptable risk, and must involve cross‑department collaboration driven by business needs.

1.2.1 Risks of Optimization

Optimizing a production system can create new problems; the goal is to keep any side effects within acceptable limits.

1.2.2 Optimization Requirements

Stability and sustainable operations are often more important than raw speed. All changes entail risk, and performance gains are not guaranteed.

1.2.3 Who Should Participate

Database administrators, business representatives, application architects, developers, hardware and system administrators, and storage administrators should all be involved in database optimization.

1.3 Optimization Approach

1.3.1 What to Optimize

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

1.3.2 Scope of Optimization

Storage, host, and OS:

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

1.3.3 Optimization Dimensions

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

Optimization Cost vs. Effect

Cost: hardware > system configuration > table structure > SQL & indexes Effect: hardware < system configuration < table structure < SQL & indexes

1.4 Available Optimization Tools

1.4.1 Database‑Level Tools

Common diagnostic commands:

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

Less common but useful tools:

zabbix               # Monitor host, system, database (requires Zabbix platform)
pt‑query‑digest      # Analyze slow logs
mysqlslap            # Analyze slow logs
sysbench            # Stress testing tool
mysql profiling     # Overall database status statistics
Performance Schema  # MySQL performance statistics
workbench           # Management, backup, monitoring, analysis, optimization (resource‑intensive)

1.4.2 Database‑Level Problem‑Solving Steps

Emergency tuning for sudden business stalls:

show processlist
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;
# Use the execution plan to identify index or statement issues
show status like '%lock%';   # Check lock status
kill SESSION_ID;            # Terminate problematic session

Routine tuning for periodic slow‑downs (e.g., daily peak hours):

1. Review slowlog and identify slow queries.
2. Prioritize and investigate each slow query.
3. Use EXPLAIN to analyze execution time.
4. Adjust indexes or rewrite the SQL.

1.4.3 System‑Level Tools

CPU monitoring: vmstat, sar, top, htop, nmon, mpstat Memory monitoring: free, ps -aux I/O monitoring: iostat, ss, netstat, iptraf, iftop, lsof Example of I/O scheduler change (temporary):

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

Permanent change via GRUB:

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

1.4.4 System Parameter Adjustments

Linux kernel tuning (example):

vim /etc/sysctl.conf
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   # Max file handles

User limits (optional for MySQL):

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

1.4.5 Application‑Level Optimizations

Separate business and database applications, disable unnecessary services (iptables, SELinux, etc.) using chkconfig, and avoid running a graphical interface on servers (runlevel 3).

1.6 Database Optimization

1.6.1 Parameter Tuning

Instance‑wide settings (advanced):

thread_concurrency          # Number of concurrent threads
sort_buffer_size            # Sort cache size
read_buffer_size            # Sequential read cache
read_rnd_buffer_size        # Random read cache
key_buffer_size            # Index cache
thread_cache_size           # Thread cache (scales with RAM)

Connection layer (basic):

max_connections            # Max client connections
max_connect_errors         # Max allowed connection errors
connect_timeout            # Connection timeout
max_user_connections       # Max connections per user
skip-name-resolve          # Disable DNS lookups
wait_timeout               # Wait timeout for idle connections
back_log                   # Connection backlog size

SQL layer (basic):

query_cache_size            # Query cache (useful for OLAP, limited for OLTP)
# Consider external caches like Redis or Memcached for frequently updated data.

1.6.2 InnoDB Engine Settings

default-storage-engine
innodb_buffer_pool_size          # Typically 50% of RAM, not exceeding 70% of physical memory
innodb_file_per_table = 1|0
innodb_flush_log_at_trx_commit = 0|1|2   # 0 = highest performance, 1 = safest, 2 = compromise
innodb_flush_method = O_DIRECT|fdatasync
innodb_log_buffer_size          # < 100M
innodb_log_file_size            # < 100M
innodb_log_files_in_group       # Usually 2‑3 files
innodb_max_dirty_pages_pct      # Flush when dirty pages reach 75%
innodb_additional_mem_pool_size # < 2G: 20M, >32G: 100M

1.7 References

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