MySQL interview question · Most asked
What is the InnoDB buffer pool, and how do you size it?
Summary
The buffer pool is InnoDB's in-memory cache of data and index pages. Reads and writes go through it, so most performance depends on the working set fitting in it. On a dedicated database server it is commonly set to around 50-75% of RAM.
Longer Answer
Pages are managed with a modified LRU (new pages enter at a midpoint so a one-off full scan does not evict the hot set). Dirty pages are flushed in the background. Watch the buffer pool hit rate (Innodb_buffer_pool_reads vs read_requests) and free pages. Leave memory for connections, temporary tables, the OS and other processes; swapping is far worse than a smaller buffer pool. innodb_dedicated_server=ON sizes it automatically. In 8.0+ the size can be changed online.
Key Points
- Cache for data and index pages.
- Working set should fit.
- ~50-75% of RAM on dedicated servers.
- Never let the server swap.
More most-asked MySQL questions
- What is a clustered index in InnoDB, and why does primary key choice matter so much?
- Why is OFFSET pagination slow on large tables, and what is keyset pagination?
- How do you read EXPLAIN output in MySQL?
- Why might MySQL not use an index you created?
- What are the transaction isolation levels, and which does InnoDB use by default?
- How does a composite index work, and what is the leftmost-prefix rule?
- What causes deadlocks in MySQL, and how do you handle them?
- What are gap locks and next-key locks, and why do they surprise developers?