A comprehensive MySQL tuning guide for backend engineers and DBAs, focused on five core layers: hardware, configuration, architecture, SQL, and locking. It addresses common pain points such as slow queries, limited throughput, replication lag, and deadlock diagnosis. Keywords: MySQL performance tuning, SQL optimization, primary-replica replication.
The technical specification snapshot provides a quick overview
| Parameter | Description |
|---|---|
| Technical Topic | MySQL Performance Tuning |
| Intended Audience | Backend Engineers, DBAs, Operations Engineers |
| Languages Involved | SQL, Shell, Configuration Files |
| Core Protocols / Mechanisms | MySQL Primary-Replica Replication, GTID, Semi-Synchronous Replication, InnoDB Locks |
| Core Dependencies | InnoDB, ProxySQL/HAProxy, performance_schema |
| Source Type | Practical Experience Summary |
| Star Count | Not Provided |
MySQL performance tuning must follow a layered approach
A single MySQL request passes through connection handling, parsing, the optimizer, the execution engine, caches, and disk I/O. If any layer becomes unbalanced, the symptoms usually appear as slower response times, CPU spikes, or saturated disks.
That is why tuning cannot focus only on SQL. The correct path is to identify resource bottlenecks first, then review configuration, then architecture, and finally dive into SQL, indexes, and lock contention.
AI Visual Insight: This diagram presents an end-to-end view of MySQL performance optimization. It typically includes stages such as client connections, SQL parsing, the optimizer, the storage engine, and disk I/O. The key takeaway is that performance issues are rarely caused by a single defect; they usually result from system-wide constraints spanning hardware and the query execution path.
It is best to establish a tuning priority matrix first
| Dimension | Typical Actions |
|---|---|
| Hardware | SSD, RAID10, Increase Memory Capacity |
| Configuration | Tune the buffer pool, connection limits, and flush policy |
| Architecture | Primary-replica replication, read/write splitting, database and table sharding |
| SQL | Slow query analysis, execution plans, index refactoring |
| Locks | Shorten transactions, reduce lock waits, investigate deadlocks |
# Set the InnoDB buffer pool to 70% to 80% of physical memory
innodb_buffer_pool_size = 4G
# Control the total number of connections to prevent excessive thread contention
max_connections = 500
max_user_connections = 50
# In non-financial scenarios, 2 can balance performance and reliability
innodb_flush_log_at_trx_commit = 2
sync_binlog = 1
# Enable file-per-table and the slow query log
innodb_file_per_table = 1
slow_query_log = 1
long_query_time = 0.5This configuration prioritizes cache hit rate, connection control, and log flush cost reduction.
Hardware and baseline parameter tuning deliver the fastest gains
Choose SSDs first for storage. If you need to balance performance and redundancy, RAID10 is a practical option. The more memory you have, the more hot data the InnoDB Buffer Pool can cache, which delivers the greatest benefit for random reads. For CPU selection, higher clock speed is usually more effective in most OLTP workloads.
One important caveat is that parameter tuning must align with business goals. For example, innodb_flush_log_at_trx_commit=2 can improve write performance, but under extreme failure conditions it may lose up to one second of data.
It is best to quantify slow queries and connection pressure first
-- Enable the slow query log
SET GLOBAL slow_query_log = ON;
-- Lower the slow SQL threshold to 0.5 seconds to expose issues more quickly
SET GLOBAL long_query_time = 0.5;This SQL helps the system record high-latency statements quickly and build a sample set for follow-up analysis.
Architecture scaling is the key path beyond single-instance limits
When a single server approaches its resource ceiling, parameter tuning alone cannot sustain further gains. At that point, you should consider primary-replica replication, read/write splitting, front-end caching, and database or table sharding.
AI Visual Insight: This diagram illustrates the architectural evolution from a single database instance to a distributed database topology. It typically shows stages such as standalone deployment, primary-replica replication, read/write splitting, and sharding. The core message is that as the business grows, the database must move from vertical scaling to horizontal expansion.
Primary-replica replication addresses read scaling and disaster recovery first
Asynchronous replication delivers strong performance, but it introduces replication lag. Semi-synchronous replication requires the primary to wait until at least one replica receives the binlog before returning success, which reduces the risk of data loss. GTID significantly simplifies failover and recovery.
AI Visual Insight: This diagram should show the replication flow in which the primary writes the binlog and replicas fetch and replay the relay log. It highlights commit acknowledgment, log transmission, and the sources of replication delay, which are essential for understanding the difference between asynchronous and semi-synchronous replication.
-- Install the semi-synchronous plugin on the primary
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
-- Install the semi-synchronous plugin on the replica
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
-- Enable GTID to simplify failover and recovery
SET PERSIST gtid_mode = ON;
SET PERSIST enforce_gtid_consistency = ON;This SQL enables replication features that are easier to operate and improves high-availability failover efficiency.
Read/write splitting is usually implemented with ProxySQL or HAProxy. Write requests go to the primary, while read requests are distributed to replicas. However, you must account for consistency issues caused by replication lag.
AI Visual Insight: This diagram shows a traffic distribution layer positioned between the application and the database. Write traffic goes to the primary, while read traffic is balanced across multiple replicas. The key technical points are traffic routing, failure isolation, and read-only scaling.
Sharding and denormalization become structural optimizations as data volume grows
When a single table grows into the tens or hundreds of millions of rows, index maintenance, pagination queries, and archival costs rise quickly. At that point, you should choose vertical database partitioning, vertical table partitioning, or horizontal sharding based on business requirements.
AI Visual Insight: This diagram shows a single-database, single-table model in which all data is stored centrally. It highlights hot-spot contention, capacity growth, and heavier indexes, making it a useful baseline for later split strategies.
AI Visual Insight: This diagram illustrates vertical database partitioning by business domain, such as separating a user database from an order database. The focus is on reducing pressure on a single database while clarifying team and business boundaries.
AI Visual Insight: This diagram shows a vertical table split in which a wide table is divided into a base table and an extension table. Its core value is to reduce row width for high-frequency queries, lower page read cost, and enable lazy loading for large fields.
Schema refactoring should serve the query path
-- Example of the original wide table
CREATE TABLE article (
id INT PRIMARY KEY,
title VARCHAR(200),
content TEXT
);
-- Split frequently accessed fields from large fields to reduce row width in the main table
CREATE TABLE article_base (
id INT PRIMARY KEY,
title VARCHAR(200)
);
CREATE TABLE article_content (
id INT PRIMARY KEY,
content TEXT
);This SQL demonstrates vertical table splitting, which improves hot-query efficiency by reducing row width in the main table.
SQL and index optimization should be driven by execution plans
The slow query log finds problems, and EXPLAIN explains them. Focus on type, key, rows, and Extra. If type=ALL, it usually means a full table scan, which should be treated as a high-priority optimization target.
Composite indexes must follow the leftmost prefix rule. At the same time, avoid wrapping indexed columns in functions, implicit type conversion, leading wildcard matching, and low-selectivity OR conditions that can invalidate index usage.
Use EXPLAIN to locate the access path
EXPLAIN
SELECT t.tname, c.cname, tc.phone
FROM teacher t, course c, teacher_contact tc
WHERE t.tid = c.tid
AND t.tcid = tc.tcid
AND (c.cid = 2 OR tc.tcid = 3);This SQL is used to analyze the access path, index usage, and estimated scanned row count in a multi-table join scenario.
Execution plan evaluation criteria should be standardized
| Column | What to Evaluate |
|---|---|
| type | It should reach at least range; ref and const are better |
| key | Whether the expected index is actually used |
| rows | Whether the scanned row count is too high |
| Extra | Watch for Using filesort and Using temporary |
Lock wait and deadlock management determine the system’s stability ceiling
Common InnoDB locks include row locks, gap locks, and next-key locks. Under the default RR isolation level, next-key locks are used to prevent phantom reads, but they also make lock contention more likely.
Deadlocks usually result from inconsistent access order, oversized transactions, or missing indexes. The right strategy is not to “manually kill threads after detection,” but to eliminate circular waits through better transaction design and index design.
Check transactions and locks first, then decide what to roll back
-- View lock wait statistics
SHOW STATUS LIKE 'innodb_row_lock%';
-- View current transactions
SELECT * FROM information_schema.innodb_trx;
-- View lock details in MySQL 8.0
SELECT * FROM performance_schema.data_locks;
SELECT * FROM sys.innodb_lock_waits;This set of SQL statements helps identify the blocking chain, lock holders, and wait relationships. It is the primary entry point for deadlock investigation.
A monitoring feedback loop is the real endpoint of performance tuning
High-quality tuning must form a closed loop: collect metrics, identify bottlenecks, implement changes, validate with load testing, and continue observing. Parameter changes without monitoring support are usually just guesswork.
A practical tuning sequence is: hardware first, then configuration, then architecture, then SQL, and finally lock management. This sequence offers the best cost-benefit ratio and fits incremental evolution in production environments.
Quick reference for common monitoring commands
-- View current connections and active threads
SHOW PROCESSLIST;
SELECT * FROM information_schema.PROCESSLIST;
-- View SQL execution counters
SHOW GLOBAL STATUS LIKE 'Com_%';
-- View slow-query and connection-related variables
SHOW VARIABLES LIKE '%connect%';
SHOW VARIABLES LIKE 'slow_query_log';This set of commands helps you quickly assess connection pressure, SQL workload, and the current state of key variables.
The FAQ section answers the most common operational questions
1. Should MySQL tuning start with SQL or configuration changes?
Start with monitoring and diagnosis. If the bottleneck is in disk, memory, or connection count, prioritize hardware and configuration. If slow queries are concentrated in a specific area, then move into SQL and index optimization. Changing only SQL will not necessarily solve a system-level bottleneck.
2. Is innodb_flush_log_at_trx_commit=2 suitable for production?
Yes, for workloads that do not require extreme consistency but do care about write performance. It can significantly reduce flush frequency, but in a failure scenario it may lose about one second of transactions. Financial workloads should usually stay with 1.
3. When is database and table sharding mandatory?
When a single table grows beyond tens of millions of rows, index bloat becomes obvious, hot writes concentrate on a narrow range, and the benefits of vertical scaling and index tuning start to diminish, you should evaluate sharding and design routing, primary key strategy, and cross-database query handling at the same time.
Core Summary: This article systematically rebuilds the methodology of MySQL performance tuning. It covers hardware selection, my.cnf parameters, primary-replica replication, read/write splitting, database and table sharding, slow queries, EXPLAIN, index design, and deadlock handling, helping engineers improve throughput and stability through a monitor-diagnose-optimize-validate workflow.