Three kinds of chains in MySQL buffer pool

Three page, three kinds of list, LRU control tuning

One, InnoDB buffer pool of three kinds of pages

1.Free page: Pages that have never been used

2,Clean page: Clear page, data page data and disk consistent

3.Dirtypage: Dirty pages

SQL Execution Requirements:

1. Find free Pages

2. Refresh Dirty Pages

1, this page is not hot data page (Brush cold page)

2, this page the earliest modification time (Brush modification time Earlier page, there may be hot page), Easy overwrite of log file

3. Clean page covered with cold

In order to achieve the above requirements, InnoDB used the link list technology (a function of each chain list, the existence meaning of the chain is to traverse).

InnoDB three kinds of chains in buffer pool

1. Free list: Use the free data page as a chain bracelet.

When the database was first started, the LRU list was empty, and when needed, the pages in the free list were deleted, and the corresponding pages were added to the LRU list to maintain the number of pages conserved.

2, LRU list: according to hot and cold will clean, dirty chain up

Least recent used (least recently used)

1. "Midpoint insertion Policy"

2, write back as far as possible back to write cold dirty block

3, covering as far as possible to cover the cold dirty blocks

lru Standard algorithm:

1) 3/ The list information for 8 is the old list, and this information is the object being evicted.

2) The midpoint of list is what we call OL The connection point of the D list head and the new list tail is equivalent to a boundary.

3) The reading of new data is first inserted into old The head of the list.

4) if it is old When the list's data is accessed, the page information becomes the new list and becomes the young page, and the data page information is moved to the head of new sublist.

5) in the buffer of the database Pool, whether new sublist or old sublist data will not be accessed, and eventually will be moved to the tail of the list as the victim.

3, flush list: The page according to the earliest dirty time chain up

The flush list is all dirty pages, and the brush disk refreshes the dirty pages in the Flush list back to disk.

1, the very old dirty block write back to disk, according to the new and old back to write data page;

2, because it is from the earliest dirty block start brush, so that the corresponding log in the logfile can be overwritten.

Q: Why do I need these three kinds of chains?
A:

Because in the InnoDB buffer pool, memory management is as follows:
1. You need to find free data block frequently:
2, the need to often find cold data blocks: LRU list (least recently used, according to the hot and cold chain up).
3, need to know which data block is relatively early dirty, flush list: We want to overwrite the old logfile, we need the system will these logfile corresponding dirty blocks, will be flush list of dirty pages on the chain to the disk to brush, (bulk to disk writing, rather than brush dirty pages).

Three, the LRU hot and cold zone control and adjustment

1, set hot and cold dividing line: innodb_old_blocks_pct

2, the time required to become a hot block: innodb_old_blocks_time

Mysql>Show variables like '%old_blocks%';+------------------------+-------+|Variable_name|Value|+------------------------+-------+|innodb_old_blocks_pct| Panax Notoginseng    ||Innodb_old_blocks_time|  +  |+------------------------+-------+2Rowsinch Set(0.01Sec

The innodb_old_blocks_pct parameter can be divided into two parts: one is to store data pages that have not been accessed for a long time, and the other is to store data pages that have been accessed recently. The data page near the head of the LRU list indicates that the data page is recently accessed, near the tail, which indicates that the pages are not accessed for a long time, and that the intersection of the two parts is called midpoint, which is the setting of the innodb_old_blocks_pct point. the default is 37%, the minimum is 5, the maximum is 95, if the memory is larger, you can lower the value, usually adjusted to 20, that is, 20% is a cold data block. The purpose is to protect the hot zone data from being brushed out of memory.

control the time required to become hot data through the Innodb_old_blocks_time parameter, The default is 1000ms, that is, 1s, that is, the data in 1s is not brushed away, the transfer into the hot zone.

3, the LRU hot and cold data monitoring

MySQL>0not00.00 Youngs/0.00 Non- Youngs/s

1. Data page from cold to hot, known as young;not Young is the amount of data that is brushed away without becoming a hot data (cumulative value).

2, NON-YOUNGS/S, if this value is very high, the general situation is that the system has a serious full table scan, which naturally means a high physical reading.

3, YOUNGS/S, if this value is relatively high, it is best to add a innodb_old_blocks_time, reduce innodb_old_blocks_pct, protect thermal data .

Three kinds of chains in MySQL buffer pool