DEV Community

Syed Umer Tariq
Syed Umer Tariq

Posted on

Configure Size of Innodb Buffer Pool

The size of the InnoDB Buffer Pool can be configured by setting the innodb_buffer_pool_size system variable. the InnoDB Buffer Pool should usually be between 50%-75% of the memory available.

There are two ways two configure innodb buffer pool size which are given as following

1) The size of the InnoDB buffer pool can be changed dynamically by setting the innodb_buffer_pool_size system variable using the SET GLOBAL statement which requires SUPER privilege.

To configure the InnoDB Buffer Pool with the SET GLOBAL statement, use the following procedure:

  • Connect to the server using MariaDB Client as the root@localhost user account or another user account with the SUPER privilege

  • Set the innodb_buffer_pool_size system variable to the new size using the SET GLOBAL statement.

For example, to set the size to 4 GB:

SET GLOBAL innodb_buffer_pool_size=(4 * 1024 * 1024 * 1024);

  • Confirm that the resize operation has been completed by querying the Innodb_buffer_pool_resize_status status variable using the SHOW GLOBAL STATUS statement

2) Changing the size of buffer pool by setting innodb_buffer_pool_size system variable in configuration file. Ensure that your custom configuration file is read last by using the z- prefix in the file name so that changes made by you are not overwritten.

innodb_buffer_pool_size needs to be set in a group that will be read by MariaDB Server, such as [mariadb] or [server]. When set in a configuration file, the value supports units such as "M" (Megabyte), "G" (Gigabyte), etc. For this method server restart is needed to reflect the changes.

Latest comments (0)