Study on Buffer Pool pollution caused by mysqldump

Preface:

Recently, Oracle MySQL posted some of the 5.6 posts on its official BlogModify the default value of a Variable. WhereInnodb_old_blocks_timeFrom 0 to 1000 (1 s)

The role of this parameter is as follows:

How long in milliseconds (MS) a block inserted into the oldSublistMust stay there after its first access before it can be moved to the new sublist.Increasing this value protects against the buffer pool being filled up by data that is referenced only for a brief period, such as duringFull table scan.

In fact, the function is to reduce the pollution of BufferPool (hereinafter referred to as BP) for a single batch of Data Queries (similar to the behavior of mysqldump.

Speaking of this, we have to mention the BP's midpoint insert mechanism.

This mechanism will be analyzed and discussed below.

I. insert mechanism of Buffer Pool

BP can be considered as a long-chain table. Divided into two parts: young and old, where old accounts for 37% of the size by default (fromInnodb_old_blocks_pctConfiguration). The Page near the top indicates that the Page was recently asked. The Page near the end indicates that it has not been accessed for a long time. The intersection of the two parts becomes the midpoint. Whenever a new Page needs to be loaded to BP, the page will be inserted to the midpoint location and declared as old-page. When the old page is accessed, It is promoted to the top of the linked list and marked as young.

Because the operation of table scan is to load page first, and then immediately trigger an access. So whenInnodb_old_blocks_timeIf it is set to 0, the pages required by table scan are not read as young pages and added to the top of the linked list. Some pages that are not frequently used will be squeezed out by BP, resulting in disk IO generated by subsequent SQL statements, resulting in slow response. This is the BP pollution mentioned in the title.

 

2. Modify innodb_old_blocks_time

Percona has done this before.Related testsThe conclusion is that when time = 0, the normal access throughput is reduced to 10%; when time = 1000, the throughput is consistent with the performance when no backup is available.

If so, let's test it in person.

The test result is as follows:

Concurrency indicates the value of -- num-threads in sysbench.

OPT indicates that there is no sysbench QPS for mysqldump in this environment.

The remaining two columns represent the sysbench QPS when mysqldump exists.

Concurrency	OPT	Old_time = 0	Old_time= 1000
1	17394	1836	2141
2	29703	3670	3981
3	47347	5683	6540
4	64717	6805	8337
5	83551	8676	15885
6	99396	12978	19893
7	112330	16491	26022
8	126600	23840	33346
9	138468	30760	39194
10	150365	39034	48925
11	163053	43174	60352
12	174916	52066	70180
13	174160	63853	78076
14	173786	65164	80661
15	174268	70965	90633
16	175044	80871	102629
17	175583	90689	103423
18	175939	94805	112629
19	175114	93303	120625

The results show that time = 1000 does not greatly improve the query performance. In the best case, the performance is only improved by 80% compared with the time = 0.

Why?

In fact, it is not hard to understand that the concurrency in the table determines the hot and cold degrees of the test page. The larger the concurrency, the more concurrent requests are generated on each side. Therefore, the more frequently each page is accessed, and the higher the position of the page in the LRU linked list. And vice versa.

Let's look at the situation of high-frequency hotspot data access. At this time, although the pages accessed by mysqldump will be constantly loaded at the top of the LRU, the high-frequency hotspot data access will seize the page to the top of the LRU at a faster speed. As a result, the page loaded by mysqldump will be quickly flushed and immediately evict (obsolete ). Therefore, time = 0 or 1000 does not have a great impact on access in such a stressful environment, because the data of dump cannot compete for hotspot data.

Similarly, ultra-low frequency data access is the same. Because of the low frequency of data access, a large number of pages are at the end of the LRU linked list. Therefore, no matter whether the dump page is loaded to the head or midpoint location, it will be in front of the hotspot data. That is to say, the data page will be eliminated in any case. Therefore, the performance in this stress environment will not fluctuate greatly with the time Value configuration changes.

What truly benefits time is the tepid data on the midpoint edge.

It can also be seen that the biggest performance improvement is in the case of medium traffic, that is, the location of 37%.

Iii. Impact of the Mid Point position

From the previous analysis, we can draw the following conclusion:Innodb_old_blocks_time The scope of the function is directly related to the hot and cold pages. Innodb_old_blocks_pct determines the data distribution of BP.

Therefore, the innodb_old_blocks_pct adjustment can influence innodb_old_blocks_time.

The curve also proves this point of view. When innodb_old_blocks_pct is adjusted to 60%, the peak is also shifted to 60%.

Summary:
1. innodb_old_blocks_time = 1000 can reduce the impact of mysqldump access on database performance to a certain extent.
2. The optimization effect of innodb_old_blocks_time = 1000 is limited, which can greatly improve the page located near the midpoint.