Mysql – the optimal setting for innodb_buffer_pool_instances for a mid level AWS RDS server with 6gb buffer pool

amazon-rdsawsinnodbMySQLmysql-5.7

We have a MySQL 5.7 AWS RDS instance running on a db.m4.large instance. The basic resources for that instance type are:

  1. 7.5gb RAM
  2. 2 VCPU

RDS defaults to a calculation that allocates 75% of RAM to innodb_buffer_pool_size.

{DBInstanceClassMemory*3/4}

This ends up being a 6gb allocation:

mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
|                           6.000000000000 |
+------------------------------------------+

With MySQL 5.7 if you do not set the number of buffer pool instances, then MySQL now defaults to 8 pools. However the MySQL 5.7 manual states this when discussing buffer pool instances:

The total size you specify is divided among all the buffer pools. For
best efficiency, specify a combination of innodb_buffer_pool_instances
and innodb_buffer_pool_size so that each buffer pool instance is at
least 1GB.

For this reason, I plan on reducing the innodb_buffer_pool_instances to a number <= 6.

Here are my questions:

  1. Is there some correlation to be made between the number of CPU's and
    the buffer_pool_instances setting?
  2. Would it be better in this scenario to go with 4,5, or 6
    buffer_pool_instances, and if so, what is the reasoning behind that
    recommendation? My simplistic response would be to change the setting to 6 buffer pool instances to satisfy the "at least 1GB" recommendation, but I would be interested to hear from anyone who has looked into this further, or done benchmarking of this setting whether that be under RDS or not.

Best Answer

Short Answer: Not a problem.

Long Answer:

The "instances" feature is new (as of 5.5.4) and exists to speed up certain things when the system is running at peak load. But even then, it does not help the performance more than a few percent. For lightly loaded servers, the improvement is probably not measurable.

What about "8"? I have yet to hear of anyone getting in trouble when each instance is less than 1GB. I suspect it is "advice", not "a hard and fast rule" to make each instance at least 1GB. In fact, glancing at a few dozen servers, I see that almost half of them violate that. I see one machine with 10 instances for only 2GB of buffer_pool.

I would trust RDS to provide reasonable settings. If their settings are bad, they will be bad for all their clients. Imagine how much hassling RDS would get!

Related Question