Master MySQL Optimization: Practical Strategies for High Performance

Preface

MySQL can be a challenging component for many Linux professionals, often because the problems and their handling are not clearly understood. Before optimizing MySQL, it is essential to understand its query process; most optimization work follows principles that guide the optimizer to operate as intended.

Optimization Philosophy

Risks : Optimization carries risk and should be approached cautiously.

Potential Issues

Optimizations may be applied to complex, production‑grade systems.

Optimization techniques have inherent risks that may be overlooked.

Every solution can introduce new problems.

Successful optimization keeps any new issues within acceptable limits.

Maintaining the status quo or causing degradation is considered a failure.

Requirements

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

Any change introduces risk.

Performance improvements and regressions are equally probable.

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

Optimization is driven by business needs.

Participants

Database administrators, business representatives, application architects, designers, developers, hardware/system administrators, and storage administrators should all be involved.

Optimization Approach

What to Optimize

Security → Data sustainability

Performance → High‑performance data access

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 database structure

Instance configuration

Follow this order when designing, diagnosing, or optimizing a system.

Optimization Dimensions

Four Dimensions

Hardware

System configuration

Database schema

SQL and indexes

Optimization Choices

Cost: Hardware > System configuration > Table structure > SQL & indexes

Effect: Hardware < System configuration < Table structure < SQL & indexes

Optimization Tools

Database‑level Tools

Common diagnostic tools include:

Less common but useful tools:

Emergency Tuning Steps

show processlist

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

and related index checks

Use execution plans to identify index or query issues show status like '%lock%'; then kill SESSION_ID; Regular Tuning Steps

Examine slowlog to find slow queries

Prioritize and investigate slow statements one by one

Analyze top SQL with explain to check execution time

Adjust indexes or rewrite queries

System‑Level Optimization

CPU monitoring tools: vmstat, sar, top, htop, nmon, mpstat Memory tools: free, ps -aux IO/Network tools: iostat, ss, netstat, iptraf, iftop, lsof vmstat Fields

Procs: r = runnable processes, b = blocked (uninterruptible) processes

Memory: swpd = swapped out memory, free = unused memory, buffers = kernel buffers, cached = page cache

Swap: amount of memory swapped in/out per second

IO: blocks read/written per second

System: interrupts and context switches per second

CPU: time spent in user, system, idle, and iowait

iostat Usage

Example: iostat -dk 1 5 shows device utilization and await time.

tps – transfers per second

iops – maximum I/O operations per second defined by hardware

kB_read/s, kB_wrtn/s – throughput

Basic Optimization

Problem Identification Path

Hardware → System → Application → Database → Architecture (HA, read/write splitting, sharding)

Hardware Optimization

Select CPU, memory, and disk based on database type

Balance memory and disk resources

Prefer random I/O for OLTP, sequential for OLAP

Disable RAID controller BBU

CPU Selection

Key factors: core count and frequency

CPU‑intensive workloads need high frequency and many cores (OLTP)

IO‑intensive workloads need many cores, frequency less critical (OLAP)

Memory Selection

OLAP databases benefit from larger memory proportional to data volume

OLTP typically requires 2‑4× CPU core count in memory

Storage Selection

Choose storage devices based on data type

Configure appropriate RAID level (RAID5, RAID10, hot‑spare)

Use SSD, SAS, or SATA with redundancy

Network

Use higher‑capacity network equipment (switches, routers, NICs) to support traffic.

Server‑Side Optimization

Physical status LEDs, remote management cards (IPMI, iLO)

Third‑party monitoring (SNMP, agents)

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

System Tuning

CPU and memory generally require no changes beyond proper hardware selection

Avoid swap usage; many cloud servers set swap to 0

IO: use RAID, avoid LVM, choose ext4 or XFS, SSD, and appropriate I/O scheduler

Adjust vm.swappiness to favor cache release over swap, and enable innodb_flush_method=O_DIRECT to bypass OS cache for InnoDB buffer pool.

Database Optimization

SQL Optimization

Review execution plans, indexes, and rewrite queries

Architecture Optimization

High‑availability, high‑performance designs, sharding

Parameter Tuning

Adjust instance‑wide settings for advanced optimization

Connection Layer

Configure appropriate connection limits and methods

SQL Layer query_cache_size for query caching (useful for OLAP)

Cache invalidates quickly for frequently modified data

Consider external memory stores (Redis, Memcached) as alternatives

InnoDB Engine Parameters