Increase the MySQL InnoDB buffer pool size

The InnoDB buffer pool is MySQL’s in-memory cache of the contents of tables and indexes.

For optimal performance, the InnoDB buffer pool should be large to hold the full contents of all databases. However, depending on the size of the databases and the performance requirements, it is not always financially practical to allocate that much memory to the buffer pool.

The default value is 134217728 bytes (128M).

You can use ServerPilot’s MySQL monitoring dashboards to know whether you need to increase the size of the InnoDB buffer pool.

Change innodb_buffer_pool_size

Create the file:

/etc/mysql/conf.d/innodb_buffer_pool_size.cnf

with the following contents (replace VALUE with the desired size).

/etc/mysql/conf.d/innodb_buffer_pool_size.cnf

[mysqld]
innodb_buffer_pool_size = VALUE

VALUE can be:

• An integer number of bytes.
• An integer with the suffix K, M, or G (case-insensitive) for kilobytes, megabytes, or gigabytes.

For example, the following sets the InnoDB buffer pool size to 500 megabytes.

/etc/mysql/conf.d/innodb_buffer_pool_size.cnf

[mysqld]
innodb_buffer_pool_size = 500M

After creating the file, restart MySQL.

sudo service mysql restart

Confirm the new value with the following command:

sudo mysql -e 'SELECT @@innodb_buffer_pool_size'