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