MySQL 5.5 频繁crash，help

jinguanding

InnoDB: Failing assertion: page_get_n_recs(page) > 1
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to

不应该是那些SESSION参数配置多大的问题，重点信息：InnoDB: Failing assertion: page_get_n_recs(page) > 1
个人推测的问题可能：
1>.111115 22:08:47 InnoDB: Initializing buffer pool, size = 8.0G 这8G内存空间被数据占用满了，没有空闲的页可用；
2>.配置的最大脏页比例很大，检查下show variables like 'innodb_max_dirty_pages_pct'; 设置为多大了？
3>.当时的IO Wait数据是否还有，以及是否很大? 也即磁盘IO出现瓶颈导致 从innodb_buffer_pool_size数据刷回磁盘的操作非常慢 而出现时间延迟，
     导致超过InnoDB master Thread等待获得空闲页的时间，从而导致InnoDB master thread启动自杀程序，而重新启动....

相关证据信息：
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/ ... nnodb-recovery.html
InnoDB: about forcing recovery.
111115 22:08:45 - mysqld got signal 6 ;

请LZ再提供下数据字典信息：show variables like 'innodb_additional_mem_pool_size';的大小
当时的STATUS数据不知道是否有监控信息，若是有的话，那就更好判断了...

飞鸿无痕_cu

学习了，呵呵！

skywalker418

jinguanding 发表于 2011-11-16 18:38
InnoDB: Failing assertion: page_get_n_recs(page) > 1
InnoDB: We intentionally generate a memory tra ...

innodb_max_dirty_pages_pct = 90
innodb_additional_mem_pool_size = 16M
没有当时的IO Wait数据，这是一台从库，在业务高峰时宕的

philip_zhong

skywalker418 发表于 2011-11-16 23:24
innodb_max_dirty_pages_pct = 90
innodb_additional_mem_pool_size = 16M
没有当时的IO Wait数据，这是 ...

看下这个bug，http://bugs.mysql.com/bug.php?id=44100
If max_connections variable is set to a value less than the number of currently open
connections, the server crashes soon afterwards.
影响version：5.0.72, 5.0.79, 5.1.30, 5.1.34, 6.0.10

jinguanding

skywalker418 发表于 2011-11-16 23:24
innodb_max_dirty_pages_pct = 90
innodb_additional_mem_pool_size = 16M
没有当时的IO Wait数据，这是 ...

innodb_max_dirty_pages_pct = 90 这个设置的非常高哦

你服务器有几块盘，做的RAID几？

另外你从服务器提供哪些服务？

philip_zhong

if it is Bug #56228 it should be solved in MySQL 5.5.9

See http://dev.mysql.com/doc/refman/5.5/en/news-5-5-9.html

InnoDB Storage Engine: The server could crash with an assertion error, if a stored procedure, stored function, or trigger modified one InnoDB table containing an auto-increment column, and dropped another InnoDB table containing an auto-increment column. (Bug #56228)
有没有这样的使用？

philip_zhong

check了mysql5.5相关的page_get_n_recs(page) > 1的bug，有两个与你的比较相似，你可以看下错误信息是不是完全匹配的。

bug：InnoDB: Failing assertion: page_get_n_recs(page) > 1 on slaves
http://bugs.mysql.com/bug.php?id=61324

bug:InnoDB: Failing assertion: page_get_n_recs(page) > 1
http://bugs.mysql.com/bug.php?id=61104

skywalker418

jinguanding 发表于 2011-11-17 09:29
innodb_max_dirty_pages_pct = 90 这个设置的非常高哦

你服务器有几块盘，做的RAID几？

2块盘raid1，就一个只读从库

skywalker418

philip_zhong 发表于 2011-11-17 10:00
check了mysql5.5相关的page_get_n_recs(page) > 1的bug，有两个与你的比较相似，你可以看下错误信息是不是完 ...

表现还是不太一样

skywalker418

philip_zhong 发表于 2011-11-17 09:45
if it is Bug #56228 it should be solved in MySQL 5.5.9

See http://dev.mysql.com/doc/refman/5.5/en ...

我用的版本是5.5.14