Comprehensive Guide to MySQL Database Optimization: Philosophy, Process, Tools, and Best Practices

Before any MySQL tuning, it is crucial to understand the MySQL query execution process, because most optimizations are about guiding the optimizer to follow a predictable, efficient plan.

Optimization Philosophy

Philosophy : Optimization must be driven by business needs, involve multiple departments, and accept that every change carries risk; the goal is to improve performance without compromising stability.

Participants

Database administrators, business representatives, application architects, developers, system and storage administrators should collaborate on every optimization task.

Optimization Dimensions

Hardware

System configuration

Database schema, indexes, and SQL

Application design

Database‑Level Tools

Commonly used MySQL utilities include:

mysql

mysqladmin

mysqlshow

SHOW [SESSION|GLOBAL] VARIABLES

SHOW [SESSION|GLOBAL] STATUS

information_schema

SHOW ENGINE INNODB STATUS

SHOW PROCESSLIST

EXPLAIN

SHOW INDEX

slow‑log

and

mysqldumpslow

Additional Monitoring Tools

Zabbix – host, system and database monitoring

pt‑query‑digest – slow‑log analysis

MySQL‑slap – load testing

sysbench – benchmark suite

Performance Schema – internal MySQL metrics

MySQL Workbench – management and analysis (resource‑intensive)

Typical Tuning Workflow

Emergency tuning (sudden business slowdown):

show processlist;

explain select id, name from stu where name='clsn';

show index from table_name;

show status like '%lock%';

kill SESSION_ID;

Regular tuning (periodic slowdown):

Analyze slow‑log, identify slow statements

Prioritize and examine each statement with EXPLAIN

Adjust indexes or rewrite SQL

System‑Level Tuning

CPU : Choose appropriate core count and frequency; CPU‑intensive workloads need high frequency, IO‑intensive workloads need more cores.

Memory : Allocate 2‑4× CPU cores for OLTP, more for OLAP; avoid swap usage.

IO : Use RAID appropriately, prefer SSD, tune I/O scheduler (deadline).

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

Permanent change (GRUB):

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

Swap : Set vm.swappiness=0 to minimise swap usage.

Linux Kernel & System Parameters

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

Adjust user limits for MySQL:

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

MySQL Configuration Tweaks

Enable O_DIRECT: innodb_flush_method=O_DIRECT Set innodb_flush_log_at_trx_commit (0‑performance, 1‑safety, 2‑balanced)

Adjust buffer pools, log sizes, thread concurrency, sort buffers, etc.

Connection Layer Settings

max_connections = ...
max_connect_errors = ...
connect_timeout = ...
max_user_connections = ...
skip-name-resolve
wait_timeout = ...
back_log = ...

SQL Layer Settings

query_cache_size

– useful for OLAP, otherwise consider external caches (Redis, Memcached)

InnoDB Storage Engine Parameters

default-storage-engine = InnoDB
innodb_buffer_pool_size = 50%‑70% of RAM
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0|1|2
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = < 100M
innodb_log_file_size = < 100M
innodb_log_files_in_group = 2‑3
innodb_max_dirty_pages_pct = 75

Application‑Level Optimizations

Separate business and database services, disable unnecessary services (iptables, selinux, graphical UI) and stop non‑essential daemons using chkconfig --level 23456 ... off.

Final Thoughts

Optimization should start from a clear goal, balance performance with safety, and consider whether MySQL is the right choice for the workload; sometimes the best optimization is to eliminate the need for a database.