The default values of both parameters leave the original behavior as of InnoDB Plugin 1.0.4 intact. To take advantage of this feature, you must set different values.
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
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
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
is not sufficient.