MySQL Innodb buffer pool tuning
Article ID: 197535
Updated On: 10-16-2023
Products
Issue/Introduction
Reports are generally running slower on larger data sets and data processing is slower than expected.
Environment
Release: All Supported Releases
Resolution
By default in Spectrum 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.
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
How to check the size
mysql> SHOW VARIABLES LIKE '%innodb_buffer%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_in_core_file | ON |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
+-------------------------------------+----------------+
11 rows in set (0.01 sec)
Feedback
