innodb_buffer_pool_size: Configure InnoDB Buffer Pool Size

Summary: in this tutorial, you will learn how to configure the size of the buffer pool using the innodb_buffer_pool_size configuration option to improve MySQL performance.

Buffer pool

In InnoDB, a buffer pool is an in-memory structure that caches frequently accessed data and indexes.

When you query data from a table, InnoDB reads data from the disk into the buffer pool. If the data is already in the buffer pool, MySQL can quickly retrieve the data from memory instead of performing a more time-consuming disk I/O access.

The buffer pool can significantly improve the read performance, especially when you have high read workloads. In general, the larger the buffer pool, the better the performance of the MySQL server.

A read workload refers to a scenario where the primary database operations are retrieving existing data, with a focus on querying rather than updating data.

To change the size of the buffer pool, you use the innodb_buffer_pool_size configuration variable.

The optimal size of the buffer pool depends on the available memory and the nature of the workload.

A general guideline is that you allocate about 80% of the available memory to the buffer pool on a server dedicated to MySQL.

Checking innodb_buffer_pool_size

First, connect to MySQL using the mysql client tool:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

Second, show the current value of the innodb_buffer_pool_size option:

SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';Code language: SQL (Structured Query Language) (sql)

Output:

+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.04 sec)Code language: SQL (Structured Query Language) (sql)

The value of the innodb_buffer_pool_size is in bytes. You can change it to megabytes using the following statement:

SELECT 
   ROUND(@@innodb_buffer_pool_size / 1024 / 1024,0) "innodb_buffer_pool_size (MB)";Code language: SQL (Structured Query Language) (sql)

Output:

+------------------------------+
| innodb_buffer_pool_size (MB) |
+------------------------------+
|                          128 |
+------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The default of the innodb_buffer_pool_size in MySQL 8.0 is 128MB.

Changing the buffer pool size using the innodb_buffer_pool_size parameter

First, change the value of innodb_buffer_pool_size in the MySQL configuration file (my.ini or my.cnf) to a bigger size, for example, 1GB, and save the file:

innodb_buffer_pool_size=1GBCode language: SQL (Structured Query Language) (sql)

Second, restart the MySQL server.

Third, connect to the MySQL server:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

Finally, show the value of the innodb_buffer_pool_size variable:

SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';Code language: SQL (Structured Query Language) (sql)

Output:

+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 1073741824 |
+-------------------------+------------+
1 row in set (0.02 sec)Code language: SQL (Structured Query Language) (sql)

The output indicates that the value has been updated successfully.

Note that if you have a buffer pool in the multi-gigabyte range, you should divide the buffer pool into separate instances to improve concurrency. To do that, you adjust the innodb_buffer_pool_instances configuration option.

Configuring innodb_buffer_pool_size online

MySQL allows you to set the innodb_buffer_pool_size dynamically using the SET statement. For example:

SET GLOBAL innodb_buffer_pool_size=1073741824;Code language: PHP (php)

This allows you to resize the buffer pool without restarting the MySQL server. Note that the change will not persist after you restart the MySQL server.

Summary

• Use the innodb_buffer_pool_size configuration variable to adjust the buffer pool size.
• Allocate 80% of memory to the buffer pool on the MySQL dedicated server.