Slicie is currently free for United States residents.

You can request an invitation at slicie.com/invite

Tuning the InnoDB Buffer Pool

The InnoDB Buffer Pool is a popular MySQL setting to change; however, it is also the most common way for people to waste memory on their server.

TLDR: Look at the hit rate and tweak over time

In MySQL, you can run show engine innodb status\G; to see how effectively your buffer pool is utilized.

Look at the buffer pool hit rate in the section titled BUFFER POOL AND MEMORY.

MySQL will eventually consume all of the memory you make available to the innodb buffer pool, which can impose serious diminishing returns.

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?

See our Terms of Service and Privacy Policy. Slicie LLC reserves the copyright of this content. If you wish to redistribute copies of the information on this page, you must be given permission from us.