Server tuning follows a loop: measure, find the bottleneck, fix one thing, re-measure. The three highest-leverage areas are the InnoDB buffer pool, the slow query log, and connection handling.
The buffer pool caches data and index pages in RAM; when your working set fits in it, reads are memory-speed and disk I/O nearly vanishes. Size it to ~50–75% of RAM on a dedicated DB server.
innodb_buffer_pool_size ;
ENGINE INNODB STATUS\G
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5; -- log anything over 500 ms
-- Then aggregate with pt-query-digest / mysqldumpslow to rank the worst queries,
-- and EXPLAIN each one. Most wins are a missing index, not a server setting.
Each connection costs memory and threads. Don't set max_connections sky-high to mask a leak — a stampede of thousands of connections thrashes the server. Use a connection pool in the app (or a proxy like ProxySQL) to keep a small, reused set, and watch Threads_connected vs Threads_running.
SHOW GLOBAL STATUS LIKE 'Threads_running'; -- how many are actually doing work
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
Fix the top slow queries (indexes) first — that usually beats any config change. Then right-size the buffer pool. Only then tune max_connections, innodb_log_file_size, and flush settings. Change one variable at a time and re-measure.
Most "MySQL is slow" incidents are a handful of unindexed queries or a buffer pool too small to hold the working set — not exotic tuning. A senior engineer reaches for the slow log and buffer-pool hit rate first, fixing the real bottleneck instead of blindly cranking config knobs.
A library of IT interview questions with detailed answers — from Junior to Senior.
Donate