AWS RDS MySQL – innodb_buffer_pool_size

InnoDB caches table and index data as it is accessed in a section of the main memory called the buffer pool.

One of the most important MySQL settings, it should be specified in accordance with the amount of RAM that is available on the server. In order to speed up processing, the buffer pool allows for direct memory access to frequently used data. On dedicated servers, the buffer pool may get up to 80% of the available physical memory. However, a suggested innodb_buffer_pool_size setting ranges from 50% to 75% of the total amount of system memory.

During server operation, innodb_buffer_pool_size can be dynamically configured.

The InnoDB buffer pool may become excessively large and consume excessive amounts of memory if the innodb_buffer_pool_size parameter is given a large value. The MySQL database engine may either cease to function or be prevented from starting as a result of this consequence. Classes of DB instance that have less memory available are more likely to experience this problem.

The best configuration is to provide the formula for this parameter rather than providing a fixed number. The formula is –
To allocate 50% of memory to innodb_buffer_pool_size = {DBInstanceClassMemory*1/2}
To allocate 75% of memory to innodb_buffer_pool_size = {DBInstanceClassMemory*3/4}

Hope this helps.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s