MySQL Tuning, Indexing & Concurrency
8-10 Years (Tech Lead)
Hard
Production Scenario
How does the InnoDB Buffer Pool manage dirty pages, and how do you size `innodb_buffer_pool_size` for production servers?
Real-World Scenario / Incident Description
A dedicated MySQL database server with 128GB RAM was running out of memory because buffer pool and OS page caches were misconfigured.
795 views
•
Updated 1 month ago
Sizing innodb_buffer_pool_size
On a dedicated database server, set innodb_buffer_pool_size to 70% - 80% of total system RAM:
- For 128GB RAM:
innodb_buffer_pool_size = 96G - Configure multiple buffer pool instances to reduce mutex contention:
innodb_buffer_pool_instances = 8
Related Concepts:
#Buffer Pool
#InnoDB
#Memory Sizing
#Dirty Pages
More MySQL Tuning, Indexing & Concurrency Interview Questions
How does the InnoDB B+Tree Index work internally, and why does Leftmost Prefix Rule dictate composite index column ordering?
Read →
Explain InnoDB Locking Modes: Record Locks, Gap Locks, and Next-Key Locks, and how they prevent Phantom Reads.
Read →
How do you optimize deep pagination (`SELECT * ... LIMIT 5000000, 20`) on large tables with 100M rows?
Read →
How do you interpret MySQL `EXPLAIN ANALYZE` output to diagnose `Using filesort` and `Using temporary`?
Read →
Explain MySQL Table Partitioning (Range, List, Hash) vs. Application-Level Sharding for 1TB+ databases.
Read →