Adjust InnoDB buffer pool size for RDS for MySQL - ApsaraDB RDS

Prerequisites

This topic applies to the following instance editions:

RDS High-availability Edition
RDS Basic Edition
RDS Cluster Edition

Limitations

You cannot manually modify the innodb_buffer_pool_size parameter for an ApsaraDB RDS for MySQL Serverless instance. The instance automatically adjusts the InnoDB buffer pool size when its instance specification changes.
If the innodb_buffer_pool_size parameter for an ApsaraDB RDS for MySQL instance is set too high, it may cause an out of memory (OOM) error and trigger a failover. To prevent this, configure the buffer pool size according to the recommended maximum values in this topic.

Use cases

Unless memory is limited, increase this parameter to optimize database performance.

Increase the size: If the server has sufficient memory, increasing innodb_buffer_pool_size can significantly reduce disk I/O. If queried data can be fully or mostly stored in memory, the CPU waits less for disk I/O, reducing overall CPU utilization. For example, for frequently executed UPDATE statements, a larger buffer pool reduces dependency on disks and alleviates row lock contention. This improves data update performance, especially for large batches of records.
Decrease the size: If memory is limited or other applications require significant memory, decrease the innodb_buffer_pool_size value.

InnoDB buffer pool size

You can adjust the InnoDB buffer pool size by setting the innodb_buffer_pool_size parameter using the following formula:

{DBInstanceClassMemory*X/Y}
# Example: {DBInstanceClassMemory*7/10}

Note

DBInstanceClassMemory is the memory of the RDS instance specification (a system variable), and X/Y represents the ratio of the Buffer Pool size to the instance memory.
The adjustable range is from 128 MB to 90% of the total instance memory (DBInstanceClassMemory * 9 / 10).

Default InnoDB buffer pool size

High-performance local disk instances

For the following specifications, the default Buffer Pool size is 80% to 90% of the instance memory. For other specifications, the default size is 75% of the instance memory (instance memory * 3 / 4).

Instance family	Instance type	CPU and memory	Default value (MB) and percentage
General purpose	rds.mysql.s3.large	4 cores, 8 GB	7372 (90%)
	rds.mysql.m1.medium	4 cores, 16 GB	14745 (90%)
	rds.mysql.c1.large	8 cores, 16 GB	13107 (80%)
	rds.mysql.c1.xlarge	8 cores, 32 GB	27852 (85%)
	rds.mysql.c2.xlarge	16 cores, 64 GB	52428 (80%)
	rds.mysql.c2.xlp2	16 cores, 96 GB	78643 (80%)
Dedicated	mysql.x4.large.2	4 cores, 16 GB	14745 (90%)
	mysql.x8.large.2	4 cores, 32 GB	29491 (90%)
	mysql.x4.xlarge.2	8 cores, 32 GB	27852 (85%)
	mysql.x8.xlarge.2	8 cores, 64 GB	55705 (85%)
	mysql.x4.2xlarge.2	16 cores, 64 GB	52428 (80%)
	mysql.x8.2xlarge.2	16 cores, 128 GB	104857 (80%)

Cloud disk instances

General-purpose specifications

The default Buffer Pool size is 75% of the instance memory (instance memory * 3 / 4).

Dedicated specifications

Default Buffer Pool size: (instance memory - system reserved memory) * 3 / 4
The formula for calculating system reserved memory is: min{Instance Memory / 2, 2048} + max{Instance CPU * 64, Instance Memory / 64} + (Instance Memory / 64) + 285, where the unit for instance memory is MB.

The following table lists the default and recommended maximum buffer pool sizes for dedicated instance specifications.

CPU cores	Instance memory (MB)	Buffer pool size
CPU cores	Instance memory (MB)	Default value (MB)	Recommended max value (MB)
2	4096	1024	1024
2	8192	4096	4096
2	16384	9216	10240
4	8192	4096	4096
4	16384	9216	10240
4	32768	21504	22528
8	16384	9216	10240
8	32768	21504	22528
8	65536	45056	48128
12	24576	15360	16384
12	49152	33792	35840
12	98304	69632	73728
16	32768	21504	22528
16	65536	45056	48128
16	131072	93184	99328
24	49152	32768	34816
24	98304	69632	73728
24	196608	140288	149504
32	65536	45056	47104
32	131072	93184	99328
32	262144	188416	200704
52	98304	67584	72704
52	196608	140288	149504
52	393216	283648	302080
64	262144	188416	200704
64	524288	378880	403456
102	786432	569344	607232
104	196608	138240	147456
104	393216	282624	302080

Note

The InnoDB buffer pool size is always a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances:

The innodb_buffer_pool_chunk_size is fixed at 32 MB, but this value may be 128 MB for instances created earlier.
If the value that you set for innodb_buffer_pool_size is not a multiple, the system automatically rounds it up to the nearest integer multiple.

For example, when innodb_buffer_pool_chunk_size is 1 GB and innodb_buffer_pool_instances is 1, if you set innodb_buffer_pool_size to 1.5 GB, it is actually adjusted to 2 GB.

Adjust buffer pool for a single instance

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane, click Parameters.
Find the innodb_buffer_pool_size parameter, click the icon in the Actions column to modify its value, and then click OK.
Click Apply Changes. In the dialog box that appears, click OK.

Note
Modifying the innodb_buffer_pool_size parameter requires a restart for RDS for MySQL 5.6, but does not require a restart for RDS for MySQL 5.7 and 8.0.

Adjust with a parameter template

You can create a new parameter template to modify innodb_buffer_pool_size and then apply the template to the target instances. If you already have a parameter template, you can modify it and then apply it to the target instances. For more information, see Using parameter templates.

Set the Running Value to {DBInstanceClassMemory*7/10}. After applying the template, you must restart an ApsaraDB RDS for MySQL 5.6 instance for the change to take effect. No restart is required for versions 5.7 and 8.0.

ApsaraDB RDS:Adjust the InnoDB buffer pool size