MySQL Performance Tuning: 6 Configuration Parameters That Matter More Than Redis
Backend Development

MySQL Performance Tuning: 6 Configuration Parameters That Matter More Than Redis

CodeGram Team
January 14, 2025
881 views
#MySQL #Database #Performance #Optimization #Backend

Article Summary

Before rushing to add caching layers, optimize your MySQL configuration first. This practical guide shows how tuning 6 critical parameters can help a 4-core 16GB MySQL server handle 3,000-5,000 QPS, along with essential SQL optimization techniques.

Your server CPU and memory are idle,

but during peak times, the database experiences connection timeouts, query freezes, and CPU spikes to 100%?

Your logs are filled with:

  • Too many connections
  • The table is full
  • Deadlock found when trying to get lock

Stop blindly adding caches and sharding databases!

80% of database performance issues stem from MySQL's default configuration.

By adjusting a few key parameters and standardizing SQL practices, you can easily support 5000+ QPS and say goodbye to being the "database scapegoat"!

 Applicable to MySQL 5.7 / 8.0

 No code changes required

 Production-tested

⚠️ Why Optimize?

MySQL's default configuration is designed for small machines with 1GB memory, while today's cloud servers typically start at 8 cores and 16GB.

💡 Optimization essence:

Let MySQL fully utilize modern hardware resources + avoid inefficient operations

🔧 6 Must-Tune MySQL Parameters (With Detailed Explanations)

📌 Configuration file: /etc/my.cnf or /etc/mysql/my.cnf

Restart MySQL after changes: systemctl restart mysqld

1️⃣ Increase Maximum Connections (Solve Too many connections)

[mysqld]
max_connections = 2000
  • Default is only 151, quickly exhausted under high concurrency
  • Note: Each connection consumes ~256KB memory, 2000 connections ≈ 500MB

2️⃣ Adjust InnoDB Buffer Pool (Dramatically Improve Query Speed)

innodb_buffer_pool_size = 10G
  • Most important parameter! Set to 60%-70% of physical memory
  • Caches table data and indexes, reducing disk IO
  • Example: 16GB memory → set 10G; 32GB memory → set 20G

3️⃣ Optimize Temporary Table Memory Limit (Avoid Disk Temporary Tables)

tmp_table_size = 256M
max_heap_table_size = 256M
  • Default is only 16M, complex GROUP BY / ORDER BY writes to disk
  • Both must be set to the same value, otherwise the smaller one prevails

4️⃣ Increase Connection Thread Cache (Reduce New Connection Overhead)

thread_cache_size = 100
  • Default is 9, frequently creates/destroys threads under high concurrency
  • Recommended value: 8 + max_connections / 100 (not exceeding 100)

5️⃣ Enable Slow Query Log (Locate Performance Bottlenecks)

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
  • Records SQL taking >1 second to execute
  • log_queries_not_using_indexes helps you find queries missing indexes

6️⃣ Adjust InnoDB Log File Size (Improve Write Performance)

innodb_log_file_size = 2G
innodb_log_files_in_group = 2
  • Default is only 48M, frequent checkpoints cause IO jitter
  • Recommendation: 25% of innodb_buffer_pool_size (not exceeding 4G)

⚠️ Important: Changing innodb_log_file_size requires stopping MySQL, deleting old log files, then restarting!

🛠️ SQL Optimization Recommendations (More Important Than Parameter Tuning!)

✅ Must-do: Add indexes for WHERE / ORDER BY fields

❌ Wrong: Full table scan

SELECT * FROM orders
WHERE user_id = 123
ORDER BY create_time;

✅ Correct: Composite index

ALTER TABLE orders
ADD INDEX idx_user_time
(user_id, create_time);

✅ Avoid SELECT *

-- Only query needed fields
SELECT id, name, email
FROM users
WHERE status = 1;

✅ Pagination Optimization (Deep Pagination Killer)

❌ Wrong: OFFSET 100000 is extremely inefficient

SELECT * FROM articles
ORDER BY id
LIMIT 100000, 10;

✅ Correct: Cursor-based pagination

SELECT * FROM articles
WHERE id > 100000
ORDER BY id
LIMIT 10;

🧪 How to Verify After Optimization?

1. Check Current Connections

SHOW STATUS LIKE 'Threads_connected';

2. Check Buffer Pool Hit Rate (Higher is Better)

SHOW ENGINE INNODB STATUS\G
  • Check BUFFER POOL AND MEMORY section
  • Hit rate = (1 - (reads / read_requests)) * 100%
  • Ideal value > 99%

3. Analyze Slow Query Log

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

⚠️ Safety and Stability Recommendations

Risk Recommendation
Blindly increasing innodb_buffer_pool_size ❌ May cause system memory shortage, triggering OOM Killer
Not monitoring slow queries ❌ Can still be dragged down by bad SQL after optimization
Executing DDL directly on master ❌ Use pt-online-schema-change for online schema changes

✅ Recommended Configuration Combination (16GB Memory Server) 

[mysqld]
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: The Three Pillars of Database Optimization

  1. 1. Tune parameters: Let MySQL fully utilize hardware resources
  2. 2. Add indexes: Avoid full table scans and temporary tables
  3. 3. Monitor slow SQL: Use data-driven optimization, not "gut feeling"

A 4-core 16GB MySQL server can easily handle 3000~5000 QPS with proper optimization.

Don't let default configuration drag down your business!

Share this article

Back to Articles Back to Top