How to allocate innodb_buffer_pool_size in MySQL?

The innodb_buffer_pool_size the most important variable for entire MySQL server. Last few years, you have seen the drastic improvements in the InnoDB storage engine features. After Oracle acquiring the MySQL, the importance of InnoDB storage engine grows multiple times over MyISAM. Even though MySQL supports multiple pluggable storage engines, one of the main reasons is its ACID compliance properties. Recent improvements in the MySQL 5.7 such as online innodb_buffer_pool_size configuration made it as a highly flexible and allow us to get the best performance without long downtime.

Initially, when I start to configure the innodb_buffer_pool_size variable on my test environment, like most of us, I googled as, 

What is MySQL innodb_buffer_pool_size?

Do I need to change MySQL innodb_buffer_pool_size?

What are the recommendations for innodb_buffer_pool_size in MySQL?

What are the best practices for configuring innodb_buffer_pool_size in MySQL?

How to MySQL innodb_buffer_pool_size?

What is the best value for MySQL innodb_buffer_pool_size?

How to choose MySQL innodb_buffer_pool_size? 

What is the optimal value for MySQL innodb_buffer_pool_size?

How large should be the MySQL innodb_buffer_pool_size?

The google results for the above keywords given me insights about MySQL innodb_buffer_pool_size. In my recent task, I created a new MySQL replication slave server on my production environment. During this process, I just implemented, whatever I learned from my testing experience. 

The result was unbelievable, I was running MySQL 5.5 with 8 core CPU and 32 GB memory on a Linux platform. My backup file was around 320GB. The backup file took around 28 hours to restore on the MySQL 5.7 with default configuration on the similar server machine. Most of the tables are in InnoDB storage engine. After applying those InnoDB configurations, the restoration took just 6 hours to complete.

The dramatic change in the restoration time makes me dive deeper into the MySQL performance tuning. There are around 450 settings in MySQL 5.7, but I feel the most important variable setting is innodb_buffer_pool_size. This post is all about only MySQL innodb_buffer_pool_size. On my environment, most of the tables are in InnoDB, so this makes sense. It may be different in your case, still better understanding of this variable makes the life easier in most cases.

Why InnoDB buffer pool?

From the early stages of MySQL, the most widely used storage engine and default storage engine until MySQL 5.5 was MyISAM. MyISAM is using Operating System file cache to cache data that queries are reading over and over again. InnoDB handles caching itself, within the InnoDB buffer pool. So it is highly required to have enough InnoDB buffer pool space.

What is there inside MySQL InnoDB buffer pool?

MySQL InnoDB buffer pool contains the following things inside:

Data caching - InnoDB data pages.

Indices caching - index data.

Buffering data - Dirty pages - data which are modified in memory but not yet flushed (written) to a data disk.

Internal structures - InnoDB buffer pool additionally stores the internal structures such as Adaptive Hash Index, row level locks, etc.

The unit of the above-mentioned objects are calculated as - InnoDB pages. Each InnoDB pages is 16k in size.

How to choose the optimal InnoDB buffer pool size?

InnoDB buffer pool size is configured by the variable - innodb_buffer_pool_size. Before MySQL 5.7, this variable can be configured only at the offline. From MySQL 5.7 this can be configured online, does not require a server restart.

In an existing server, you can see the default value of innodb_buffer_pool_size by using the following command:

SHOW VARIABLES LIKE '%innodb_buffer_pool_size%';

It is recommended that the buffer pool should be capable of holding entire database. But when we consider the practical situation it is not always possible.

Let me tell you an example, my server machine has around 32GB of RAM. My database size is around 300 GB. In this scenario, it is not possible to allocate 300 GB of RAM to innodb_buffer_poool_size variable.  

How to deal with this situation?

When we cannot keep an entire database in memory, we should try to keep at least working data set in memory. In most cases, we are not going to process the data of an entire table, so the required data set should be in memory. So it is always better to allocate around 75% - 80% of the total available memory of server machine to innodb_buffer_pool_size.

