Master MySQL Optimization: Proven Strategies for Performance and Stability

1. Introduction

MySQL can be a tricky component for many Linux professionals, often because the causes of database issues and the corresponding troubleshooting approaches are unclear. Before attempting any MySQL optimization, it is essential to understand MySQL's query execution process, as most performance improvements rely on guiding the optimizer to follow a predictable, efficient path.

2. Optimization Philosophy

2.1 Risks of Optimization

Optimizations are rarely performed on a clean, isolated environment; they often involve complex, production‑grade systems.

The techniques themselves carry significant risk, which may be invisible without proper awareness.

Every technical solution can introduce a new problem.

Successful optimization must keep any side‑effects within acceptable limits.

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

2.2 Why Optimize

Stability and business continuity usually outweigh raw performance.

Any change inevitably carries risk.

Performance gains are probabilistic; they may improve or degrade.

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

Ultimately, optimization is driven by business needs.

2.3 Who Should Participate

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

3. Optimization Approach

3.1 What to Optimize

Database optimization focuses on two main aspects: security (data sustainability) and performance (high‑speed data access).

3.2 Scope of Optimization

Storage, Host, and OS

Host architecture stability

I/O planning and configuration

Swap partition management

OS kernel parameters and network issues

Application Layer

Application stability

SQL statement performance

Serial resource access

Session management for poorly performing queries

Assessing whether MySQL is the right choice for the application

Database Layer

Memory allocation

Physical and logical database schema

Instance configuration

The following diagram illustrates the four optimization dimensions: hardware, system configuration, database schema, and SQL/indexes.

3.3 Prioritizing Optimizations

Cost order: hardware > system configuration > database schema > SQL & indexes

Effectiveness order: hardware < system configuration < database schema < SQL & indexes

4. Optimization Tools

4.1 Database‑Level Tools

Common diagnostic tools include:

Less frequently used but handy tools:

4.2 Emergency Tuning Steps

Show processlist

Explain problematic queries, check indexes, examine execution plans

Inspect lock status (show status like '%lock%') and kill offending sessions

4.3 Routine Tuning Steps

Analyze slow‑query log to identify slow statements

Prioritize and investigate each slow query

Use EXPLAIN to fine‑tune execution plans

Adjust indexes or rewrite SQL as needed

5. Basic Optimization

Optimization follows a layered hierarchy: hardware → system → application → database → architecture (high‑availability, read/write splitting, sharding).

5.1 Hardware

Select CPU, memory, and disk based on database type

Balance memory and disk resources

Consider random vs. sequential I/O characteristics

Disable RAID controller BBU if present

5.2 CPU Selection

Key factors: core count and clock speed

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

I/O‑intensive workloads (OLAP) prioritize core count over frequency

5.3 Memory

OLAP systems benefit from larger memory pools

OLTP systems typically require 2–4× CPU core count in memory

5.4 Storage

Choose storage media based on data type

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

Implement OS‑level redundancy (RAID1) and use SSD/SAS/SATA as needed

5.5 Network

Deploy higher‑capacity network equipment (switches, routers, NICs, HBA cards) to support traffic.

5.6 Server‑Side Optimizations

Prefer runlevel 3 (no graphical UI) on servers

Consider whether MySQL is truly required or if an alternative database fits better

6. Database‑Level Optimization

6.1 Parameter Tuning

Adjust instance‑wide settings for advanced performance and scalability.

6.2 Connection Layer

Configure appropriate client limits and connection handling strategies.

6.3 SQL Layer

Adjust query_cache_size for OLAP workloads (generally not exceeding a few GB)

Use external in‑memory stores (Redis, Memcached) for frequently accessed data

6.4 InnoDB Engine Parameters

7. References

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

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