The innodb_buffer_pool_size parameter sets the size of the InnoDB buffer pool. You can adjust this parameter for your RDS MySQL instance based on your business requirements to improve instance performance.
Before you begin
Your RDS instance runs one of the following RDS editions:
RDS High-availability Edition
RDS Basic Edition
RDS Cluster Edition
Limits
RDS MySQL Serverless instances do not support manual modification of the innodb_buffer_pool_size parameter. The instance automatically adjusts the size of the InnoDB buffer pool based on specification changes.
If the innodb_buffer_pool_size parameter of an RDS MySQL instance is set too large, it may cause out of memory (OOM) and trigger a primary/secondary switchover. We recommend that you configure the parameter based on the maximum buffer pool value recommended in this topic.
Scenarios
In daily use, except when memory is limited, we recommend that you increase this parameter to optimize database performance.
Increase: When server memory is sufficient, increasing innodb_buffer_pool_size can effectively reduce disk I/O throughput. If the queried data can be completely or mostly stored in memory, the CPU does not need to frequently wait for disk I/O, which helps reduce CPU usage. For example, for frequently executed UPDATE statements, a larger buffer pool can reduce dependency on disks, reduce lock wait, and improve the efficiency of MySQL in processing data updates, especially when many records are updated.
Decrease: When memory resources are limited, or when other applications have high memory requirements, you can decrease the innodb_buffer_pool_size parameter value.
InnoDB buffer pool size
You can reconfigure the innodb_buffer_pool_size parameter to change the size of the InnoDB buffer pool for an RDS instance. Currently, you can modify this parameter only by using a formula. The formula is as follows:
{DBInstanceClassMemory*X/Y}
# Example: {DBInstanceClassMemory*7/10}DBInstanceClassMemoryis the memory of the RDS instance type (system variable), andX/Yrepresents the ratio of the buffer pool size to the instance memory.The adjustable range is: [128 MB, DBInstanceClassMemory * 9 / 10], which means the minimum value is 128 MB and the maximum value is 90% of the RDS instance memory.
Default values of InnoDB buffer pool size
High-performance local disk instances
For the following instance types, the default buffer pool size is 80% to 90% of the instance memory. For other instance types, the default value is 75% of the instance memory (instance memory * 3 / 4).
Instance family | Instance type | CPU and memory | Default value (MB) and percentage of instance memory |
General-purpose instance type | 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 instance | 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%) |
Disk instances
General-purpose instance types
The default buffer pool size is 75% of the instance memory (instance memory * 3 / 4).
Dedicated instance types
Default buffer pool size:
(instance memory - system reserved memory) * 3 / 4The 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 of instance memory is MB.
For your convenience, the following table provides the default buffer pool size and recommended maximum buffer pool size for dedicated instance types.
Instance CPU cores | Instance memory (MB) | Buffer pool size | |
Default value (MB) | Recommended 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 |
The capacity of the InnoDB buffer pool is always a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances:
innodb_buffer_pool_chunk_sizeis fixed at 32 MB. For instances created earlier, it might be 128 MB.When the
innodb_buffer_pool_sizevalue set by the user is not a multiple, the system automatically rounds 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 will actually be adjusted to 2 GB.
Change the size of the InnoDB buffer pool for a single RDS 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
on the right to modify it, and then click OK.Click Submit Parameters, and then click OK in the confirmation dialog box.
NoteModifying the
innodb_buffer_pool_sizeparameter for RDS MySQL 5.6 requires a restart, but this is not required for RDS MySQL 5.7 and 8.0.
Change the size of the InnoDB buffer pool for multiple RDS instances at a time
You can create a parameter template to modify innodb_buffer_pool_size and then apply it to the instances that need modification. If a parameter template that contains this parameter has been created, you need to only change the value of this parameter in the template. Then, you can apply the template to multiple RDS instances at a time. For more information, see Use parameter templates.
References
You can also analyze and handle RDS MySQL memory usage issues based on monitoring information and slow log analysis of your instance.