天天看点

mysql innodb_buffer_pool

注意:

    1. InnoDB缓冲池中不仅包含表的数据页和索引页,还包括undo页,插入缓冲,自适应哈希索引,锁信息,数据字典.
    2. 所以,在InnoDB缓冲池里的数据页永远不会等于池的大小.(也许会无限接近)
    3. 在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