Rather than using a strict LRU
algorithm, 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
afterward.
Newly read blocks are inserted into the middle of the LRU list.
All newly read pages are inserted at a location that by default
is 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 17.5.1, “Buffer Pool”.
You can control the insertion point in the LRU list and choose
whether InnoDB
applies the same optimization
to blocks brought into the buffer pool by table or index scans.
The configuration parameter
innodb_old_blocks_pct
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 value range is 5
(new pages in
the buffer pool age out very quickly) to 95
(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
configuration parameter
innodb_old_blocks_time
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
1000
. Increasing this value makes more and
more blocks likely to age out faster from the buffer pool.
Both innodb_old_blocks_pct
and
innodb_old_blocks_time
can be
specified in the MySQL option file (my.cnf
or
my.ini
) or changed at runtime with the
SET
GLOBAL
statement. Changing the value at runtime
requires privileges sufficient to set global system variables.
See Section 7.1.9.1, “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
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
innodb_old_blocks_time
during
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
innodb_old_blocks_pct=50
.
The effect of the
innodb_old_blocks_time
parameter is harder to predict than the
innodb_old_blocks_pct
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
sufficient.