Background

From Version MySQL 5.7.5, you can dynamically adjust "innodb_buffer_pool_size" online, and also this new feature introduces the parameter "innodb_buffer_pool_chunk_size". Since "buffer pool" size is subject to the influence of two parameters (namely, "innodb_buffer_pool_chunk_size" and "innodb_buffer_pool_instances"), the actual "innodb_buffer_pool_size" might differ from DBA settings, and it is even larger sometimes.

The following describes this new feature from two respects:

1. How to make a dynamic adjustment on line? What influences does online adjustment exert on service? What scenarios is it applicable to?
2. How do "innodb_buffer_pool_chunk_size" and "innodb_buffer_pool_instances" influence buffer pool?

Failure Phenomenon

When conducting performance pressure measurement to MySQL 5.7.21 database, we select a virtual machine with 2G memory and set "innodb_buffer_pool_size" according to 60% of the memory (2G* 60%=1228MB). Soon after the pressure measurement begins, the database is OOM (out of memory).

From troubleshooting we find that buffer pool size set in "my.cnf" is totally different from the size searched from memory:

• my.cnf value: innodb_buffer_pool_size = 1228MB
• Select value of "@@ innodb_buffer_pool_size;": 2147483648 (2048MB)

We set the size according to 60% of the total memory (namely, 1228MB), but the actual "buffer pool" in the memory has occupied all memory of the entire virtual machine. To our surprise, "my.cnf" static file fails to control the size of "buffer pool". Such a problem perplexes DBA.

Analysis of cause

Explanation of Terms

• innodb_buffer_pool_size: this parameter controls the size of "innodb" buffer pool, and is used for saving innodb table and indexed data. "buffer pool" is the short name for the following:
• innodb_buffer_pool_instance: this parameter controls the number of zones that are divided for the innodb buffer pool. If "innodb_buffer_pool_size"<1G, the instance is 1, or is 8 by default. This parameter is down to 1 and up to 64. "instance" is the short name for the following:
• innodb_buffer_pool_chunk_size: this parameter controls "innodb buffer pool" size adjustment, for adjusting the chunk size of the action. This parameter is 128MB by default, down to 1MB (its size is adjustable by 1MB), and up to "innodb_buffer_pool_size / innodb_buffer_pool_instances". Chunk is the short name for the following:

Further analysis

1. First, let's see the relationship among the three parameters like "innodb_buffer_pool_size", "innodb_buffer_pool_instances", and "innodb_buffer_pool_chunk_size". The buffer pool may be used for saving multiple instances, and each instance consists of multiple chunks. Range of instance number and range of total chunks are respectively 1-64 and 1-1000. For example, the chunk on one server with 4G memory is 128MB. Provided that buffer pool is set as 2G, and the number of instances is set as 4, each instance is 512MB (namely, 4 chunks). Display the following figure:

2. Then, let's see how do "innodb_buffer_pool_instances" and "innodb_buffer_pool_chunk_size" influence "innodb_buffer_pool_size".

   • When initializing buffer pool, if "innodb_buffer_pool_size" is smaller than "innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances", "innodb_buffer_pool_chunk_size" is truncated to "innodb_buffer_pool_size / innodb_buffer_pool_instances".

   For example, see the following figurre for the initial status:

   

   Set "innodb_buffer_pool_chunk_size=1073741824" in "my.cnf", and restart the instance:

   

   In the aforesaid example, "innodb_buffer_pool_chunk_size" is adjusted from 134217728 by default to "innodb_buffer_pool_size/innodb_buffer_pool_instances= 2147483648/8=268435456"

   • "buffer pool" size must be always equal to integer multiple of "innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances" . In modification of any parameter, MySQL automatically adjusts "innodb_buffer_pool_size" into integer multiple of "innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances".

     Since "innodb_buffer_pool_chunk_size" or "innodb_buffer_pool_instances" influences the "innodb_buffer_pool_size", you must make modification with great care.

