Since MySQL 5.7.5, we have been able to resize dynamically the InnoDB Buffer Pool. This new feature also introduced a new variable — innodb_buffer_pool_chunk_size — which defines the chunk size by which the buffer pool is enlarged or reduced. This variable is not dynamic and if it is incorrectly configured, could lead to undesired situations.
Let’s see first how innodb_buffer_pool_size , innodb_buffer_pool_instances and innodb_buffer_pool_chunk_size interact:
The buffer pool can hold several instances and each instance is divided into chunks. There is some information that we need to take into account: the number of instances can go from 1 to 64 and the total amount of chunks should not exceed 1000.
So, for a server with 3GB RAM, a buffer pool of 2GB with 8 instances and chunks at default value (128MB) we are going to get 2 chunks per instance:
This means that there will be 16 chunks.
I’m not going to explain the benefits of having multiple instances, I will focus on resizing operations. Why would you want to resize the buffer pool? Well, there are several reasons, such as:
- on a virtual server you can add more memory dynamically
- for a physical server, you might want to reduce database memory usage to make way for other processes
- on systems where the database size is smaller than available RAM
- if you expect a huge growth and want to increase the buffer pool on demand
Reducing the buffer pool
Let’s start reducing the buffer pool:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | | innodb_buffer_pool_size | 2147483648 | | innodb_buffer_pool_instances | 8 | | innodb_buffer_pool_chunk_size | 134217728 | mysql> set global innodb_buffer_pool_size=1073741824; Query OK, 0 rows affected (0.00 sec) mysql> show global variables like 'innodb_buffer_pool_size'; +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | innodb_buffer_pool_size | 1073741824 | +-------------------------+------------+ 1 row in set (0.00 sec) |
If we try to decrease it to 1.5GB, the buffer pool will not change and a warning will be showed:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | mysql> set global innodb_buffer_pool_size=1610612736; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+---------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------------------------+ | Warning | 1210 | InnoDB: Cannot resize buffer pool to lesser than chunk size of 134217728 bytes. | +---------+------+---------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show global variables like 'innodb_buffer_pool_size'; +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | innodb_buffer_pool_size | 2147483648 | +-------------------------+------------+ 1 row in set (0.01 sec) |
Increasing the buffer pool
When we try to increase the value from 1GB to 1.5GB, the buffer pool is resized but the requested innodb_buffer_pool_size is considered to be incorrect and is truncated:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | mysql> set global innodb_buffer_pool_size=1610612736; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+-----------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------+ | Warning | 1292 | Truncated incorrect innodb_buffer_pool_size value: '1610612736' | +---------+------+-----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show global variables like 'innodb_buffer_pool_size'; +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | innodb_buffer_pool_size | 2147483648 | +-------------------------+------------+ 1 row in set (0.01 sec) |
And the final size is 2GB. Yes! you intended to set the value to 1.5GB and you succeeded in setting it to 2GB. Even if you set 1 byte higher, like setting: 1073741825, you will end up with a buffer pool of 2GB.
1 2 3 4 5 6 7 8 9 10 11 | mysql> set global innodb_buffer_pool_size=1073741825; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show global variables like 'innodb_buffer_pool_%size' ; +-------------------------------+------------+ | Variable_name | Value | +-------------------------------+------------+ | innodb_buffer_pool_chunk_size | 134217728 | | innodb_buffer_pool_size | 2147483648 | +-------------------------------+------------+ 2 rows in set (0.01 sec) |
Interesting scenarios
Increasing size in the config file
Let’s suppose one day you get up willing to change or tune some variables in your server, and you decide that as you have free memory you will increase the buffer pool. In this example, we are going to use a server with innodb_buffer_pool_instances = 16 and 2GB of buffer pool size which will be increased to 2.5GB
So, we set in the configuration file:
1 | innodb_buffer_pool_size = 2684354560 |
But then after a restart, we found:
1 2 3 4 5 6 7 8 | mysql> show global variables like 'innodb_buffer_pool_%size' ; +-------------------------------+------------+ | Variable_name | Value | +-------------------------------+------------+ | innodb_buffer_pool_chunk_size | 134217728 | | innodb_buffer_pool_size | 4294967296 | +-------------------------------+------------+ 2 rows in set (0.00 sec) |
And the error log says:
1 | 2018-05-02T21:52:43.568054Z 0 [Note] InnoDB: Initializing buffer pool, total size = 4G, instances = 16, chunk size = 128M |
So, after we have set innodb_buffer_pool_size in the config file to 2.5GB, the database gives us a 4GB buffer pool, because of the number of instances and the chunk size. What the message doesn’t tell us is the number of chunks, and this would be useful to understand why such a huge difference.
Let’s take a look at how that’s calculated.
Increasing instances and chunk size
Changing the number of instances or the chunk size will require a restart and will take into consideration the buffer pool size as an upper limit to set the chunk size. For instance, with this configuration:
1 2 3 | innodb_buffer_pool_size = 2147483648 innodb_buffer_pool_instances = 32 innodb_buffer_pool_chunk_size = 134217728 |
We get this chunk size:
1 2 3 4 5 6 7 8 | mysql> show global variables like 'innodb_buffer_pool_%size' ; +-------------------------------+------------+ | Variable_name | Value | +-------------------------------+------------+ | innodb_buffer_pool_chunk_size | 67108864 | | innodb_buffer_pool_size | 2147483648 | +-------------------------------+------------+ 2 rows in set (0.00 sec) |
However, we need to understand how this is really working. To get the innodb_buffer_pool_chunk_size it will make this calculation: innodb_buffer_pool_size / innodb_buffer_pool_instances with the result rounded to a multiple of 1MB.
In our example, the calculation will be 2147483648 / 32 = 67108864 which 67108864%1048576=0, no rounding needed. The number of chunks will be one chunk per instance.
When does it consider that it needs to use more chunks per instance? When the difference between the required size and the innodb_buffer_pool_size configured in the file is greater or equal to 1MB.
That is why, for instance, if you try to set the innodb_buffer_pool_size equal to 1GB + 1MB – 1B you will get 1GB of buffer pool:
1 2 3 4 | innodb_buffer_pool_size = 1074790399 innodb_buffer_pool_instances = 16 innodb_buffer_pool_chunk_size = 67141632 2018-05-07T09:26:43.328313Z 0 [Note] InnoDB: Initializing buffer pool, total size = 1G, instances = 16, chunk size = 64M |
But if you set the innodb_buffer_pool_size equals to 1GB + 1MB you will get 2GB of buffer pool:
1 2 3 4 | innodb_buffer_pool_size = 1074790400 innodb_buffer_pool_instances = 16 innodb_buffer_pool_chunk_size = 67141632 2018-05-07T09:25:48.204032Z 0 [Note] InnoDB: Initializing buffer pool, total size = 2G, instances = 16, chunk size = 64M |
This is because it considers that two chunks will fit. We can say that this is how the InnoDB Buffer pool size is calculated:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | determine_best_chunk_size{ if innodb_buffer_pool_size / innodb_buffer_pool_instances < innodb_buffer_pool_chunk_size then innodb_buffer_pool_chunk_size = roundDownMB(innodb_buffer_pool_size / innodb_buffer_pool_instances) fi } determine_amount_of_chunks{ innodb_buffer_amount_chunks_per_instance = roundDown(innodb_buffer_pool_size / innodb_buffer_pool_instances / innodb_buffer_pool_chunk_size) if innodb_buffer_amount_chunks_per_instance * innodb_buffer_pool_instances * innodb_buffer_pool_chunk_size - innodb_buffer_pool_size > 1024*1024 then innodb_buffer_amount_chunks_per_instance++ fi } determine_best_chunk_size determine_amount_of_chunks innodb_buffer_pool_size = innodb_buffer_pool_instances * innodb_buffer_pool_chunk_size * innodb_buffer_amount_chunks_per_instance |
What is the best setting?
In order to analyze the best setting, you will need to know that there is an upper limit of 1000 chunks. In our example with 16 instances, we can have no more than 62 chunks per instance.
Another thing to consider is what each chunk represents in percentage terms. Continuing with the example, each chunk per instance represent 1.61%, which means that we can increase or decrease the complete buffer pool size in multiples of this percentage.
From a management point of view, I think that you might want to consider at least a range of 2% to 5% to increase or decrease the buffer. I performed some tests to see the impact of having small chunks and I found no issues but this is something that needs to be thoroughly tested.
You May Also Like
InnoDB is known for balancing high reliability with high performance. The InnoDB primary keys you choose can have a critical impact on your database’s performance. For guidance on choosing the best primary keys, read Principal Architect Yves Trudeau’s blog: Tuning InnoDB Primary Keys.
Watch our MySQL Troubleshooting webinar to better understand the impact monitoring tools can have on your database’s performance and how you can minimize their impact. The webinar, hosted by our Principal Support Engineer Sveta Smirnova, will teach you how to monitor your database safely and effectively.
I have ibm thinkpad notebook T400 with 8GB RAM and 40GB free on harddrive. I am trying to load MIMIC III MIT health db cvs files of which there are 26. 24 I can load with success. One of the csv files is for table chartevents/when I try to load this csv file to mysql 5.7, it runs for about an hour then says ‘table full’ error. I have tried to set innodb_buffer_pool_size from current value of 1342177280 (I found current value by running ‘show variables like ‘%innodb_buffer_pool_size%’) to 6GB which in online mode is 6442450944. However when I run set global innodb_buffer_pool_size = 6442450944, I get 1292 truncated incorrect innodb_butter_pool_size value: ‘6442450944’. And when I rerun the show variables like above, I see the pool size has not changed and is still 1342177280. If in my.ini i put the innodb_buffer_pool_size = 6G and at c prompt run mysqld, it returns to command prompt (meaning the dameon won’t run. Only if I comment out the innodb_buffer_pool_size = 6G will at the c prompt running mysqld stay and not return to the c prompt (the behavior of running mysqld and it returning to c prompt means mysqld would not start.) Since I need to load this csv file into mysql (30MM row csv) and I get table full error, my thought is getting the pool size up to 6GB of RAM would possibly solve the table full error; but I can’t seem to change the pool size to get to 6GB and the 1242177280 byte current setting is not sufficient. Thoughts?
This post starts out so promising, and then just leaves me with more questions then answers.
You demonstrate how I’m unable to resize my pool_size to 1.5G. Then you explain a bunch of internal logic, and after trying to wrap my head around this for 20 minutes, I’m still UNCLEAR whether or not its possible for me to set my pool_size to 1.5G, and if so; how.
A simple example of how to actually set the pool_size to my desired value would complete these article.