How to set innodb_buffer_pool_size value?

This variable can be configured online from MySQL 5.7 using the following command:

SET GLOBAL innodb_buffer_pool_size = (value in bytes);

You should be very cautious to set the value for this variable at online. Because, when you restart the server, you will lose this configuration value. It is recommended to keep track of this changes and when you restart the server, you must set this value on the configuration file my.cnf.

Before MySQL 5.7 you have to set this configuration offline. You can set this value by editing my.cnf configuration file. Open my.cnf file and under the [mysqld] section, edit the following line.

innodb_buffer_pool_size = X G;

Now restart the MySQL server, and check the new configuration value by running 

SHOW VARIABLES LIKE '%innodb_buffer_pool_size%';

Example:

Online method:

SET GLOBAL innodb_buffer_pool_size = 26843545600;

Offline method:

innodb_buffer_pool = 26G 

Note: Server restart required here.

Is innodb_buffer_pool_size is the reason for slow performance?

There is no doubt that innodb_buffer_pool_size is one of the most important variables for MySQL server. In the right scenario having a larger innodb_buffer_pool_size, will increase your database server performance dramatically. But is this always true?. No. We should have enough buffer pool, but it is not the only cause for slow performance. You can verify this by analyzing the performance of the InnoDB buffer pool.

The InnoDB buffer pool performance can be calculated by using the following formula:

Performance = innodb_buffer_pool_reads / innodb_buffer_pool_read_requests * 100 

innodb_buffer_pool_reads: It indicates the number of requests that cannot be satisfied with InnoDB buffer pool. Need to read from the disk.

innodb_buffer_pool_read_requests: It indicates the number of requests of logical reads from memory.

For example on my server, let me check the InnoDB buffer pool performance:

innodb_buffer_pool_reads                = 91661

innodb_buffer_pool_read_requests   = 4029033624

Performance = 91661 / 4029033624 * 100

InnoDB Performance = 0.0022750120389663. This means the InnoDB can satisfy most of the requests from Buffer pool itself. Very minimal percentage of reads are done from the disk. So there is no need to increase the innodb_buffer_pool_size value.

So the logic is if your InnoDB buffer pool is able to satisfy all your query requests, then you have enough innodb_buffer_pool_size. If it reads from disk (the working data set is not available in buffer pool memory) then you should increase the innodb_buffer_pool_size.

When the performance of the InnoDB buffer pool maximum, it should not be the reason for the slow performance of the database server, in this case, you can check for other configurations.

When to decrease the innodb_buffer_pool_size?

Having larger innodb_buffer_pool_size is not always mandatory, but still, it depends on the database size and working data set.If it is a dedicated MySQL server machine, having excess memory will not be a problem, but when you are using shared server machine, having free memory will be useful for other applications and operating system.

You can use this command to check the memory status: 

SHOW ENGINE INNODB STATUS;

The sample output will be like as follows:

----------------------

BUFFER POOL AND MEMORY

----------------------

Total large memory allocated 26386366464

Dictionary memory allocated 23826297

Buffer pool size   1572672

Free buffers       8192

Database pages     1553364

Old database pages 573246

Modified db pages  36

Pending reads      0

Pending writes: LRU 0, flush list 0, single page 0

Pages made young 881819, not young 18198964

0.02 youngs/s, 0.05 non-youngs/s

Pages read 681064, created 2749237, written 3988300

0.02 reads/s, 0.12 creates/s, 11.50 writes/s

Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

LRU len: 1553364, unzip_LRU len: 0

I/O sum[5152]:cur[0], unzip sum[0]:cur[0]

Free buffers:

Having higher value for Free buffers is an indicator of you are having free InnoDB buffer pool. But make sure that, you are checking this value many times before coming to the conclusion. If you are seeing this higher value for a longer period of time, you can consider to decreasing the InnoDB buffer pool size.

InnoDB buffer pool hit ratio:

