We have all been there when a query to the database takes longer than usual and sometimes it feels like forever. There has been always been complaints about mysql database being slow. There are quite a few reasons for the query being slow. One of them being the buffer pool size is too small. To understand the problem first we need to take a look at the buffer pool size itself.

What is Buffer Pool?

Memory in a computer system is used to get faster access to the most commonly used data. This behavior is known as caching. Caching is very important to improve the speed of accessing data. Accessing data from the disk itself can be much slower. For instance, reading from disk can be 10 to 100000 times slower, depending on the amount and structure of the data.

MySQL has two well-known database storage engines amongst others –

1. MyISAM
2. InnoDB

To see the available engines, log into MySQL

mysql -h hostname -u dbadmin -p

Please use the appropriate username and password according to your installation configuration.

To show the available storage engines, run

show engines;

The above command will result in showing all available storage engines. InnoDB should be the default mySQL storage engine.

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

MyISAM uses operating system’s cache to store frequently accessed data that queries read multiple times. On the other hand, InnoDB takes control over the caching mechanism with the help of buffer pool. So, in summary, the buffer pool is a caching mechanism used by the storage engine.

Current Buffer Pool Size

It is important to know the current InnoDB buffer pool size before one can set/reset or change the buffer pool size to a new value. Once logged into MySQL, run the following sql command to see current buffer pool size (GB)

SELECT @@innodb_buffer_pool_size/1024/1024/1024;

Current buffer pool size in GB

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

Increase/Decrease Buffer Pool Size

InnoDB buffer pool size can be configured in two ways – offline and online. Offline means, configuring at startup, and online means when the service is running.

Offline

To increase or decrease buffer pool size offline, edit my.cnf file [mysqld] section. If my.cnf includes /etc/mysql/conf.d/ and/or /etc/mysql/mysql.conf.d/ then [mysqld] section could be in /etc/mysql/mysql.conf.d/mysqld.cnf. Add the following lines

innodb_buffer_pool_size=1G
innodb_buffer_pool_instances=4

In the above example, innodb_buffer_pool_size is set to 1G, and innodb_buffer_pool_instances is set to 4. innodb_buffer_pool_chunk_size is 128M, which is the default value.

Once the configuration is done, restart MySQL service with

sudo systemctl restart mysql.service

Check that the buffer pool size has changed accordingly by running the following command in mysql terminal (GB)

SELECT @@innodb_buffer_pool_size/1024/1024/1024;

The above query will return the current buffer pool size in GigaBytes.

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

Buffer pool size must always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. If you configure innodb_buffer_pool_size to a value that is not equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances, buffer pool size is automatically adjusted to a value that is equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.

Online

To increase or decrease buffer pool size use the following command in mysql terminal (Bytes)

SET GLOBAL innodb_buffer_pool_size=2147483648;

In the above example, innodb_buffer_pool_size is set to 2G. Likewise innodb_buffer_pool_instances can also be set from mysql terminal.

mysql> SET GLOBAL innodb_buffer_pool_size=2147483648;
Query OK, 0 rows affected (0.00 sec)

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

But How Much Memory?

When it comes to allocating memory to buffer pool size, it is absolutely necessary to use caution and some calculations. As the data will be available in the buffer pool so one can allocate the amount equals to the entire database.

To be on the safe side and to rip the benefit of the buffer pool, it is often recommended to use 60%-80% of system’s total memory (in dedicated servers). In a shared environment, it’s much harder to calculate how much memory should be allocated because it all depends on the size of the database.

Once buffer pool size is allocated correctly you can now take the advantage of improved MySQL performance.

Enjoy!