问题描述
在过去的4天里，我的每晚更新遇到了很多问题，除了1夜，在这4天之间一切正常.
For the past 4 days I have had massive problems with my nightly updates, except for 1 night were it all went fine in between these 4 days.
在这些更新过程中，我更新了两个全文索引.我以这种方式做.
During these updates i update a couple of fulltext indexes. I do it in this manner.
- 删除全文索引
- 更新全文表
- 添加全文索引
- Drop the fulltext index
- Update the fulltext table
- Add the fulltext index
此功能已完美运行了2年以上.通常的更新时间约为3-4小时，这对于每晚更新的数据量来说是正常的.但实际上自星期五以来，更新时间一直在 9-12 小时之间！
This has been working perfect for over 2 years. Usual update time was around 3-4 hours which was normal for the amount of data that is updated each night. But since Friday really the update times has been between 9-12 hours!
昨晚服务器在引擎中故意崩溃，这是在错误日志中
InnoDB:######诊断信息打印到标准错误流 InnoDB:错误:信号灯等待已持续> 600秒InnoDB:我们 故意使服务器崩溃，因为它似乎已挂起. 2014-07-21 05:20:54 1384 InnoDB:线程4996中的断言失败 文件srv0srv.cc第1748行
InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Error: semaphore wait has lasted > 600 seconds InnoDB: We intentionally crash the server, because it appears to be hung. 2014-07-21 05:20:54 1384 InnoDB: Assertion failure in thread 4996 in file srv0srv.cc line 1748
InnoDB:我们有意生成一个内存陷阱. InnoDB:提交一个 到 http://bugs.mysql.com 的详细错误报告. InnoDB:如果您得到 反复断言失败或崩溃，甚至是InnoDB: mysqld启动时，可能存在InnoDB:InnoDB中的损坏 表空间.请参考InnoDB: http://dev.mysql.com/doc/refman /5.6/zh-CN/force-innodb-recovery.html InnoDB:关于强制恢复.
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 InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html InnoDB: about forcing recovery.
我刚刚重新启动了服务器，它运行正常，因此现在我正等待在bugs.mysql.com中发布完整的错误报告
Ive just restarted the server and it went fine so for now im waiting to post a full bug report in bugs.mysql.com
我在此页面，它似乎是同一种问题，但没有进一步的消息.
I discovered something on this page , and it seems to be the same kind of problem, but no further messages tho.
我不知道从这里去哪里，我也不知道为什么这突然发生.
I dont know where to go from here, I dont know why this is all of a sudden happening.
我必须从这里提供什么样的详细信息?
What kind of details must I provide from here?
- Mysql服务器版本:5.6.13
- sort_buffer_size = 2M
- innodb_buffer_pool_size = 53G
- innodb_log_buffer_size = 4M
- innodb_flush_log_at_trx_commit = 0
- innodb_log_file_size = 25G
编辑
在阅读此后，它指出
我已使用禁用了自适应哈希索引 SET GLOBAL innodb_adaptive_hash_index=0我现在正在尝试第一次尝试看问题是否已解决.情况就像晚上一样.
I have disabled the adaptive hash index using SET GLOBAL innodb_adaptive_hash_index=0and I am now trying the first attempt to see if issue is fixed. Situation is just like at night.
夜间更新:
更新正常.少于6小时.全文索引更新没有问题，但是我仍然发现使用JOIN进行简单的更新查询很慢. (8秒内有40000条记录，通常少于1即可完成.)
The update went fine. Less than 6 hours. There was no problem with the fulltext index update, i still however found that simple update query with JOIN was slow. (40000 records in 8 seconds, which usually was done in less than 1).
今天将继续尝试对其进行微调.
Will continue today to try and fine tune it.
推荐答案
问题出在innodb_adaptive_hash_index
innodb_adaptive_hash_index=0并重新启动即可解决问题.
innodb_adaptive_hash_index=0 and a restart solved the problem.
我在问题中提到的
这对我有用，因为我再也没有遇到过同样的问题.
This worked for me as I have not had the same issue again.
这篇关于警告:长时间等待信号量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！