[MySQL FAQ]系列 -- InnoDB报错,buffer不够用

一朋友发来消息,说他的mysql报错,日志大致如下:

090318 15:16:35 InnoDB: WARNING: over 4 / 5 of the buffer pool is occupied by
InnoDB: lock heaps or the adaptive hash index! Check that your
InnoDB: transactions do not set too many row locks.
InnoDB: Your buffer pool size is 16 MB. Maybe you should make
InnoDB: the buffer pool bigger?
InnoDB: Starting the InnoDB Monitor to print diagnostics, including
InnoDB: lock heap and hash index sizes.

 

太明显了,innodb的buffer pool设置太小了,才16Mb,约80%的内存都被用于维护锁信息的哈希表给占用了,因此出问题了。
解决办法很简单,只需要加大 innodb_buffer_pool_size 即可,如果是32位系统,上限是2G;如果是64位系统,并且专用mysql服务器的话,建议设置服务器总内存大小的50%左右。最终值需要根据实际运行情况不断调整。