Rather than using a strict LRU
InnoDB uses a technique to
minimize the amount of data that is brought into the
buffer pool and never
accessed again. The goal is to make sure that frequently
accessed (“hot”) pages remain in the buffer pool,
even as read-ahead and
full table scans
bring in new blocks that might or might not be accessed
Newly read blocks are inserted into the middle of the LRU list.
All newly read pages are inserted at a location that by default
3/8 from the tail of the LRU list. The
pages are moved to the front of the list (the most-recently used
end) when they are accessed in the buffer pool for the first
time. Thus, pages that are never accessed never make it to the
front portion of the LRU list, and “age out” sooner
than with a strict LRU approach. This arrangement divides the
LRU list into two segments, where the pages downstream of the
insertion point are considered “old” and are
desirable victims for LRU eviction.
For an explanation of the inner workings of the
InnoDB buffer pool and specifics about the
LRU algorithm, see Section 126.96.36.199, “The InnoDB Buffer Pool”.
You can control the insertion point in the LRU list and choose
InnoDB applies the same optimization
to blocks brought into the buffer pool by table or index scans.
The configuration parameter
the percentage of “old” blocks in the LRU list. The
default value of
37, corresponding to the original fixed ratio
of 3/8. The value range is
5 (new pages in
the buffer pool age out very quickly) to
(only 5% of the buffer pool is reserved for hot pages, making
the algorithm close to the familiar LRU strategy).
The optimization that keeps the buffer pool from being churned
by read-ahead can avoid similar problems due to table or index
scans. In these scans, a data page is typically accessed a few
times in quick succession and is never touched again. The
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
1000. Increasing this value makes more and
more blocks likely to age out faster from the buffer pool.
innodb_old_blocks_time can be
specified in the MySQL option file (
my.ini) or changed at runtime with the
GLOBAL statement. Changing the value at runtime
requires privileges sufficient to set global system variables.
See Section 188.8.131.52, “System Variable Privileges”.
To help you gauge the effect of setting these parameters, the
SHOW ENGINE INNODB STATUS command reports
buffer pool statistics. For details, see
Section 184.108.40.206, “Monitoring the Buffer Pool Using the InnoDB Standard Monitor”.
Because the effects of these parameters can vary widely based on your hardware configuration, your data, and the details of your workload, always benchmark to verify the effectiveness before changing these settings in any performance-critical or production environment.
In mixed workloads where most of the activity is OLTP type with
periodic batch reporting queries which result in large scans,
setting the value of
the batch runs can help keep the working set of the normal
workload in the buffer pool.
When scanning large tables that cannot fit entirely in the
buffer pool, setting
innodb_old_blocks_pct to a
small value keeps the data that is only read once from consuming
a significant portion of the buffer pool. For example, setting
innodb_old_blocks_pct=5 restricts this data
that is only read once to 5% of the buffer pool.
When scanning small tables that do fit into memory, there is
less overhead for moving pages around within the buffer pool, so
you can leave
innodb_old_blocks_pct at its
default value, or even higher, such as
The effect of the
parameter is harder to predict than the
parameter, is relatively small, and varies more with the
workload. To arrive at an optimal value, conduct your own
benchmarks if the performance improvement from adjusting
innodb_old_blocks_pct is not