MySQL performance tuning innodb buffer pool query cache

MySQL Performance Tuning (innodb_buffer_pool, query_cache)

MySQL with default configuration — innodb_buffer_pool_size = 128M on a server with 64 GB RAM. Added broken query_cache (which is completely removed in MySQL 8.0), max_connections = 151 with hundreds of active users. Tuning is sequential work with memory, disk I/O and query planner.

InnoDB Buffer Pool: Main Parameter

Buffer pool is the cache of InnoDB data and index pages in memory. Similar to shared_buffers in PostgreSQL. The more the working data set fits in memory, the less MySQL goes to disk.

# /etc/mysql/conf.d/performance.cnf
[mysqld]

# 70-80% RAM for dedicated server
# For 32 GB RAM server:
innodb_buffer_pool_size = 24G

# Multiple buffer pool instances — reduce contention with parallel access
# 1 instance per 1 GB, minimum 1, maximum 64
innodb_buffer_pool_instances = 24

# Buffer pool warmup on restart (MySQL 5.7+)
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup  = ON

Check buffer pool efficiency:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

-- Key metrics:
-- Innodb_buffer_pool_reads       — disk reads (we want minimum)
-- Innodb_buffer_pool_read_requests — total buffer pool requests
-- Hit rate = (1 - reads/read_requests) * 100
-- Goal: > 99%

SELECT
    (1 - (variable_value / (
        SELECT variable_value
        FROM information_schema.global_status
        WHERE variable_name = 'Innodb_buffer_pool_read_requests'
    ))) * 100 AS hit_rate_pct
FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_reads';

InnoDB Redo Log

# Large redo log files = less frequent checkpoints = higher write throughput
# MySQL 5.7 and below: changes require stop, delete old files, restart
innodb_log_file_size = 1G    # 1-4 GB for high-load servers
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64M

# MySQL 8.0.30+: dynamic redo log, innodb_redo_log_capacity
innodb_redo_log_capacity = 4G

Query Cache (MySQL 5.7 and Below)

# Query cache — DISABLE. It's a mutex on entire cache with any table write.
# With high concurrency Query Cache becomes a bottleneck.
query_cache_type = 0
query_cache_size = 0

In MySQL 8.0 query_cache is removed. Proper cache for web applications is at application level (Redis, Memcached), not MySQL level.

Sort and Join Buffers

# Buffer for disk sorting (ORDER BY without index)
sort_buffer_size = 4M

# Buffer for JOIN without indexes (avoid such queries!)
join_buffer_size = 4M

# Temp tables in memory
tmp_table_size    = 256M
max_heap_table_size = 256M

# Buffer for reading during full table scan
read_buffer_size       = 2M
read_rnd_buffer_size   = 4M

sort_buffer_size is allocated per connection when sorting is needed — don't set too large with large max_connections.

Connections and Threads

# Maximum connections
max_connections = 500

# Thread cache — avoid creating new thread with each connection
thread_cache_size = 50

# Thread stack size (usually don't change)
thread_stack = 256K

# Timeouts for inactive connections
wait_timeout         = 300   # 5 minutes
interactive_timeout  = 300

# Open files (tables, data files)
open_files_limit      = 65535
table_open_cache      = 4000
table_definition_cache = 2000

I/O Tuning for SSD

# Flush method — O_DIRECT bypasses OS page cache (no double caching)
innodb_flush_method = O_DIRECT

# Parallel I/O
innodb_read_io_threads  = 8
innodb_write_io_threads = 8
innodb_io_capacity      = 2000   # disk IOPS (SSD ~10000, NVMe ~100000)
innodb_io_capacity_max  = 4000

# Adaptive flushing — MySQL decides when to more aggressively flush dirty pages
innodb_adaptive_flushing = ON

# Native AIO (Linux)
innodb_use_native_aio = ON

Slow Queries: Enable Logging

# Slow queries
slow_query_log           = ON
slow_query_log_file      = /var/log/mysql/slow.log
long_query_time          = 1      # seconds (1 = queries > 1 sec)
log_queries_not_using_indexes = ON  # even fast ones without indexes
min_examined_row_limit   = 100    # ignore queries examining < 100 rows

Analysis via pt-query-digest:

pt-query-digest /var/log/mysql/slow.log \
  --limit 20 \
  --output report > /tmp/slow_report.txt

Planner: EXPLAIN FORMAT=JSON

EXPLAIN FORMAT=JSON
SELECT o.*, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
  AND o.created_at > DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY o.created_at DESC\G

-- Look for: "access_type": "ALL" — full table scan, need index
-- "rows": 1000000 — large number of examined rows
-- "using_filesort": true — sorting without index

Optimal composite index for the query above:

ALTER TABLE orders
  ADD INDEX idx_status_date (status, created_at DESC);
-- Covers filter by status AND sorting by created_at

Monitoring via Performance Schema

-- Top queries by total execution time
SELECT digest_text,
       count_star,
       ROUND(avg_timer_wait / 1e12, 3) AS avg_sec,
       ROUND(sum_timer_wait / 1e12, 3) AS total_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name = 'mydb'
ORDER BY sum_timer_wait DESC
LIMIT 10;

-- Tables with most full scans
SELECT object_schema, object_name,
       count_read,
       count_full_scan
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema = 'mydb'
ORDER BY count_full_scan DESC
LIMIT 10;

Typical Tuning Result

On 32 GB RAM server with Laravel project (MySQL 5.7), before tuning:

• innodb_buffer_pool_size = 128M — hit rate 87%
• query_cache = ON — 40% of CPU time on mutex
• slow queries > 1s: 200–400 per minute

After tuning (buffer pool to 24G, query_cache OFF, indexes from slow log):

• hit rate 99.4%
• slow queries > 1s: 3–7 per minute
• p95 latency API dropped from 450ms to 85ms