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
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 front 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 Plugin 1.0.5, this mechanism has been extended in two ways.
You can control the insertion point in the LRU list. A new
innodb_old_blocks_pct now controls the
percentage of “old” blocks in the LRU list. The
default value of
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 Plugin 1.0.5 introduces a new
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
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_time are dynamic, global and can be specified
in the MySQL option file (
my.ini) or changed at runtime with the
SET GLOBAL command. Changing the setting
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:cur, unzip sum:cur
Old database pagesis the number of pages in the “old” segment of the LRU list.
Pages made youngand
not youngis the total number of “old” pages that have been made young or not respectively.
non-young/sis the rate at which page accesses to the “old” pages have resulted in making such pages young or otherwise respectively since the last invocation of the command.
notprovides the same rate but in terms of overall buffer cache accesses instead of accesses just to the “old” pages.
Per second averages provided in
Monitor output are based on the elapsed time between the current
time and the last time
InnoDB Monitor output