Tuning the InnoDB Buffer Pool

What is the InnoDB Buffer Pool?

If your database's table is using the InnoDB Storage Engine (most popular applications do), the InnoDB Buffer Pool is used to cache access to data it frequently uses. The buffer pool will remove less frequently accessed data from the buffer pool over time, and it is designed to keep frequently accessed data in the buffer pool.

Why does it matter?

The reason it's important to correctly tune the InnoDB Buffer Pool is that you want to avoid doing IO to the storage when MySQL could instead access the same data in the much faster RAM.

Accessing the storage on a server is much slower than accessing data from RAM. Slicie's servers utilize Intel Optane NVME for storage, which can be read from in about 35 microseconds; however, reading the same data from RAM can still be 100 times faster.

What is the normal (wrong) advice?

The first result on Google states, "Typically, a recommended innodb_buffer_pool_size value is 50 to 75 percent of system memory."

MySQL's Documentation states, "On dedicated servers, up to 80% of physical memory is often assigned to the buffer pool.."

The common advice is wrong on any server, but it's more obvious as to why it's wrong on a Cloud Server with Slicie. On our servers, your memory scales up automatically as your usage changes. On Slicie, you could allow your server to scale up to many hundreds of gigabytes. So how much memory would you give MySQL with that setting? Would you give it 50-75% of the total potential of your server? This would ensure you would use that amount of memory because MySQL will use all of the memory you make available to it.

On Slicie, if you manually assigned your server 8 GiB of RAM and gave MySQL's InnoDB Buffer Pool 4 GiB, why would MySQL's needs for the InnoDB Buffer Pool change if you bumped your server up to 32GiB? The demand on your MySQL service would not change, and the workload would remain exactly the same, so why would it suddenly make sense to quadruple the memory you assign to the buffer pool?

How do I set it correctly?

It's not that complicated - but it does require a bit more work than simply saying, "give it 50%!"

If you don't have any idea of what this value should be because you've never calculated it correctly, start with something like 2-4 GB. If you're using Slicie, this will just work automatically because your server will scale to allow for it, but if you're on an old hosting plan, make sure you actually have enough memory available to do so.

Once your application is running and interacting with MySQL, you're going to look at how effectively the buffer pool is being used. Rather than just guessing a random number, we're going to make an educated guess that we can change over time.

In SSH, go into MySQL and run this command: show engine innodb status\G;

You will see a result like this:

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2226126848
Dictionary memory allocated 5664736
Buffer pool size   131072
Free buffers       8193
Database pages     120319
Old database pages 44251
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2541617, not young 100241547
142.36 youngs/s, 4.25 non-youngs/s
Pages read 10400593, created 169618, written 18630295
0.50 reads/s, 0.00 creates/s, 13.37 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 2 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 120319, unzip_LRU len: 1097
I/O sum[9272]:cur[0], unzip sum[0]:cur[0]

In order to keep things simple, the only line we're looking at is this one:

Buffer pool hit rate 1000 / 1000

This is an example of a server that is wasting memory. This is, unfortunately, very common. What this is telling you is that 1,000 out of 1,000 times, the request was satisfied by the buffer pool. While that sounds literally perfect, what you don't know is how much less memory could have achieved the same result.

With Slicie, you pay for what you use, so if you can get a nearly identical level of performance by spending less money on RAM that's good.

What InnoDB buffer pool hit rate should I want?

You want to periodically check this value when you're considering tuning the memory for MySQL. If you're typically in the 99.0%+ range (990 / 1000), that likely has such a small performance impact that you won't be able to measure it in your application. Here are a few simple rules for the hit rate:

• 100% (1000 / 1000): You're wasting RAM... maybe a lot of it!
• 99.9% (999 / 1000): Your database is larger than the RAM assigned to the buffer pool, but you're getting diminishing returns on the cost of RAM. You should still lower your buffer pool.
• 99.0% (990 / 1000): This is a good place to be. You're probably not going to benefit much from being better than this, and to try and do so might require a lot more memory for the buffer pool. Going lower than this might save you money on RAM, but it might start to be noticeable.
• 95% (950 / 1000): If you have a large MySQL database, this might be totally fine. In some situations, it's not practical to achieve 99%+ effectiveness of the buffer pool. Some eCommerce sites may have massive catalogs that can't practically be stored entirely in memory.
• Below 50% (500 / 1000): The reality is that this, or any number, can happen with a unique enough use case. You want to experiment with increasing the buffer pool size and see if it's worthwhile to pay for more memory.

Wait... can you make this more complicated?

If your hit rate is already 99%+, you may be wondering how "how much memory is wasted". This line helps a bit:

142.36 youngs/s, 4.25 non-youngs/s

The InnoDB buffer pool basically is made up of a list of pages that it's keeping in memory. By default, this list is broken up between "young" and "non-young" pages and the oldest 3/8ths of the pages are considered "non-young". In this example, it's saying that of the InnoDB access, 142.36 of them are recently accessed in the list and just 4.25 are in the "end" or bottom 3/8ths of the list. As in this example, nearly all of the accesses are to the portion of the buffer pool that is more recent, indicating that it's not close performing poorly. If this were a 10 young to 1 non-young ratio, it would be indicative that the hit-rate is likely suffering.

Wait... so what hit rate should I want?

Ultimately RAM costs money, but performance also has value. You need to decide what is a worthwhile trade-off between performance and cost. The purpose of this page is to explain that it is not as simple as some arbitrary percent of your total memory and that most people following that advice wind up completely wasting their money on memory.