警告:长时间等待信号量 [英] Warning: A long semaphore wait

问题描述

在过去的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.

1. 删除全文索引
2. 更新全文表
3. 添加全文索引

1. Drop the fulltext index
2. Update the fulltext table
3. 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:警告:长时间的信号量等待: -线程8676在dict0boot.ic行36上等待了241.00秒的信号量:互斥体在0000000053B0C1E8创建了文件dict0dict.cc 第887行，锁定var 1服务员标志1 InnoDB:######启动InnoDB 监视30秒以打印诊断信息:InnoDB:待办事项 0，pwrites 0

InnoDB: Warning: a long semaphore wait: --Thread 8676 has waited at dict0boot.ic line 36 for 241.00 seconds the semaphore: Mutex at 0000000053B0C1E8 created file dict0dict.cc line 887, lock var 1 waiters flag 1 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0

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

编辑

在阅读此后，它指出

"MySQL 5.6及更高版本中的体系结构更改使更多的工作负载 比以前更适合禁用自适应哈希索引 发布，尽管默认情况下仍处于启用状态."

"The architectural changes in MySQL 5.6 and higher make more workloads suitable for disabling the adaptive hash index than in earlier releases, although it is still enabled by default."

我已使用禁用了自适应哈希索引 SET GLOBAL innodb_adaptive_hash_index=0 我现在正在尝试第一次尝试看问题是否已解决.情况就像晚上一样.

I have disabled the adaptive hash index using SET GLOBAL innodb_adaptive_hash_index=0 and 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.

我在问题中提到的

"MySQL 5.6及更高版本中的体系结构更改使更多的工作负载 比以前更适合禁用自适应哈希索引 发布，尽管默认情况下仍处于启用状态."

"The architectural changes in MySQL 5.6 and higher make more workloads suitable for disabling the adaptive hash index than in earlier releases, although it is still enabled by default."

这对我有用，因为我再也没有遇到过同样的问题.

This worked for me as I have not had the same issue again.

这篇关于警告:长时间等待信号量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！