Solution

When setting "buffer pool", refer to the calculation formula: ceil (set buffer pool size /chunk size /number of instances)? chunk size ? number of instances. The calculated result for this value should meet your expectations.

Case review

Online dynamic modification to a buffer pool

1. Adjust the high buffer pool on the line. The process for adjusting the high buffer pool is as follows:

   (1) Allocate new memory pages while taking "innodb_buffer_pool_chunk_size" as the unit;
   (2) Expand buffer pool's AHI(adaptive hash index) linked list, and bring in the newly allocated pages;
   (3) Add newly allocated pages into "free list";

Test result:

2. Adjust the low buffer pool on line. The process for adjusting the low buffer pool is as follows:

(1) Reorganize buffer pool, and get ready to recover pages;
(2) Release these pages while taking "innodb_buffer_pool_chunk_size" as the unit (it might take some time);
(3) Adjust the AHI linked list, and use the new memory address.

Test result is as follows:

We can see that through online modification, the buffer pool has changed from 480MB to 256MB.

Influence factors to buffer pool after MySQL 5.7.5

From the official website we has already known that MySQL 5.7.5's buffer pool size must be the integer multiple of "innodb_buffer_pool_chunk_size* innodb_buffer_pool_instance". Then, how do the two parameters influence buffer pool settings?

Example: system memory, 4G, chunk size, 128MB, and the number of instances, 8:

We can see that buffer pool is set 1 byte higher than original "1073741824 (1G)" in online modification, but "innodb_buffer_pool_size" is automatically adjusted high to "2147483648 (2G)". Why does this happen (adjusted to 2G)？

Calculation method: ceil(set buffer pool size/chunk size/number of instances) ? chunk size ? number of instances= ceil(1073741825/134217728/8) ? 134217728 ? 8= 2147483648byte=2G

Conclusions & Suggestions

Dynamic adjustment is convenient and rapid, but the influence is not remarkable in the actual test.

In an actual test, upward adjustment to buffer pool exerts no online influence, and downward adjustment brings little online influence as well. Test Scenario I for adjusting down buffer pool:

(1) Session 1: A large transaction is running, and the predicted memory usage is 6G.
(2) Session 2: Set the buffer pool size to 1G. (3) We can see that SQL for setting buffer pool size is completed in a second, but the setting is not enabled until the session 1 transaction ends. (4) During the enabling process, the system does not show any lock information, and there is no influence on addition, deletion, change, and the query of other database tables.

Test Scenario I for adjusting down buffer pool:

(1) In the system there are 100 concurrent "Addition, Deletion, Modification, Query" actions in execution. (2) Session 2: Set buffer pool from 5G to 1G. (3) We can see that the SQL for setting buffer pool size is completed in a second, but the setting is not enabled immediately, and it takes effect 5 seconds later. (4) During the enabling of the buffer pool, the system does not display any lock information, and there is no influence on "Addition, Deletion, Modification, Query" of other database tables.

Set buffer pool with great care after 5.7.5

From MySQL 5.7.5, "innodb buffer pool" size is subject to the influence of chunk and instance, so you must calculate the set buffer pool beforehand, or the overly large buffer pool from automated adjustment by MySQL might result in the instance OOM (out of memory).

When setting "buffer pool", refer to the calculation formula: ceil (set buffer pool size /chunk size /number of instances)? chunk size ? number of instances. The calculated result for this value should meet your expectations.

For example, the memory of the current virtual machine of MySQL is 8G, chunk, 128MB, number of instances, 8.

• "innodb_buffer_pool" should be only set as the integer multiple of chunk size ? number of instances=1G, namely, as nG (n is an integer).
• What if I want to set it as n.5G? Solution: adjust "chunk size ? number of instances" as an integer multiple of 512MB. For example, after adjusting chunk to 64MB and the number of instances to 8, you may set a buffer pool as n.5G (n is an integer).