There are a lot of factors and considerations to remember when setting innodb_buffer_pool_size. Since innodb_buffer_pool_size is the number one variable to configure to improve MySQL server performance, you want to get it right. Let Releem do all the work for you. Releem will automatically configure your MySQL settings, including innodb_buffer_pool_size, to maximize performance and stability.
The recommendation for years in setting innodb_buffer_pool_size has been 80% of RAM. This rule of thumb should not be followed, except for small servers under 1GB. As the server size gets larger, RAM is more inefficiently used if innodby_buffer_pool_size is set to 80%. Below is a list of factors you should consider when setting or assessing innodb_buffer_pool_size:
Configuring innodb_buffer_pool_size Dynamically
- InnoDB also reserves additional memory for controls structures or buffers, so the total space allocated to memory is roughly 10% greater than what the pool size is set to.
- If innodb_buffer_pool size is set too low or not set (default value maybe 8MB) expect extremely high input/output usage.
- If innodb_buffer_pool is set to high, the OS may experience paging and regular swapping which will negative the performance benefits. No real need to set larger than the database itself.
- To determine if innodb_buffer_pool_size is large enough, check status variable innodb_buffer_pool_wait_free. If this is continually increasing, you did not allocate enough memory for your buffer pool.
- Account for the operating system and MySQL memory needs such as system processes, socket buffers, query cache, MySQL threads, and more
Innodb_buffer_pool_size has been able to be configured dynamically since MySQL 5.7.5 and MariaDB 10.2.2. To change innodb_buffer_pool_size while the server is online, innodb_buffer_pool_chunk_size and innodb_buffer_pool_instances must be considered.
The buffer pool is broken down into instances and the instances are divided into chunks.
Resizing of innodb_buffer_pool_size is achieved using chunks set by the innodb_buffer_pool_size_chunk_size variable. Errors will arise if innodb_buffer_pool_size is not set to a value that is compatible with the number of instances and chunk size.
You can determine the currently set value of innodb_buffer_pool_chunk_size by entering: SELECT @innodb_buffer_pool_chunk_size (if previously configured variable yourself) OR
show variables like %innodb_buffer_pool_chunk_size% (for MySQL system variables)
Innodb_buffer_pool_instances can be determined in the same manner.
The relationship between these three variables can be seen in this calculation: innodb_buffer_pool_size = innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances * N