What I learned with time is the more I read about the Innodb engine and its code, the more I feel that I need to learn more.

Understanding the buffer pool in detail helps the DBA/Architect to understand how data is flowing from the disk to the buffer and their age in the buffer pool.

Understanding it at a very high level, flow works like Pages are read from data files i.e. ibd file, if they are not in cache or buffer, and then copied into the midpoint of the buffer pool. A buffer pool is a collection of pages ordered in the list structure. A midpoint is a place where the old and new sub-list meets i.e. the head of the old sublist.

This old sublist can be maintained from the innodb_old_blocks_pct and also for how long a data or block can stay in the old sublist ( don’t forget old sublist is part of a buffer pool that maintains the least recently used data). The duration for which data remain in the old sublist can be controlled from the old block time.

These pages can be from the user’s query or random or linear. Now if a particular page is coming from the user query it will move to the head of the new sub list and if the random read  is enabled(pages that are coming from the same extent) then will remain in the old sublist and can be evicted based on the eviction policy

The eviction policy of LRU old list is below :

Suppose out of a 10 GB buffer pool, we have given 3 GB to the old sublist(innodb_old_blocks_pct ). This means that the data which came as a result of random or linear or full table scanning will come first in the old sublist and reside there.  For example on performing a table scan, the data will come in the head of the old sublist first and if the same page is accessed again then after innodb_old_blocks_time it will move to the new sublist. It Is important to understand that the new sublist should not be filled with unwanted data otherwise the most accessed page will have to be evicted because of less memory and the LRU algorithm will become expensive

Few things to check about the old and new sub-list which can help us to decide if it needs further tuning. Take a look below which is a snapshot from the engine InnoDB status :

———————-

BUFFER POOL AND MEMORY

———————-

Total large memory allocated 137363456

Dictionary memory allocated 1077229

Buffer pool size   8191

Free buffers       1024

Database pages     7155

Old database pages 2621

Modified db pages  0

Pending reads      0

Pending writes: LRU 0, flush list 0, single page 0

Pages made young 1, not young 54162256.  <- – – – – – – – – – number of pages moved from old to new and number of pages in old sublist i.e. not being promoted to new list

0.00 youngs/s, 0.00 non-youngs/s.  <- – – – – – – – – – the number of access to make or to move a page from old to new and non-young means didn’t move to the new list. This shows whether pages are fequently getting move from old to new or is not moving at all . Depending on this value we can tweak innodb_old_block_pct or innodb_old_block_time

Pages read 129946, created 324, written 890

0.00 reads/s, 0.00 creates/s, 0.00 writes/s

No buffer pool page gets since the last printout

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

LRU len: 7155, unzip_LRU len: 0

I/O sum[0]:cur[0], unzip sum[0]:cur[0]

In the next coming days, I am going to append more information on the insights of the buffer pool and which tables can help us to understand the status of all pages in the buffer pool.

Additionally, I will be appending more about the read-ahead process. Keep MySQLing.

Ankit
Linkedin