Is InnoDB Buffer Pool big enough?

Last updated on 26 March 2021

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 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.

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

MariaDB

MySQL

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, you may want to consider a MariaDB Health Check or a 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.

Federico Razzoli

About Federico Razzoli

Federico is Vettabase Ltd director, and he's an expert database consultant specialised in the MariaDB and MySQL ecosystems.

2 Replies to “Is InnoDB Buffer Pool big enough?”

  1. Federico,
    Thank you for your great explanation. Another common “wisdom” used to be that the swap size should be set to the same size as the main RAM. I wonder how many computers (or smartphones or tablets) really need any swap space at all nowadays. And I hope that nobody would configure their buffer pool to be so big that it would cause excessive swapping.

    I would like to point out that in MariaDB Server 10.5, we simplified the buffer pool and the page flushing. There is only one buffer pool instance and one dedicated page cleaner thread. Here are some related tickets from https://jira.mariadb.org:

    MDEV-15058 gives some reasoning for moving to a single buffer pool instance
    MDEV-15053 and MDEV-22871 reduced buf_pool.mutex contention
    MDEV-23399 simplified the LRU page eviction and removed the single-page flushing. The LRU eviction is now being executed by user threads, not by the page cleaner thread anymore. This is what you refer to as “emergency flush”. I prefer to call it “eviction flushing”.
    MDEV-23855 reduced the latency of another “emergency flush”, which I call “checkpoint flushing”. When the circular redo log file fills up, we must perform a log checkpoint, which will logically discard the start of the log. If we allowed the end of the log to overwrite the start, then crash recovery would not work.

    These changes align with the Albert Einstein’s thought of “making things as simple as possible, but not simpler”. The last part is important, because our refactoring introduced an inadvertent regression:
    MDEV-24537 innodb_max_dirty_pages_pct_lwm=0 lost its special meaning

    At FOSDEM 2021, I will give a talk on buffer pool performance improvements in the MariaDB server.

    • Hi Marko!
      Einstein’s wisdom was amazing.
      Thanks for pointing out these changes. You mentioned some of them in our interview and our meetup in London, but it’s useful to have the links in one place.
      Thanks for also mentioning the regression. I wish that admitting limits and flaws were more common in software communities.
      I’m against swapping, especially in a cluster (having a crashed node is better than having a frozen cluster). But, talking of the buffer pool size… I saw some unreasonably big values for innodb_buffer_pool_size, almost equal to the RAM.
      I’ll be waiting to hear your talk at FOSDEM!

      Cheers,
      Federico

Leave a Reply

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

*