Master MySQL Optimization: Proven Strategies, Tools, and System Tuning

MySQL can be challenging for many Linux practitioners, often because the causes of database issues and their handling are unclear.

Before optimizing MySQL, understanding its query execution process is essential; most optimization work follows principles that guide the optimizer to operate as intended.

Optimization Philosophy

Note: Optimization carries risk and should be performed cautiously.

Potential issues include:

Optimizations may target complex, production‑grade systems, not just simple environments.

Risks may be unseen or unanticipated.

Every solution can introduce new problems.

Successful optimization keeps new issues within acceptable limits.

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

Key requirements:

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

Any change brings risk.

Performance improvements are probabilistic; degradation is equally possible.

Optimization should be a collaborative effort across departments.

Thus, optimization is driven by business needs.

Participants typically include DBAs, business representatives, application architects, developers, system and storage administrators, and other stakeholders.

Optimization Scope

What to Optimize

Security: data durability.

Performance: high‑speed data access.

Areas of Focus

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

Optimization Dimensions

The four dimensions are hardware, system configuration, table structure, and SQL & indexes.

Cost hierarchy: hardware > system config > table structure > SQL & indexes.

Effectiveness hierarchy: hardware < system config < table structure < SQL & indexes.

Database‑Level Tools

Commonly Used (12)

MySQL

mysqladmin

mysqlshow

SHOW [SESSION|GLOBAL] VARIABLES

SHOW [SESSION|GLOBAL] STATUS

information_schema

SHOW ENGINE INNODB STATUS

SHOW PROCESSLIST

EXPLAIN

SHOW INDEX

SLOW‑LOG

mysqldumpslow

Less Common but Useful (7)

Zabbix (monitoring)

pt‑query‑digest

MySQL slap

sysbench

MySQL profiling

Performance Schema

Workbench (management, backup, monitoring, analysis, optimization)

Database Problem‑Solving

Emergency tuning for sudden business stalls:

<code>show processlist
explain select id, name from stu where name='clsn'; # ALL id name age sex
select id,name from stu where id=2-1   # function result set >30
show index from table;
# Use execution plan to detect index or statement issues
show status like '%lock%';   # check lock status
kill SESSION_ID;            # terminate problematic session
</code>

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

<code>1) Review slowlog and identify slow queries;
2) Prioritize and investigate each slow query;
3) Analyze top SQL with EXPLAIN and check execution time;
4) Adjust indexes or rewrite statements.
</code>

System‑Level Monitoring

CPU: vmstat, sar, top, htop, nmon, mpstat.

Memory: free, ps‑aux.

I/O devices: iostat, ss, netstat, iptraf, iftop, lsof.

vmstat Details

Procs: r = processes waiting for CPU, b = processes in uninterruptible sleep (I/O wait).

Memory: swpd = swapped pages, free = unused, buff = buffers, cache = cache.

Swap: pages swapped in/out per second; aim for s1 and s0 near 0.

IO: b1 = blocks read, b0 = blocks written per second.

System: in = interrupts per second, cs = context switches.

CPU: time spent in user, system, idle, and I/O wait.

iostat Details

Example:

iostat -dk 1 5

or

iostat -d -k -x 5

to view %util and await.

TPS: transactions per second for the device.

IOPS: hardware‑specified maximum I/O operations per second.

KB_read/s, KB_wrtn/s, total KB_read, total KB_wrtn.

System Problem Diagnosis

High CPU, low I/O often indicates insufficient memory, poor disk performance, SQL issues, or I/O bottlenecks.

High I/O, low CPU suggests many small writes, autocommit overhead, or large I/O bursts.

Both high usually points to hardware limits or problematic SQL.

Basic Optimization Approach

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

Goal: define clear objectives, balance performance with safety, and anticipate issues.

Hardware Optimization

Host selection: balance CPU, memory, and disk; consider random vs sequential I/O; disable RAID controller BBU.

CPU: choose core count and frequency based on workload (CPU‑intensive vs I/O‑intensive).

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

Storage: select appropriate devices and RAID level (raid5, raid10, hot‑spare); ensure OS redundancy (raid1, SSD/SAS/SATA).

Network: use higher‑throughput switches, routers, NICs, and HBA cards.

Server Hardware Management

Physical status LEDs

Remote management cards (IPMI, iLO, iDRAC)

Third‑party monitoring (SNMP, agents)

Enterprise storage platforms (EMC, Hitachi, IBM, Huawei)

System Optimization

CPU and memory generally require no tuning beyond proper hardware selection.

Swap: avoid using swap for MySQL; many cloud instances set swap to 0.

IO: use RAID without LVM, ext4 or XFS, SSDs, and appropriate I/O scheduler.

Disable swap:

<code>/proc/sys/vm/swappiness = 0   # temporary
Add vm.swappiness=0 to /etc/sysctl.conf   # permanent
</code>

Set I/O scheduler to deadline:

<code># echo deadline > /sys/block/sda/queue/scheduler   # temporary
</code>

Permanent change (GRUB):

<code>vi /boot/grub/grub.conf
kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet
</code>

Linux Kernel Parameter Tuning

<code>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 open files
</code>

User Limits (optional for MySQL)

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

Application Optimization

Separate business and database applications.

Disable unnecessary services and firewalls (iptables, SELinux, etc.):

<code>chkconfig --level 23456 acpid off
chkconfig --level 23456 anacron off
chkconfig --level 23456 autofs off
chkconfig --level 23456 avahi-daemon off
chkconfig --level 23456 bluetooth off
chkconfig --level 23456 cups off
chkconfig --level 23456 firstboot off
chkconfig --level 23456 haldaemon off
chkconfig --level 23456 hplip off
chkconfig --level 23456 ip6tables off
chkconfig --level 23456 iptables off
chkconfig --level 23456 isdn off
chkconfig --level 23456 pcscd off
chkconfig --level 23456 sendmail off
chkconfig --level 23456 yum-updatesd off
</code>

Avoid running graphical interfaces on servers; use runlevel 3.

Consider whether MySQL is truly needed; sometimes the best architecture avoids a relational database.

Database‑Level Optimization

SQL focus: execution plans, indexes, query rewriting.

Architecture focus: high‑availability, high‑performance designs, sharding.

Database Parameter Tuning

Instance‑wide settings (example values):

<code>thread_concurrency
sort_buffer_size
read_buffer_size
read_rnd_buffer_size
key_buffer_size
thread_cache_size   # e.g., 1G→8, 2G→16, 3G→32, >3G→64
</code>

Connection layer:

<code>max_connections
max_connect_errors
connect_timeout
max_user_connections
skip-name-resolve
wait_timeout
back_log
</code>

SQL layer:

query_cache_size (increase for OLAP workloads; consider external caches like Redis or Memcached for frequently updated data).

Storage engine (InnoDB) settings:

<code>default-storage-engine
innodb_buffer_pool_size   # ~50% of RAM, not exceeding 70%
innodb_file_per_table
innodb_flush_log_at_trx_commit   # 0=fast, 1=safe, 2=balanced
binlog_sync
innodb_flush_method   # O_DIRECT or fdatasync
innodb_log_buffer_size   # <100M
innodb_log_file_size     # <100M
innodb_log_files_in_group   # 2‑3
innodb_max_dirty_pages_pct   # flush at 75%
log_bin
max_binlog_cache_size
max_binlog_size
innodb_additional_mem_pool_size   # 20M for <2G RAM, 100M for >32G
</code>

References:

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

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