Buffer pool hit ratio = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_reads ) * 100

Innodb_buffer_pool_hit_ration below - 99.9% indicates that - innodb_buffer_pool_could be increased.

What are the InnoDB buffer pool status variables?

You can view the InnoDB buffer pool status variables by running this command:

show global status like '%innodb_buffer_pool_pages%';

The sample output will be:

mysql> show global status like '%innodb_buffer_pool_pages%';

+----------------------------------+---------+

| Variable_name                    | Value   |

+----------------------------------+---------+

| Innodb_buffer_pool_pages_data    | 1553363 |

| Innodb_buffer_pool_pages_dirty   | 14      |

| Innodb_buffer_pool_pages_flushed | 3994981 |

| Innodb_buffer_pool_pages_free    | 8193    |

| Innodb_buffer_pool_pages_misc    | 11116   |

| Innodb_buffer_pool_pages_total   | 1572672 |

+----------------------------------+---------+

6 rows in set (0.03 sec)

Explanation:

innodb_buffer_pool_pages_data shows the number of dirty and clean data and index pages.

innodb_buffer_pool_pages_misc shows the number of pages that are busy because they have been allocated for administrative overhead such as row locks or the adaptive hash index.

innodb_buffer_pool_pages_free shows the free pages in InnoDB buffer pool - a large number of free pages over a longer period is a strong indicator that InnoDB buffer pool is too big and can easily be decreased.

innodb_buffer_pool_pages_dirty shows the number of InnoDB buffer pool data pages that are modified in memory but not yet written to the data files (dirty page flushing).

innodb_buffer_pool_pages_flushed: indicates the number of requests to flush the dirty pages from the InnoDB buffer pool.

innodb_buffer_pool_reads: it indicates the number of requests that cannot be satisfied with InnoDB buffer pool. Need to read from the disk.

innodb_buffer_pool_read_requests: it indicates the number of requests of logical reads from memory.

innodb_buffer_pool_wait_free: it indicates the number of times, queries has to wait for dirty pages to be flushed to disk. It is a counter, it counts how many times this flush has happened. If innodb_buffer_pool_wait_free > 0 is a strong indicator that the InnoDB buffer pool is too small.

innodb_buffer_pool_write_request - indicates the number of writes done to the buffer pool.The ratio of write requests to pages flushed indicates the number of rows changed in a block before it flushed to disk

Rows changes per flush = innodb_buffer_pool_write_request / innodb_buffer_pool_pages_flushed

Sample output:

mysql> show global status like '%innodb_buffer_pool_pages_data%';

+-------------------------------+---------+

| Variable_name                 | Value   |

+-------------------------------+---------+

| Innodb_buffer_pool_pages_data | 1527965 |

+-------------------------------+---------+

1 row in set (0.59 sec)

mysql> show global status like '%innodb_buffer_pool_pages_misc%';

+-------------------------------+-------+

| Variable_name                 | Value |

+-------------------------------+-------+

| Innodb_buffer_pool_pages_misc | 44863 |

+-------------------------------+-------+

1 row in set (0.55 sec)

mysql> show global status like '%innodb_buffer_pool_pages_free%';

+-------------------------------+-------+

| Variable_name                 | Value |

+-------------------------------+-------+

| Innodb_buffer_pool_pages_free | 92    |

+-------------------------------+-------+

1 row in set (0.58 sec)

mysql> show global status like '%innodb_buffer_pool_pages_dirty%';

+--------------------------------+-------+

| Variable_name                  | Value |

+--------------------------------+-------+

| Innodb_buffer_pool_pages_dirty | 757   |

+--------------------------------+-------+

1 row in set (0.49 sec)

mysql> show global status like '%innodb_buffer_pool_pages_flushed%';

+----------------------------------+--------+

| Variable_name                    | Value  |

+----------------------------------+--------+

| Innodb_buffer_pool_pages_flushed | 839273 |

+----------------------------------+--------+

1 row in set (0.59 sec)

