请选择 进入手机版 | 继续访问电脑版
查看: 697|回复: 0

[Mysql数据库] mysql5.6-buffer pool 预热

3万

主题

3万

帖子

10万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
100197
发表于 2015-12-2 18:44:46

mysql5.6-buffer pool 预热,有需要的朋友可以参考下。


参考:http://dev.mysql.com/doc/refman/5.6/en/innodb-preload-buffer-pool.html


Several InnoDB system variables control the size of the buffer pool and let you tune the LRU algorithm:


***innodb_buffer_pool_size


Specifies the size of the buffer pool. If your buffer pool is small and you have sufficient memory, making the pool larger can improve performance by reducing the amount of disk I/O needed as queries access InnoDB tables.


***innodb_buffer_pool_instances


Divides the buffer pool into a user-specified number of separate regions, each with its own LRU list and related data structures, to reduce contention during concurrent memory read and write operations. This option takes effect only when you set the innodb_buffer_pool_size to a size of 1 gigabyte or more. The total size you specify is divided among all the buffer pools. For best efficiency, specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1 gigabyte.


***innodb_old_blocks_pct


Specifies the approximate percentage of the buffer pool that InnoDB uses 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 to the new sublist when Innodb has evicted 1/4 of the inserted block's pages from the buffer pool, 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.
'


innodb_old_blocks_time can be set at runtime, so you can change it temporarily while performing operations such as table scans and dumps:


SET GLOBAL innodb_old_blocks_time = 1000;
... perform queries that scan tables ...
SET GLOBAL innodb_old_blocks_time = 0;
This strategy does not apply if your intent is to “warm up” the buffer pool by filling it with a table's content. For example, benchmark tests often perform a table or index scan at server startup, because that data would normally be in the buffer pool after a period of normal use. In this case, leave innodb_old_blocks_time set to 0, at least until the warmup phase is complete.


**Monitoring the Buffer Pool


The output from the InnoDB Standard Monitor contains several fields in the BUFFER POOL AND MEMORY section that pertain to operation of the buffer pool LRU algorithm:


Old database pages: The number of pages in the old sublist of the buffer pool.


Pages made young, not young: The number of old pages that were moved to the head of the buffer pool (the new sublist), and the number of pages that have remained in the old sublist without being made new.


youngs/s non-youngs/s: The number of accesses to old pages that have resulted in making them young or not. This metric differs from that of the previous item in two ways. First, it relates only to old pages. Second, it is based on number of accesses to pages and not the number of pages. (There can be multiple accesses to a given page, all of which are counted.)


young-making rate: Hits that cause blocks to move to the head of the buffer pool.


not: Hits that do not cause blocks to move to the head of the buffer pool (due to the delay not being met).


回复

使用道具 举报