Optimizing MySQL performance is critical for high-traffic applications. This guide explains how to monitor and tune MySQL for efficient resource usage, focusing on modern defaults, InnoDB, and current best practices.
1. Check MySQL Status and Variables
Log in to your MySQL server:
mysql -u root -p
Check general statistics:
SHOW STATUS LIKE ‘%tables%’;
SHOW VARIABLES LIKE ‘table_open_cache’;
Analysis:
- Open_tables – Number of tables currently open
- Opened_tables – Tables opened because the cache was too small
Tuning tip:
If Opened_tables increases rapidly, increase table_open_cache in your my.cnf :
[mysqld]
table_open_cache = 4000
2. Thread Cache
Check thread usage:
SHOW STATUS LIKE ‘Threads_created’;
SHOW STATUS LIKE ‘Threads_connected’;
Explanation:
- Threads_created increases each time MySQL must create a new thread for a connection
- Threads_connected shows active connections
Tuning tip:
Increase thread_cache_size if Threads_created rises quickly, especially under high connection rates:
[mysqld]
thread_cache_size = 50
This allows threads to be reused, improving performance.
3. Key Buffer (for MyISAM)
Although InnoDB is the default storage engine, some legacy tables may still use MyISAM. Check:
SHOW VARIABLES LIKE ‘key_buffer_size’;
SHOW STATUS LIKE ‘Key_read%’;
SHOW STATUS LIKE ‘Key_write%’;
Guidelines:
- key_reads / key_read_requests < 0.01 → key_buffer_size is sufficient
- Increase key_buffer_size only if MyISAM tables are heavily used
[mysqld]
key_buffer_size = 256M
For modern MySQL, InnoDB buffer pool tuning is far more important than key_buffer_size.
4. InnoDB Buffer Pool (Modern Tuning)
For InnoDB-heavy databases (default in MySQL 5.7+):
SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
SHOW STATUS LIKE ‘Innodb_buffer_pool%’;
Tuning tips:
- Set to ~70-80% of available RAM for dedicated database servers
- Monitor Innodb_buffer_pool_reads vs Innodb_buffer_pool_read_requests
- Higher buffer pool size reduces disk reads and improves performance
[mysqld]
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8
5. Connection Limits and Timeout
SHOW VARIABLES LIKE ‘max_connections’;
SHOW VARIABLES LIKE ‘wait_timeout’;
SHOW STATUS LIKE ‘Max_used_connections’;
Guidelines:
- max_connections – Max concurrent connections
- wait_timeout – Seconds before idle connections are closed
Example tuning:
[mysqld]
max_connections = 500
wait_timeout = 15
max_connect_errors = 100
Adjust these based on your traffic patterns to avoid resource exhaustion.
6. Slow Query Logging
Logging slow queries helps identify performance bottlenecks.
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 5
log_queries_not_using_indexes = 1
- Logs queries taking longer than 5 seconds
- Optionally logs queries not using indexes
Analyze these logs regularly to optimize inefficient queries.
7. Query Cache (Removed)
Note: The Query Cache feature has been deprecated since MySQL 5.7 and removed in MySQL 8.0.
Modern performance gains come from InnoDB buffer pool, thread cache, and proper indexing, not query cache.
8. General Recommendations
- Use InnoDB for most tables
- Increase table_open_cache and thread_cache_size for high-traffic workloads
- Tune innodb_buffer_pool_size for large datasets
- Enable slow query logs to identify bottlenecks
- Monitor regularly and adjust based on metrics, not assumptions
9. Monitoring Useful Metrics
SHOW STATUS LIKE ‘Connections’;
SHOW STATUS LIKE ‘Threads_created’;
SHOW STATUS LIKE ‘Open_tables’;
SHOW STATUS LIKE ‘Opened_tables’;
SHOW STATUS LIKE ‘Innodb_buffer_pool_reads’;
These metrics help you determine if your server is efficiently using resources.
Conclusion
Modern MySQL performance tuning focuses on InnoDB buffer pool, thread reuse, and table cache management, along with regular monitoring of slow queries. Deprecated features like the query cache should be ignored, and tuning should always be guided by real-world metrics.
If you require help, contact SupportPRO Server Admin
