Shulou(Shulou.com)06/01 Report--

InnoDB buffer pool is set to two SUBLIST:

One is to store the blocks needed for most queries.

The other is to store blocks needed for a small number of queries; and can be recycled; [@ more@]

The InnoDB buffer pool is divided into two sublists:

A new sublist containing blocks that are heavily used by queries

And an old sublist containing less-used blocks and from which candidates for eviction are taken.

InnoDB buffer pool is set to two SUBLIST:

One is to store the blocks needed for most queries.

The other is to store the blocks needed for a small number of queries; and can be recycled

In the default operation of the buffer pool

A block when read in is loaded at the midpoint and then moved immediately to the head of the new sublist as soon as an access occurs.

In the case of a table scan (such as performed for a mysqldump operation)

Each block read by the scan ends up moving to the head of the new sublist because multiple rows are accessed from each block.

This occurs even for an one-time scan, where the blocks are not otherwise used by other queries.

Blocks may also be loaded by the read-ahead background thread and then moved to the head of the new sublist by a single access.

These effects can be disadvantageous because they push blocks that are in heavy use by other queries out of the new sublist

To the old sublist where they become subject to eviction.

By default, as long as a block is accessed, the block is immediately placed in the header of the latest access list

Like MYSQLDUMP, if you need to scan the whole table once, then the blocks in BUFFERPOOL will be replaced again.

One disadvantage of this is that the blocks in BUFFERPOOL are swapped in and out very frequently, and it is possible to swap out some hot blocks, which eventually leads to frequent IO.

InnoDB now provides two system variables that enable LRU algorithm tuning:

Innodb_old_blocks_pct

Specifies the approximate percentage of the buffer pool used for the old block sublist.

The range of values is 5 to 95. The default value is 37 (that is, 3amp 8 of the pool).

Innodb_old_blocks_time

Specifies how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access

Before it can be moved to the new sublist.

The default value is 0:

A block inserted into the old sublist moves immediately to the new sublist the first time it is accessed

No matter how soon after insertion the access occurs. If the value is greater than 0, blocks remain in the old sublist until an access occurs at least that many ms after the first access. For example, a value of 1000 causes blocks to stay in the old sublist for 1 second after the first access before they become eligible to move to the new sublist

After BLOCK is visited for the first time, stay in "old sublist" first, wait for "innodb_old_blocks_time" ms and then move to "new sublist".

Such a large full-table scan query will not kick out the hot blocks right away.

For details, see: http://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.