You can configure the innodb_buffer_pool_size parameter of an ApsaraDB RDS for MySQL instance based on your business requirements to improve the performance of the instance.

Prerequisites

The RDS instance runs one of the following RDS editions:
  • High-availability Edition
  • Basic Edition

Memory allocation

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

Storage type mysqld program Management service Operating system
Local SSD The memory that is allocated to the mysqld program is used for various purposes. Most of the memory space is occupied by the InnoDB buffer pool and database connections. For more information about the memory that is allocated to the mysqld program, see the MySQL official documentation.
Notice The memory capacity that is required increases with the number of database connections. 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 SSD or enhanced SSD (ESSD) The management services occupy approximately 500 MB of memory. The operating system occupies approximately 500 MB to 700 MB of memory.

Size of the InnoDB buffer pool

You can reconfigure the innodb_buffer_pool_size parameter to adjust the size of the InnoDB buffer pool for an RDS instance. The value of this parameter must be in the following format:

{DBInstanceClassMemory × X/Y}    

Example:

{DBInstanceClassMemory × 7/10}
Note
  • DBInstanceClassMemory is a system variable, which specifies 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]. The minimum size is 128 MB, and the maximum size is 80% of the memory capacity that you 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 SSDs or ESSDs with a purchased memory capacity that is less than 16 GB, the default size of the InnoDB buffer pool is calculated by using the following formula: Default size of the InnoDB buffer pool = (Purchased memory capacity of the RDS instance - Reserved memory capacity of the RDS instance) × 0.75.
    Note The reserved memory capacity of the RDS instance is calculated by using the following formula:
    MIN{Purchased memory capacity of the RDS instance × 0.65, [(Purchased memory capacity of the RDS instance/16384) + 1] × 2048}
  • If the RDS instance uses standard SSDs or ESSDs with a purchased memory capacity that is greater than or equal to 16 GB or the RDS instance uses local SSDs, the default size of the InnoDB buffer pool is calculated by using the following formula: Default size of the InnoDB buffer pool = Purchased memory capacity of the RDS instance × 0.75.
Note The default size of the InnoDB buffer pool is an integer multiple of 128. If the calculated result is not an integer multiple of 128, an approximate integer that is an integer multiple of 128 is taken. For example, an RDS instance has a memory capacity of 1,024 MB, the calculated result is 268, and the approximate integer that is an integer multiple of 128 is 256. In this case, the default size of the InnoDB buffer pool for the RDS instance is 256 MB.

The following table provides the default size and maximum size of the InnoDB buffer pool for various memory capacities.

Memory capacity (Unit: MB) Default buffer pool size (Unit: MB) Maximum buffer pool size (Unit: MB)
1,024 256 256
2,048 512 512
4,096 1,536 1,536
8,192 4,608 4,608
16,384 12,288 12,288
24,576 18,432 19,456
32,768 24,576 25,600
49,152 36,864 38,912
65,536 49,152 52,224
98,304 73,728 77,824
131,072 98,304 104,448
196,608 147,456 156,672
229,376 172,032 183,296
262,144 196,608 208,896
393,216 294,912 314,368
491,520 368,640 393,216
786,432 589,824 628,736

The size of the InnoDB buffer pool must be a multiple of the result that is obtained by using the following formula: Value of the innodb_buffer_pool_chunk_size parameter × Value of the innodb_buffer_pool_instances parameter. If the size of the InnoDB buffer pool is not a multiple of the result, ApsaraDB RDS changes the size to a multiple of the result. For example, if the result that you obtain by using the formula is 1 GB and you set the innodb_buffer_pool_size parameter to 1.5 GB, ApsaraDB changes the value of the innodb_buffer_pool_size parameter to 2 GB.

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

  1. Visit the RDS instance list, select a region above, and click the target instance ID.
  2. In the left-side navigation pane, click Parameters.
  3. Find the innodb_buffer_pool_size parameter and click the The Edit button icon. In the dialog box that appears, enter a new value and click OK.
    Warning After you change the value of the innodb_buffer_pool_size parameter for an RDS instance, the instance restarts. Proceed with caution.
    Edit the parameter
  4. Click Apply Changes above the parameter list. In the message that appears, click OK. Then, wait for the RDS instance to restart.
    The Apply Changes button

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

You can create a parameter template that contains the innodb_buffer_pool_size parameter. Then, you can apply the template to multiple RDS instances to change the size of the InnoDB buffer pool for these RDS instances. If a parameter template that contains this parameter has been created, you need only to change the value of this parameter in the template and 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