We have a MySQL 5.7 AWS RDS instance running on a db.m4.large instance. The basic resources for that instance type are:
- 7.5gb RAM
- 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:
- Is there some correlation to be made between the number of CPU's and the buffer_pool_instances setting?
- 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.