Innodb improves efficiency by using buffer pools to reduce the number of disk queries. But memory is limited after all, it is impossible to store all the data in the database table in memory, so this time you need memory management algorithm to maintain memory space. What are the common memory management algorithms? LRU algorithm, the least recently used algorithm, which is to eliminate the least recently used pages in a period of time

Commonly used LRU algorithm principle

LRU is generally implemented using a linked list, which has Head and Tail nodes. When a node is accessed, the node is placed in the Head, as shown below. The thread queries page2 and sets page2 as the head node.

What if I read a new page? This will render the TAIL node obsolete

Does Inndo use this algorithm to maintain pages in memory? The answer is no, the traditional LRU algorithm is not suitable for Innodb’s memory management scenario.

Innodb LRU algorithm

There is a general rule in computers called the locality principle, which simply states that when a program accesses an element, it will access adjacent elements. For example, page2 has 1600 rows stored (row from 1 to 1600). B+ Tree itself requires that the data be stored sequentially, and according to the principle of locality, if a thread requests the 100th row of records, it will generally request the records between 100 and 200 rows soon.

After the principle of locality, let’s go back to describe why the traditional LRU algorithm can not meet Innodb memory management. Innodb uses read-ahead technology to store disk data in memory, that is, some data is stored in memory when the database is initialized. Currently, preread data cannot be identified as hotspot data, and the commonly used LRU algorithm cannot ensure that frequently accessed data is retained in memory for a long time. Innodb has upgraded the LRU algorithm.

Ps: The core reason is that the algorithm of the unupgraded LRU will put the preread data in the HEAD node

Innodb splits the list into two parts: new list and old List, new/old is usually at 7:3, new/old is usually at 7:3, When a preread page enters the buffer pool, it is not directly added to the head of the new list, but loaded into the head of the old list. In this way, the tail of the old list is also the first to be eliminated. This ensures that preread pages are not placed at the head of the linked list, thus preserving hot data. The pages hit by the query are added directly to the head of the new List, and the tail of the new List goes to the tail of the old list

The Mysql cache pool is polluted

Cache pool contamination is the use of full-table queries that add pages that are not actually used to the cache pool, causing hot data to be flushed out.

MySQL buffer pool added a “old generation dwell time window” mechanism:

  • Suppose T= residence time window of old generation;
  • [Fixed] Pages inserted into the head of an older generation will not be placed in the head of the younger generation, even if they are accessed immediately.
  • Only when “visited” and “stay time in old generation” is greater than T, will be put into the head of the new generation;

With the “old generation stay time window” policy, pages loaded in a short period of time will not be immediately inserted into the head of the new generation, but those pages visited only once in a short period of time will be preferentially eliminated.

Innodb memory management more important parameters

Parameters: the innodb_buffer_pool_size

Configure the buffer pool size. If memory allows, dbAs often recommend increasing this parameter. The more data and indexes are placed in memory, the better the performance of the database.

Parameters: innodb_old_blocks_pct

Introduction: The ratio of the old generation to the length of the whole LRU chain is 37 by default, that is, the ratio of the meso-Cenozoic to the old generation in the whole LRU chain is 63:37. Voiceover: If you set this parameter to 100, it degrades to normal LRU.

Parameter: innodb_old_blocks_time Description: Old generation dwell time window, the unit is milliseconds, the default value is 1000, that is, the old generation will be inserted into the head of the new generation only when both “accessed” and “old generation dwell time exceeds 1 second” conditions are met.

Innodb_old_blocks_pct = innodb_olD_blocks_time = innodb_old_blocks_time = innodb_old_blocks_time = innodb_old_blocks_time = innodb_old_blocks_time = innodb_old_blocks_time = innodb_old_blocks_time Try not to cache useful data in the new area immediately flushed. (This is also a palliative approach, a large number of full table scans to optimize SQL and table index structure)

2. If you are not doing a lot of full table scans in your business, you can increase innodb_old_blocks_pct, reduce the time of Innodb_old_blocks_time, and cache useful query data as much as possible in Innodb_buffer_pool_size. Reduce disk I/O and improve performance.

reference

Buffer pool, this time completely understand!!

One parameter per day — Innodb_old_blocks_pct and innodb_old_blocks_time understand