You can configure the innodb_buffer_pool_size parameter for an RDS for MySQL instance to improve performance.

Background information

The memory of an RDS for MySQL instance is shared by the following items:
  • The mysqld services. The InnoDB buffer pool of the mysqld services use memory resources. The size of the InnoDB buffer pool is specified by the innodb_buffer_pool_size parameter.
  • The RDS management services.
  • The underlying operating system.

RDS instance with Local SSDs

On an RDS for MySQL instance with local SSDs, the memory resources can all be used to run the mysqld services, including the InnoDB buffer pool and other MySQL services such as session connections.

Local SSDs are assigned from isolated storage resources on the physical server that hosts the RDS instance. The RDS management services and operating system do not use memory resources of the RDS instance.

RDS instance with standard or enhanced SSDs

On an RDS for MySQL instance with standard or enhanced SSDs, the memory resources are used to run the mysqld services, RDS management services, and underlying operating system.

The standard or enhanced SSDs are deployed on different ECS instances. Therefore, memory resources must be allocated to run the RDS management services and underlying operating system.

Generally, the underlying operating system uses 500 MB to 700 MB of memory, and the RDS management services use 500 MB of memory.

InnoDB buffer pool

You can configure the innodb_buffer_pool_size parameter for an RDS instance to specify the size of the InnoDB buffer pool. The value is configured in the following format:

{DBInstanceClassMemory*X/Y}    

Example:

{DBInstanceClassMemory*7/10}
Note
  • DBInstanceClassMemory indicates the memory system variable of the RDS instance.
  • X indicates the numerator, and Y indicates the denominator.
  • The value range is [128 MB, DBInstanceClassMemory × 8/10]. It indicates that the minimum size of the InnoDB buffer pool is 128 MB and the maximum size is 80% of the RDS instance memory.

The default size of the InnoDB buffer pool for an RDS instance is as follows:

  • If the RDS instance memory is smaller than 16 GB, the default size of the InnoDB buffer pool is calculated as follows: (RDS instance memory - Memory required by the RDS management services - Memory required by the underlying operating system) × 0.7.
  • If the RDS instance memory is larger than or equal to 16 GB or the RDS instance uses local SSDs, the default size of the InnoDB buffer pool is calculated as follows: RDS instance memory × 0.7.

To ensure stability of the instance, the default value of the innodb_buffer_pool_size parameter is set to 256 MB for instances with 1 CPU core and 1 GB or 2 GB of memory.

The size of the InnoDB buffer pool must be a multiple of the calculation result of innodb_buffer_pool_chunk_size × innodb_buffer_pool_instances. If you specify a size that is not a multiple of the calculation result, the system automatically changes the size. For example, if the calculation result of innodb_buffer_pool_chunk_size × innodb_buffer_pool_instances is 1 GB and you set the innodb_buffer_pool_size value to 1.5 GB, the system change the value of the innodb_buffer_pool_size parameter to 2 GB.

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

  1. Log on to the ApsaraDB for RDS console.
  2. In the left-side navigation pane, click Instances. In the top navigation bar, select the region where the target RDS instance resides.
    Select a region
  3. Find the target instance and click the instance ID.
  4. In the left-side navigation pane, click Parameters.
  5. Find the innodb_buffer_pool_size parameter and click the Change the parameter value icon on the right to change the value. Then, click Confirm.
    Warning After you change the value of the innodb_buffer_pool_size parameter, the instance restarts. Proceed with caution.
    Edit the parameter
  6. In the upper-right corner of the parameter list, click Apply Changes. In the Edit Parameters dialog box, click Confirm. Then, wait for the instance to restart.
    Apply Changes

Change the size of the InnoDB buffer pool by using a parameter template

Create a parameter template and configure the value of the innodb_buffer_pool_size parameter. Then, apply the template to the target instance. If you have already created a parameter template, you can modify the template and then apply it. For more information, see Use a parameter template to manage parameters.

Modify the parameter template