Boost MySQL Performance: 6 Essential Config Tweaks for 5000+ QPS

Even when CPU and memory appear idle, MySQL can hit connection timeouts, query stalls, and 100% CPU spikes during peak traffic.

Common error messages include Too many connections , The table is full , and Deadlock found when trying to get lock .

Instead of blindly adding caches or sharding tables, 80% of performance problems stem from MySQL's default settings. Adjusting a few key parameters and writing proper SQL can support 5000+ QPS.

Key optimization principles: let MySQL fully utilize modern hardware and avoid inefficient operations.

1️⃣ Increase max_connections

max_connections = 2000

Default is 151; each connection uses ~256KB memory, so 2000 connections need ~500MB.

2️⃣ Enlarge innodb_buffer_pool_size

innodb_buffer_pool_size = 10G

Set to 60‑70% of physical memory to cache tables and indexes, reducing disk I/O.

3️⃣ Raise tmp_table_size and max_heap_table_size

tmp_table_size = 256M
max_heap_table_size = 256M

Default 16M causes complex GROUP BY/ORDER BY to spill to disk.

4️⃣ Increase thread_cache_size

thread_cache_size = 100

Reduces overhead of creating/destroying threads under high load.

5️⃣ Enable slow query log

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

Records queries taking longer than 1 second and those lacking indexes.

6️⃣ Grow innodb_log_file_size

innodb_log_file_size = 2G
innodb_log_files_in_group = 2

Larger log files reduce checkpoint frequency and I/O jitter.

SQL optimization tips

-- Add composite index for WHERE and ORDER BY
ALTER TABLE orders ADD INDEX idx_user_time (user_id, create_time);
-- Avoid SELECT *
SELECT id, name, email FROM users WHERE status = 1;
-- Use cursor‑based pagination instead of large OFFSET
SELECT * FROM articles WHERE id > 100000 ORDER BY id LIMIT 10;

Verification steps

1. Check current connections

SHOW STATUS LIKE 'Threads_connected';

2. Inspect InnoDB buffer pool hit rate

SHOW ENGINE INNODB STATUS\G

3. Analyze slow query log

mysqldumpslow -s t /var/log/mysql/slow.log

Recommended configuration for a 16 GB server:

max_connections = 2000
innodb_buffer_pool_size = 10G
tmp_table_size = 256M
max_heap_table_size = 256M
thread_cache_size = 100
slow_query_log = 1
long_query_time = 1
innodb_log_file_size = 2G

Summary: Tune parameters to use hardware, add proper indexes, and analyze slow SQL; a 4‑core 16 GB MySQL can handle 3000‑5000 QPS after optimization.