mysql> show global status like '%innodb_buffer_pool_reads%';

+--------------------------+---------+

| Variable_name            | Value   |

+--------------------------+---------+

| Innodb_buffer_pool_reads | 1684867 |

+--------------------------+---------+

1 row in set (0.58 sec)

mysql> show global status like '%innodb_buffer_pool_read_requests%';

+----------------------------------+-------------+

| Variable_name                    | Value       |

+----------------------------------+-------------+

| Innodb_buffer_pool_read_requests | 26439817181 |

+----------------------------------+-------------+

1 row in set (0.61 sec)

mysql> show global status like '%innodb_buffer_pool_wait_free%';

+------------------------------+-------+

| Variable_name                | Value |

+------------------------------+-------+

| Innodb_buffer_pool_wait_free | 0     |

+------------------------------+-------+

1 row in set (0.51 sec)

mysql> show global status like '%innodb_buffer_pool_write_request%';

+-----------------------------------+----------+

| Variable_name                     | Value    |

+-----------------------------------+----------+

| Innodb_buffer_pool_write_requests | 49063623 |

+-----------------------------------+----------+

1 row in set (0.52 sec)

Some useful queries:

How much memory should allocate to InnoDB buffer pool?

Recommended InnoDB buffer pool size based on the all InnoDB data and indices with additional 50% memory:

set @idbdataindx = (select sum(data_length+index_length) from information_schema.tables where engine = 'innodb');

set @ibpsG = @idbdataindx * 1.5 / (1024*1024*1024);

select @ibpsG;

Sample output:

mysql> set @idbdataindx = (select sum(data_length+index_length) from information_schema.tables where engine = 'innodb');

Query OK, 0 rows affected, 1 warning (42.17 sec)

mysql> select @idbdataindx;

+--------------+

| @idbdataindx |

+--------------+

| 251380645888 |

+--------------+

1 row in set (0.50 sec)

mysql> set @ibpsG = @idbdataindx * 1.5 / (1024*1024*1024);

Query OK, 0 rows affected (0.50 sec)

mysql> select @ibpsG;

+---------------+

| @ibpsG        |

+---------------+

| 351.174705505 |

+---------------+

1 row in set (0.61 sec)

How much actual GB of memory is in use by InnoDB buffer pool at this moment?

By multiplying data available in the buffer pool and InnoDB page (InnoDB buffer pool unit) size we can find the actual memory is in use by InnoDB buffer pool at this moment.

set @ibpdata = (select variable_value from information_schema.global_status where variable_name = 'innodb_buffer_pool_pages_data');

set @idbpgsize = (select variable_value from information_schema.global_status where variable_name = 'innodb_page_size');

set @ibpsize = @ibpdata * @idbpgsize / (1024*1024*1024);

select @ibpsize;

Sample output:

mysql> set @ibpdata = (select variable_value from information_schema.global_status where variable_name = 'innodb_buffer_pool_pages_data');

Query OK, 0 rows affected (0.50 sec)

mysql> set @idbpgsize = (select variable_value from information_schema.global_status where variable_name = 'innodb_page_size');

Query OK, 0 rows affected (0.51 sec)

mysql> set @ibpsize = @ibpdata * @idbpgsize / (1024*1024*1024);

Query OK, 0 rows affected (0.51 sec)

mysql> select @ibpsize;

+--------------------+

| @ibpsize           |

+--------------------+

| 23.310394287109375 |

+--------------------+

1 row in set (0.50 sec)

Note: In MySQL 5.7 the global status table moved to the performance_schema database. By default, it is disabled on the information_schema database.

Finally, there is no single solution for everyone, you have to understand your environment and decide wisely. Searching on the internet will give you some insights but it may not directly suitable to your environment. One thing I can say is, you have start somewhere, so learn it and master the task. I hope this post will give you some idea about InnoDB buffer pool. If you feel, I missed something here / any doubt or clarification on this post, please mention on the comment section.