Is InnoDB Buffer Pool big enough?

Last Updated on

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.

The official recommendation from MySQL is to keep it 80% of your available memory. The official MariaDB recommendation is to keep it 70-80% of your memory. Both are wrong.

An Amsterdam canal
Amsterdam, Holland

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 classical answer “it depends”. Unfortunately, the classical 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 more useless.
  • Other buffers need our memory. In particular, session buffers may be quite fat if concurrency is high and users run many big joins.

This is good but quite generic advice. Normally we don’t know the size of the most frequently 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. 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… if 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 too 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 free pages. In this way, when new data must be added to the buffer pool, InnoDB can just write into a free page.

It could happen that no free pages are available. There could be several reasons for this, like 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 it doing so it also has to evict some data that is probably frequently read too.

Note that, while such an emergency flush occurs, a user thread is waiting.

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.

Disk writes

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 variables is a good indicator of its efficiency.

Recap

We can check these metrics:

Reference

MySQL:

MariaDB:

See also

Articles:

Courses:

Conclusions

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, I really suggest you consider a MySQL Health Check. Chances are, there are many things we can do to improve your database performance.

Toodle pip,
Federico

Leave a Reply

Your email address will not be published. Required fields are marked *