Tuning MySQL innodb buffer pools

To Nha Notes | March 4, 2021, 1:17 p.m.

As RDS MySQL default settings, the default value of innodb_buffer_pool_size is defined as below:

innodb_buffer_pool_size = {DBInstanceClassMemory*3/4}

And, as MySQL's recommendation, the innodb_buffer_pool_size value should be defined as multiple of innodb_buffer_pool_instances * innodb_buffer_pool_chunk_size.

For example: we have an MySQL RDS instance which has 128G memory.

So the innodb_buffer_pool_size = 3/4 * 128G = 93G.

Then we can configure:

innodb_buffer_pool_size = 24 instances
innodb_buffer_pool_chunk_size = 128MB

innodb_buffer_pool_size * innodb_buffer_pool_chunk_size = 24*128MB = 3G

So, we can see 93G = 31 * 3 = 31 * innodb_buffer_pool_size * innodb_buffer_pool_chunk_size is a valid configuration.