[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%左右。最终值需要根据实际运行情况不断调整。
最近评论