InnoDB buffer pool is the most important memory area to allocate. It contains the most frequently read data and index entries from InnoDB tables. Its size is determined by
innodb_buffer_pool_size, which is one of the two most important settings for performance.
Why is the rule of thumb wrong?
Because it makes you think that there is a magical number. In a technical documentation, magical numbers look much more sexy than the usual answer “it depends”. Unfortunately, the usual answer is given too many times because it’s true too many times.
The problems with this particular magic number are:
- A huge buffer pool can be slow.
- Having a buffer pool bigger than the data we most frequently read gives us very small benefit.
- Having a buffer pool bigger than the whole dataset is useless.
- Other buffers need memory. In particular, session buffers may be quite fat if concurrency is high and users run many big joins. Some tools may need memory, too (backup, etc).
So the universal rule is: keep the buffer pool as big as frequently read data. But this advice is too generic. Normally we don’t know the size of the most frequently accessed data. So let’s see how to find out if the buffer pool is too small.
Checking if your buffer pool is too small
There are several metrics you can check. You can see them with
SHOW ENGINE InnoDB STATUS, which is easy to read for humans. But it’s much better to check them in a graph, and see how they change over time. For that, you need some monitoring solution. Here we’ll mention the status variables to check. Depending on your monitoring solution, you will need to find the correct graphs to check.
Size of the free list
Innodb_buffer_pool_pages_free is the number of pages in the buffer pool that are not currently in use. But in many cases, you see a value and wonder… is this number too low? Is it big?
First of all, free pages should never be zero, or close to zero. And there is a rule of thumb that the free pages shouldn’t be less than 5% of the total pages, at least not often. But these are vague indications. Actually, this metric is clearly not enough.
Dirty pages flush
Innodb_buffer_pool_wait_free counts the time spent waiting until an “emergency flush” operation has ended. It shouldn’t grow often. But what does it mean?
InnoDB background threads always try to keep some pages free. In this way, when new data must be added to the buffer pool, InnoDB can just write it into a free page.
It could happen that no free pages are available. There could be several reasons for this, like busy CPU, slow disks or slow operating system sync functions. But normally this happens if the buffer pool is not big enough. If queries frequently read data that is not in the buffer pool, InnoDB keeps reading data from the disk and cache it. But while doing so it has to evict some data that is probably frequently read too.
Note that, while such an emergency flush occurs, a user connection is on hold.
Innodb_buffer_pool_pages_flushed counts the number of flushed pages. Flushed pages shouldn’t be too many, compared to the read pages. If it happens, caching pages does not save enough work for InnoDB. However, these are the total flushed pages, not just the pages flushed to quickly free some memory.
Innodb_buffer_pool_read_requests is the number of pages read. InnoDB tries to read each of them from the buffer pool first. If a page is not in the buffer pool, it is read from disk, and
Innodb_buffer_pool_reads is incremented.
The purpose of a buffer pool is to reduce disk reads, so the ratio between these counters is a good indicator of its efficiency.
We can check these metrics:
Innodb_buffer_pool_pages_freeshould not be zero or close to zero. We should always have free pages available.
- There is a general rule of thumb that it should normally be >= 5% of total pages.
Innodb_buffer_pool_wait_freeshould not happen. It indicates that a user thread is waiting because it found no free pages to write into.
Innodb_buffer_pool_pages_flushedshould be low, or we are flushing too many pages to free pages. Compare it with the number of read pages.
Innodb_buffer_pool_read_requestsshould be low, or the buffer pool is not preventing enough disk reads.
We saw some quick methods to find out if InnoDB is not big enough, taking a look at graphs. These are not the only ways. Feel free to comment, and let us know which methods you use. And remember: comments are welcome, and they help making this website a collective KnowledgeBase!
If you find out that your InnoDB buffer pool is not big enough, you may want to consider our MariaDB Health Check or our MySQL Health Check.If your buffer pool is too small, probably many more settings can be optimised, and that would sensibly improve your database performance.