Tuning Innodb_buffer_pool_size variable

The InnoDB buffer pool stores MySQL database indexes and data in memory. The innodb_buffer_pool_size variable sets InnoDB buffer pool size in bytes.

innodb_buffer_pool_size – Usage

Innodb_buffer_pool_size may be the most important variable for any MySQL database using InnoDB tables. Configuration of innodb_buffer_pool_size is highly recommended to reduce excessive I/O usage. I/O usage is the primary bottleneck for MySQL servers because it's slow, and high usage indicates that the disk subsystems are being overworked. This may markedly affect performance during peak usage and maintenance.

InnoDB buffer pool works by caching the most recently accessed data. By caching on memory, data can be retried from the cache instead of the disk. The buffer pool is designed to keep the most frequently-used data available in the cache, by managing two sublists.

• When new data is accessed, it is stored at the top of the 'older' list. The oldest item in this list is removed and will need to be retrieved from the disk if queried again.

• This data is moved to the top of the 'newer' list when it is queried again.

When the buffer pool is set correctly, data that is accessed often can be quickly retrieved from memory, as needed.

Releem collects information on your MySQL server and will automatically select the best configuration for innodb_buffer_pool_size, tailored to your database.

innodb_buffer_pool_size – Configuration

Innodb_buffer_pool_size can be configured offline or online, when the server is running. Configuration may be preferred online, to allow for testing. When the server is restarted the innodb_buffer_pool_size will revert.

Command Line Configuration:

mysqld> set global innodb_buffer_pool_size = XX
Replace XX with value to suit your database needs. To verify that the variable has been changed:

mysqld> show global variables like 'innodb_buffer_pool_size'

Configuration File:

[mysqld]
Innodb_buffer_pool_size = XX

Replace XX with value to suit your database needs. Restart MySQL server. Refer to considerations below if you get any errors concerning instances or chunk size.

innodb_buffer_pool_size – Considerations

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:

1. InnoDB also reserves additional memory for control structures or buffers, so the total space allocated to memory is roughly 10% greater than what the pool size is set to.

2. If innodb_buffer_pool_size is set too low or not set (default value maybe 8MB) expect extremely high input/output usage.

3. 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.

4. 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.

5. Account for the operating system and MySQL memory needs such as system processes, socket buffers, query cache, MySQL threads, and more.

Configuring innodb_buffer_pool_size Dynamically

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

Buffer pool size must always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.

Innodb_buffer_pool_size needs to be configured or resized to a factor of these two variables. For example, if the chunk size is 128MB (default) and there are 4 instances, then innodb_buffer_pool_size would be 512MB.

Important innodb_buffer_pool_instances and
innodb_buffer_pool_chunk_size are not dynamic. If you decide to change them, the server will need to be restarted.

Original article here: https://releem.com/docs/mysql-performance-tuning/innodb_buffer_pool_size