MySQL Performance Tuning: InnoDB, Caches, and Query Optimization
Imagine: an e-commerce site handling 50,000 orders per day, MySQL with default configuration — innodb_buffer_pool_size = 128M, query_cache enabled, max_connections = 151. Result: crashes every 2 hours, p95 latency 450 ms. After tuning — 85 ms, 5.3 times faster. Our engineers with 10 years of experience and 500+ projects tune InnoDB, caches, and queries for your workload. p95 latency drops from 450 ms to 85 ms — 5.3x improvement. Proper buffer pool configuration boosts hit rate to 99.4%. In one project, write performance increased 3x after enlarging the redo log.
How to Tune InnoDB Buffer Pool?
InnoDB is MySQL's primary storage engine. Its buffer pool is the main cache for data and index pages. As per the MySQL Performance Tuning Guide, it should occupy 70–80% of RAM on a dedicated server. Example configuration for a server with 32 GB RAM:
[mysqld] innodb_buffer_pool_size = 24G innodb_buffer_pool_instances = 24 innodb_buffer_pool_dump_at_shutdown = ON innodb_buffer_pool_load_at_startup = ON innodb_log_file_size = 1G innodb_log_files_in_group = 2 innodb_log_buffer_size = 64M innodb_flush_method = O_DIRECT innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_io_capacity = 2000 innodb_io_capacity_max = 4000 innodb_adaptive_flushing = ON innodb_use_native_aio = ON max_connections = 500 thread_cache_size = 50 thread_stack = 256K wait_timeout = 300 open_files_limit = 65535 table_open_cache = 4000 table_definition_cache = 2000 Check effectiveness via hit rate:
SELECT (1 - (SELECT variable_value FROM information_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads') / (SELECT variable_value FROM information_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests')) * 100 AS hit_rate_pct; Target: hit rate >99%. If lower, increase buffer pool or optimize indexes. In a typical project, hit rate rises from 87% to 99.4%.
Why You Must Disable Query Cache?
query_cache in MySQL 5.7 and below uses a mutex on the entire cache on every write. On high-load sites, up to 40% of CPU time is spent on query_cache_mutex. In MySQL 8.0, it is removed. Application-level caching (Redis, Memcached) is the correct solution. Disable it in config:
query_cache_type = 0 query_cache_size = 0 How to Find and Optimize Slow Queries?
Enable slow query log and analyze with pt-query-digest. Typical issues: full table scans, missing indexes, sorting without indexes. Slow queries are those taking longer than 1 second.
slow_query_log = ON slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1 log_queries_not_using_indexes = ON min_examined_row_limit = 100 Analysis command:
pt-query-digest /var/log/mysql/slow.log --limit 20 --output report > /tmp/slow_report.txt Use EXPLAIN FORMAT=JSON for analysis. Look for "access_type": "ALL" (full table scan) and "using_filesort": true. Create composite indexes, e.g.: ALTER TABLE orders ADD INDEX idx_status_date (status, created_at DESC);.
How to Tune Redo Log for Maximum Write Performance?
Increasing innodb_log_file_size (or innodb_redo_log_capacity in MySQL 8.0) reduces checkpoint frequency, boosting write throughput. On high-load sites, this lowers latency and increases transaction throughput. In one project, after increasing log file size from 256M to 1G, write performance increased 3x.
Before and After Tuning Comparison
| Parameter | Before Tuning | After Tuning |
|---|---|---|
| innodb_buffer_pool_size | 128M | 24G |
| Hit rate | 87% | 99.4% |
| Query cache | Enabled (40% CPU on mutex) | Disabled |
| Slow queries >1s / min | 300 | 5 |
| p95 latency API | 450 ms | 85 ms |
| Query type | Before Tuning | After Tuning |
|---|---|---|
| SELECT heavy join >1M rows | 12 sec | 0.7 sec |
| INSERT with triggers | 200 ops/sec | 1,200 ops/sec |
Our Tuning Process
- Configuration audit: review my.cnf, buffer pool, redo log, buffers, I/O. Capture current metrics via Performance Schema.
- Slow log analysis: download logs for a week, run through pt-query-digest, select top 20 by time. For each, run EXPLAIN.
- Parameter tuning: adjust buffer pool, redo log, buffers, connections, I/O. Restart MySQL during maintenance window.
- Query optimization: create/refine indexes, rewrite heavy JOINs, add caching.
- Monitoring: deploy Performance Schema, set up alerts for slow queries and hit rate. Document with report.
What's Included
- Configuration audit: check mysql config, buffer pool, redo log, buffers, I/O.
- Slow log analysis: identify top 20 heavy queries, index recommendations.
- Performance tuning: parameter adjustments, index optimization, query restructuring.
- Monitoring: deploy Performance Schema, scripts for regular checks.
- Documentation: report with changes, rationale, and measurement results.
- Support: 2 weeks post-tuning for adjustments.
Cost Savings from Tuning
On average, clients save a significant amount per month after optimization. This not only reduces infrastructure costs but also improves application speed, directly impacting conversion rates. Our engineers will calculate the savings for your project individually.
To achieve similar results, contact us — we will tune your MySQL. Order optimization and we guarantee measurable results. Get a consultation: we will assess your project within 1-2 days.