mysql 5.5 innodb 优化_mysql 5.5 -- innodb buffer pool优化

InnoDB buffer pool 被设为两个SUBLIST：

一个是存放大部分查询所需用到的块；

另一个是存放小量查询所需用到的块；并可以被回收；[@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 被设为两个SUBLIST：

一个是存放大部分查询所需用到的块；

另一个是存放小量查询所需用到的块；并可以被回收；

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 a 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.

默认情况下，一个数据块只要被访问，那么这个块将立刻被放到“最新访问列表”的头部；

像MYSQLDUMP，需求全表扫描一次，那么BUFFERPOOL里的块将被重新换一次；

这样做的一个缺点就是BUFFERPOOL里的块换进换出特别的频繁，而且有可能把一些热块给换出去；最终导致频繁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, 3/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

BLOCK 被第一次访问的后，先呆在”old sublist“，的等待”innodb_old_blocks_time“ms后会移到“new sublist”;

这样一个大的全表扫描查询就不会马上把热块给踢出去；

详见： http://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html