What is held in memory.
WHAT THE BUFFER POOL HOLDS
Data and index pages recently read.
WHY IT MATTERS MOST
Reading from memory is orders of magnitude faster than disk.
WHAT THE HIT RATE MEASURES
The proportion of reads served from it.
WHAT A HIGH RATE MEANS
The working set fits.
WHAT A FALLING RATE MEANS
Data has outgrown memory, and performance will degrade.
WHAT TO DO ABOUT IT
Add memory, reduce data, or improve indexes so less is read.
WHAT THE OLD QUERY CACHE WAS
A cache of complete query results.
WHY IT WAS REMOVED
It required locking that hurt concurrency, and it invalidated on any table change.
WHAT REPLACED IT IN PRACTICE
Caching in the application layer.
WHY THAT IS BETTER
The application knows what can be stale and for how long.
WHAT SORT AND JOIN BUFFERS DO
Provide working space for operations.
WHAT HAPPENS WHEN THEY ARE TOO SMALL
The operation spills to disk, becoming far slower.
WHAT HAPPENS WHEN THEY ARE TOO LARGE
They are allocated per connection, and memory is exhausted.
WHY THAT CATCHES PEOPLE
A setting that looks harmless multiplies by connection count.
WHAT TO DO
Raise them modestly, and watch total memory.
WHAT TO MEASURE
Temporary tables created on disk.
