Innodb_buffer_pool explanation

Question

The title might be a bit misleading, as i am fully aware of the concept of innodb buffer pool, yet want to understand something specific about it's calculation process.

According to the mysql docs:

On a dedicated database server, you might set the buffer pool size to 80% of the machine's physical memory size.

Now, how does the percentage approach makes sense here when we are talking about actual numbers?

Lets say that currently my db uses 80 gb of ram and 20 gb of ram is left for other tasks. After a period of time, my database grows bigger and i decide to increase the buffer pool size. To keep the 80% proportion, i have to increase the total ram of the server, even though other processes did not require more ram memory. Why not just add the additional ram while keeping the same 20gb of ram for the other tasks?

Answer 1

The 80% is just a general rule of thumb for helping people start with a more sensible value than the default.

Buffer pool size calculation can actually be more complicated. Ideally frequently accessed data pages should remain in the buffer pool and you don't want too much eviction to happen because of its impact on performance. This is again generic but a more realistic measurement to have the buffer pool large enough to accommodate hot data + enough space for avoiding unfrequent reads triggering too much eviction.

Just because you have a database 1TB big you don't have to have 1Tb of buffer pool if you only read the same 100MB all the time.

Keep in mind also that inserts and updates also affect buffer pool usage because the operation happens there. You should try to match the amount of data you can write into innodb logs (controlled by innodb_log_file_size) and the space required for this in buffer pool. Even if you have large innodb log files but you don't have enough space for insert than eviction will cause flushing data pages to tablespace which in essence have the same effect as checkpointing.

One empirical way to correctly size buffer pool is to set as high as your datasize (or as large as possible) and run normal production load on it while decreasing the size constantly while measuring transaction rate, response time and threads_running. When you see performance starting to drop that's the size that you need at least.

Answer 2

I prefer to say "70% of available RAM". So, 70% * (80-20) = 56. But, in reality, it is hard to predict how much 'other' memory mysqld will use.

Approaching from the other side, the buffer_pool does not need to be much more than the total of all the Data_length + Index_length as seen from SHOW TABLE STATUS.

Here is a longer discussion.

Answer 3

The ultimate aim is to keep the entire database in memory for performance gain. But, practically it is not always possible. For example, consider the database size of 2 TB. In this case, we cannot allocate 2 TB RAM due to resource constraints. So we should try to keep at least working data set in memory. It is a very complex process to identify the actual working set data, so we should try set maximum possible value. So, here 80% of the total available memory will be a good choice. Since we need memory for other processes too.

When you allocate entire memory to an InnoDB buffer pool, other processes compete for memory.This will lead to swapping. Swapping in a database server is very worst. So you have to start from less to a high value for innodb_buffer_pool_size variable based on the requirement.

To answer your question, when you know that database size is growing, just start with 80% of the total available RAM and start monitoring your InnoDB memory status. If MySQL uses above 95% of InnoDB buffer pool, then you should consider increasing the innodb_buffer_pool_size. Some of the useful commands to do this:

show global status like '%innodb_buffer_pool_pages_data%';
show global status like '%innobd_buffer_pool_pages_free%';
show global status like '%innodb_page_size%';
show global status like '%innodb_buffer_pool_reads%';
show global status like '%innodb_buffer_pool_read_requests%';

and one more:

SHOW ENGINE INNODB STATUS;

Find the Free Buffers and Buffer Pool hit ratio on BUFFER POOL AND MEMORY section. Check this page for an understanding of the InnoDB buffer pool, related variables and how to allocate memory to InnoDB buffer pool.

It is safe to keep the 20% of the remaining memory for other processes to avoid swapping in a worst case. I hope this will help you!.