Comprehensive MySQL Optimization Guide: Hardware, OS, Config & Query Tuning

MySQL Optimization

Optimization Approach

0. Hardware optimization

1. Disk I/O optimization

2. Operating‑system optimization

3. Vertical and horizontal sharding

4. my.cnf parameter tuning

5. MySQL query optimization

6. MySQL storage engine selection

Hardware Optimization

CPU: 64‑bit, high frequency, large cache, strong parallel processing.

Memory: large capacity, high frequency, avoid using swap.

Disk: Prefer 15,000 RPM or faster, use RAID10/RAID5 or SSD.

Network: Standard 1 GbE, preferably 10 GbE with bonding; keep MySQL and web servers on the same LAN and minimize firewall overhead.

Note: NIC bonding combines multiple NICs into one logical interface for redundancy, bandwidth expansion, and load balancing.

Disk I/O Planning and Related Technologies

Use RAID10 or RAID5.

Prefer 15,000 RPM disks or SSDs.

Place swap on RAID0 or SSD.

Separate database directory onto its own disk/partition, distinct from the OS.

Store binary logs on a dedicated partition to reduce I/O contention.

Operating System Optimization – Kernel and TCP Connection Limits

TCP Connection Limits and Max Open Files

1. Increase TCP port range:

# echo "net.ipv4.ip_local_port_range = 1024 65000" >> /etc/sysctl.conf
# sysctl -p

2. Raise Linux connection tracking limits:

# echo 524288 > /proc/sys/net/ipv4/ip_conntrack_max   # CentOS 7
# sysctl -p

3. Tune TCP timeout and reuse settings:

# cat >> /etc/sysctl.conf <<EOF
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 30
EOF
# sysctl -p

4. Increase the maximum number of open files for the MySQL user:

# vi /etc/security/limits.conf
mysql soft nofile 102400
mysql hard nofile 102400
mysql soft nproc 102400
mysql hard nproc 102400

5. Ensure the pam_limits module is loaded:

# vi /etc/pam.d/login
session required /usr/lib64/security/pam_limits.so

6. Raise the system‑wide file descriptor limit:

# echo "fs.file-max = 1000000" >> /etc/sysctl.conf
# sysctl -p

Disable Unnecessary Services and Optimize Filesystem

Mount the database on a dedicated XFS filesystem with the noatime option to avoid updating access timestamps:

# vi /etc/fstab
UUID=... /data xfs defaults,noatime 0 0
# mount -o remount /data

Vertical and Horizontal Sharding

Vertical sharding: dedicate a server solely to MySQL to avoid interference from other services.

Horizontal sharding: use master‑slave replication, load balancing, or high‑availability clusters to scale out when a single instance cannot handle load.

my.cnf Parameter Optimization

General Principles

Allocate roughly 40 % of resources to the OS and 60‑70 % to MySQL (CPU and memory).

Query Cache

Enable the query cache to store SELECT results in memory, reducing disk reads for repeated queries. Typical size: 32‑512 MB.

# vi /etc/my.cnf
[mysqld]
query_cache_size = 256M
query_cache_type = 1

Restart MySQL to apply: # systemctl restart mysqld Check cache status:

mysql> SHOW STATUS LIKE 'Qcache%';

Force Resource Limits

# vi /etc/my.cnf
[mysqld]
max_connections = 500
wait_timeout = 10
max_connect_errors = 100

These settings prevent resource exhaustion and limit connection retries.

Table Cache

Cache open tables to reduce file‑open overhead. Example:

# vi /etc/my.cnf
[mysqld]
table_open_cache = 512

Verify with:

mysql> SHOW GLOBAL STATUS LIKE 'Open%_tables';

Key Buffer (Index Cache)

# vi /etc/my.cnf
[mysqld]
key_buffer_size = 512M

Monitor with:

mysql> SHOW STATUS LIKE '%key_read%';

Query Optimization

1. Enable the slow‑query log to capture queries exceeding a threshold:

# vi /etc/my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /data/mysql/log/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

Restart MySQL and test:

# systemctl restart mysqld
mysql> CREATE TABLE test (id INT, name VARCHAR(20));
mysql> INSERT INTO test VALUES (1,'man');
mysql> SELECT * FROM test;
# cat /data/mysql/log/slow.log

2. Use EXPLAIN to view execution plans without running the query.

3. Apply common best‑practice tips: use LIMIT 1 when only one row is needed, avoid SELECT *, add appropriate indexes, avoid column calculations in WHERE clauses, keep SQL simple, replace OR with IN, avoid leading wildcards, and refrain from using != or <> which can disable index usage.

Summary

MySQL offers over 100 tunable variables; mastering a core subset—hardware provisioning, OS limits, my.cnf parameters, query cache, table and index caches, and query‑level optimizations—allows you to achieve stable, high‑performance deployments, while SHOW STATUS helps verify that the server behaves as expected.