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 0
Dictionary memory allocated 25583833
Buffer pool size   1572725
Free buffers       8207
Database pages     1538575
Old database pages 567788
Modified db pages  822
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1615114, not young 26547869
0.81 youngs/s, 0.00 non-youngs/s
Pages read 1278278, created 1723995, written 16679808
0.00 reads/s, 0.17 creates/s, 8.06 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1538575, unzip_LRU len: 0
I/O sum[3568]: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... 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.