1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

What is the optimal setting for innodb_buffer_pool_instances for a mid level AWS RDS server...

Discussion in 'Programming/Internet' started by gview, Oct 8, 2018.

  1. gview

    gview Guest

    We have a MySQL 5.7 AWS RDS instance running on a db.m3.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?

    Login To add answer/comment
     

Share This Page