Historically, InnoDB has inserted newly read blocks into the middle of the list representing the buffer cache, to avoid pollution of the cache due to excessive read-ahead. The idea is that the read-ahead algorithm should not pollute the buffer cache by forcing the frequently accessed (“hot”) pages out of the LRU list. To achieve this, InnoDB internally maintains a pointer at 3/8 from the tail of the LRU list, and all newly read pages are inserted at this location in the LRU list. The pages are moved to the from of the list (the most-recently used end) when they are accessed from the buffer cache for the first time. Thus pages that are never accessed never make it to the front 5/8 of the LRU list.

The above arrangement logically divides the LRU list into two segments where the 3/8 pages downstream of the insertion point are considered “old” and are desirable victims for LRU eviction. Starting with InnoDB storage engine 1.0.5, this mechanism has been extended in two ways.

You can control the insertion point in the LRU list. A new configuration parameter innodb_old_blocks_pct now controls the percentage of “old” blocks in the LRU list. The default value of innodb_old_blocks_pct is 37, corresponding to the original fixed ratio of 3/8. The permissible value range is 5 to 95.

The optimization that keeps the buffer cache from being churned too much by read-ahead, is extended to avoid similar problems resulting from table or index scans. During an index scan, a data page is typically accessed a few times in quick succession and is then never touched again. InnoDB storage engine 1.0.5 introduces a new configuration parameter innodb_old_blocks_time which specifies the time window (in milliseconds) after the first access to a page during which it can be accessed without being moved to the front (most-recently used end) of the LRU list. The default value of innodb_old_blocks_time is 0, corresponding to the original behavior of moving a page to the MRU end of the LRU list on first access in the buffer pool.

Both the new parameters innodb_old_blocks_pct and innodb_old_blocks_time are dynamic, global and can be specified in the MySQL option file (my.cnf or my.ini) or changed at runtime with the SET GLOBAL command. Changing the setting requires the SUPER privilege.

To help you gauge the effect of setting these parameters, some additional statistics are reported by SHOW ENGINE INNODB STATUS command. The BUFFER POOL AND MEMORY section now looks like:

Total memory allocated 1107296256; in additional pool allocated 0
Dictionary memory allocated 80360
Buffer pool size   65535
Free buffers       0
Database pages     63920
Old database pages 23600
Modified db pages  34969
Pending reads 32
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 414946, not young 2930673
1274.75 youngs/s, 16521.90 non-youngs/s
Pages read 486005, created 3178, written 160585
2132.37 reads/s, 3.40 creates/s, 323.74 writes/s
Buffer pool hit rate 950 / 1000, young-making rate 30 / 1000 not 392 / 1000
Pages read ahead 1510.10/s, evicted without access 0.00/s
LRU len: 63920, unzip_LRU len: 0
I/O sum[43690]:cur[221], unzip sum[0]:cur[0]