You can reconfigure the innodb_buffer_pool_size parameter for an ApsaraDB RDS for MySQL instance to improve the performance of the instance.

Memory allocation

The following table describes how the memory capacity of an RDS instance is allocated. The memory allocation varies based on the storage type.

Storage type mysqld program Management service Operating system
Local SSD The memory capacity that is allocated to the mysqld program is divided into a number of parts. The major two parts are the memory capacity that is occupied by the InnoDB buffer pool and the memory capacity that is occupied by database connections. For more information about the memory allocation for the mysqld program, see the MySQL official documentation.
Notice A larger number of database connections indicates a higher amount of occupied memory capacity. We recommend that you expand the memory capacity of the RDS instance based on your business requirements.
The management services do not occupy the purchased memory capacity of the RDS instance. The operating system does not occupy the purchased memory capacity of the RDS instance.
Standard or enhanced SSD The management services occupy about 500 MB of memory. The operating system occupies 500 MB to 700 MB of memory.

InnoDB buffer pool specifications

You can adjust the size of the InnoDB buffer pool for an RDS instance by using the innodb_buffer_pool_size parameter. The value of this parameter must conform to the following formula:

{DBInstanceClassMemory*X/Y}    

Example:

{DBInstanceClassMemory*7/10}
Note
  • DBInstanceClassMemory is the system variable that is used to specify the memory capacity of the RDS instance.
  • X is the numerator, and Y is the denominator.
  • The size of the InnoDB buffer pool must be within the following range: [128 MB, DBInstanceClassMemory × 8/10]. Based on the range, the minimum size is 128 MB, and the maximum size is 80% of the memory capacity that you have purchased for the RDS instance.

The default size of the InnoDB buffer pool for an RDS instance is calculated based on the following rules:

  • If the RDS instance uses standard or enhanced SSDs with a purchased memory capacity less than 16 GB, use the following formula: Default size of the InnoDB buffer pool = (Purchased memory capacity of the RDS instance - Memory capacity required by the management services - Memory capacity required by the operating system) × 0.7.
  • If the RDS instance uses standard or enhanced SSDs with a purchased memory capacity greater than or equal to 16 GB or if the RDS instance uses local SSDs, use the following formula: Default size of the InnoDB buffer pool = Purchased memory capacity of the RDS instance × 0.7.

For stability purposes, the innodb_buffer_pool_size parameter is set to 256 MB by default for RDS instances that provide 1 CPU core and 1 GB or 2 GB of memory.

Multiply the value of the innodb_buffer_pool_chunk_size parameter and the value of the innodb_buffer_pool_instances parameter. The size of the InnoDB buffer pool must be a multiple of the obtained result. Otherwise, ApsaraDB RDS changes the size to a multiple of the obtained result. In this example, the result that you obtained by multiplying the value of the innodb_buffer_pool_chunk_size parameter and the value of the innodb_buffer_pool_instances parameter is 1 GB. In this case, if you set the innodb_buffer_pool_size parameter to 1.5, ApsaraDB changes the value of this parameter to 2.

Change the size of the InnoDB buffer pool for a single RDS instance

  1. Go to the Parameters page.
    1. Log on to the ApsaraDB for RDS console. In the left-side navigation pane, click Instances. In the top navigation bar, select the region where your RDS instance resides.
      选择地域
    2. Find your RDS instance and click its ID. In the left-side navigation pane, click Parameters.
  2. Find the innodb_buffer_pool_size parameter and click the Edit icon icon. In the dialog box that appears, enter a new value and click Confirm.
    Warning After you change the value of the innodb_buffer_pool_size parameter, the RDS instance restarts. Proceed with caution.
    Edit parameters
  3. Click Apply Changes above the parameter list. In the message that appears, click OK. Then, wait for the RDS instance to restart.
    Apply Changes button

Change the size of the InnoDB buffer pool for multiple RDS instances at a time

You can create a parameter template, set the innodb_buffer_pool_size parameter in the template, and then apply the template to multiple RDS instances. If a parameter template is created, you can change the value of this parameter in the template and then apply the template to multiple RDS instances. For more information, see Use a parameter template to manage parameters.

Edit parameters by using a parameter template