MySQL Innodb buffer pool tuning


Article ID: 197535


Updated On:


CA Spectrum


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


10.4.2 / 20.2


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


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