首页 > SQL数据库管理 > [HowTo]MySQL服务器状态-innodb_buffer_pool

[HowTo]MySQL服务器状态-innodb_buffer_pool

2014年10月21日

[HowTo]MySQL服务器状态-innodb_buffer_pool
http://blog.chinaunix.net/uid-16844903-id-3767860.html
注意:
InnoDB缓冲池中不仅包含表的数据页和索引页,还包括undo页,插入缓冲,自适应哈希索引,锁信息,数据字典.
所以,在InnoDB缓冲池里的数据页永远不会等于池的大小.(也许会无限接近)
在cacti的InnoDB Buffer Pool监控图形中,会出现这种情况:明明有空闲的内存,但是Database Pages不会填满Pool Size
SHOW ENGINE INNODB STATUS(Percona)
这里面的两个状态是什么联系?还没有搞清楚.
Hash table size
Adaptive hash index

————————————-
INSERT BUFFER AND ADAPTIVE HASH INDEX
————————————-
Ibuf: size 1, free list len 995, seg size 997, 34699 merges
merged operations:
insert 209995, delete mark 28278, delete 26716
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 25499809, node heap has 51279 buffer(s)
688855.38 hash searches/s, 70787.26 non-hash searches/s

———————-
BUFFER POOL AND MEMORY
———————-
Total memory allocated 13237223424; in additional pool allocated 0
Internal hash tables (constant factor + variable factor)
Adaptive hash index 1044157824 (203998472 + 840159352)
Page hash 12750664 (buffer pool 0 only)
Dictionary cache 51252364 (51001072 + 251292)
File system 98072 (82672 + 15400)
Lock system 31908976 (31875512 + 33464)
Recovery system 0 (0 + 0)
Dictionary memory allocated 251292
Buffer pool size 786431
Buffer pool size, bytes 12884885504
Free buffers 191819
Database pages 543333
Old database pages 200546
Modified db pages 140937
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 3261, not young 0
0.44 youngs/s, 0.00 non-youngs/s
Pages read 483871, created 59462, written 1160632
2.37 reads/s, 10.62 creates/s, 217.05 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 543333, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

参考
http://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html
http://dev.mysql.com/doc/refman/5.5/en/innodb-monitors.html
http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-buffer-pools.html
http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size
http://www.percona.com/doc/percona-server/5.5/diagnostics/innodb_show_status.html
http://www.percona.com/docs/wiki/percona-server:features:innodb_show_hashed_memory#description

本文的评论功能被关闭了.