MySQL Innodb buffer pool tuning

book

Article ID: 197535

calendar_today

Updated On:

Products

CA Spectrum

Issue/Introduction

Reports are generally running slower on larger data sets and data processing is slower than expected.

Environment

10.4.2 / 20.2

Resolution

By default in Spectrum 10.4.2 / 20.2 the innodb settings are as follows:

 

innodb_file_per_table
innodb_buffer_pool_size = 3072M
innodb_log_file_size = 512M
innodb_log_buffer_size = 8M
innodb-buffer-pool-instances = 2
innodb_read_io_threads = 32
innodb_write_io_threads = 32
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:20G

 

In larger customer environments the innodb_buffer_pool_size should be increased to about 70-80% of available physical memory.

As a result, the innodb-buffer-pool-instances should also be increased.

As a general rule of thumb you will want at LEAST 1GB per instance of buffer pool size. 

For example, if you set the innodb_buffer_pool_size to 8GB you will want to set the instances to at most 8.

Another example would be setting the innodb_buffer_pool_size to 32GB and setting the instances to 16.  Giving each instance MORE than the 1GB minimum.

 

Note: These settings are also applicable to 10.4.0 and 10.4.1.  However, the following should be set on those versions:

 

innodb_read_io_threads = 32
innodb_write_io_threads = 32

 

These are already set to this on 10.4.2 and no changes are required to those variables.

 

 

Additional Information

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instances