1. Home
  2. MySQL Tutorials
  3. InnoDB Buffer Pool Size

InnoDB Buffer Pool Size

InnoDB Buffer Pool is the memory space in which indexes, caches, buffers and row data are stored. innodb_buffer_pool_size is the MySQL configuration parameter that specifies the amount of memory allocated to the InnoDB buffer pool by MySQL,this is one of the most important variable settings for MySQL configuration and size should be depend on available VM RAM.

Commonly 60-70% of physical memory can be allocated to InnoDB buffer pool size, Default size is 128M, Connect to MySQL and run below command to check,

mysql> SELECT @@innodb_buffer_pool_size;

Output:

mysql> SELECT @@innodb_buffer_pool_size;
 +---------------------------+
 | @@innodb_buffer_pool_size |
 +---------------------------+
 |                 134217728 |
 +---------------------------+
 1 row in set (0.00 sec)

Increase InnoDB Buffer Pool Size In MySQL 5.7

If you want change size permanantly , edit the configuration file /etc/mysql/mysql.conf.d/mysqld.cnf, Under the [mysqld] section add the following line:

[mysqld]
innodb_buffer_pool_size = 5G

To take effect restart the MySQL Server

$ sudo systemctl restart mysql

Increase InnoDB Buffer Pool Size In MySQL 8.0

Default size of InnoDB Buffer pool is 128 MB. To check enter below command on mysql prompt.

mysql> SELECT @@innodb_buffer_pool_size;

Output:

mysql> SELECT @@innodb_buffer_pool_size;
 +---------------------------+
 | @@innodb_buffer_pool_size |
 +---------------------------+
 |                 134217728 |
 +---------------------------+
 1 row in set (0.00 sec)

To change the value of InnoDB pool without restarting the server.

mysql> SET GLOBAL innodb_buffer_pool_size=4294967296;

MySQL 8.0 New Features

SET PERSIST:

MySQL 8.0 Introduced new features, We can set variables in mysql config file without restarting server. Persisted variables are stored in the file mysqld-auto.cnf located in the data directory folder in JSON format.

Enter following command to resize value using SET PERSIST.

mysql> SET PERSIST innodb_buffer_pool_size=4294967296;
Was this article helpful to you? Yes 1 No

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.