MySQL 警告：长时间的信号量等待

Warning: A long semaphore wait

提问by Mad Dog Tannen

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.

在过去的 4 天里，我在每晚更新时遇到了很多问题，除了 1 个晚上在这 4 天之间一切正常。

During these updates i update a couple of fulltext indexes. I do it in this manner.

在这些更新期间，我更新了几个全文索引。我就是这样做的。

1. Drop the fulltext index
2. Update the fulltext table
3. Add the fulltext index

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

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-12hours!

这已经完美地工作了 2 年多。通常更新时间约为 3-4 小时，这对于每晚更新的数据量来说是正常的。但从周五开始，更新时间真的在9-12小时之间！

Last night the server crashed intentionally by the engine, this was in the error log

昨晚服务器故意崩溃，这是在错误日志中

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: ###### 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: 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.htmlInnoDB: about forcing recovery.

InnoDB：警告：一个长信号量等待：--Thread 8676 已在 dict0boot.ic 第 36 行等待了 241.00 秒信号量：Mutex 在 0000000053B0C1E8 创建了文件 dict0dict.cc 第 887 行，lock var 1DBwaiters：flag ### Inno ## 启动 InnoDB Monitor 30 秒以打印诊断信息：InnoDB: Pending preads 0, pwrites 0

InnoDB：###### 诊断信息打印到标准错误流 InnoDB：错误：信号量等待已持续 > 600 秒 InnoDB：我们故意使服务器崩溃，因为它似乎已挂起。2014-07-21 05:20:54 1384 InnoDB：文件 srv0srv.cc 行 1748 中线程 4996 中的断言失败

InnoDB：我们故意生成一个内存陷阱。InnoDB：向http://bugs.mysql.com提交详细的错误报告。InnoDB：如果在mysqld 启动后立即出现多次断言失败或崩溃，即使是InnoDB:，也可能在InnoDB 表空间中出现InnoDB: 损坏。请参考 InnoDB：http: //dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.htmlInnoDB：关于强制恢复。

Ive just restarted the server and it went fine so for now im waiting to post a full bug report in bugs.mysql.com

我刚刚重新启动了服务器并且一切正常，所以现在我正在等待在 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 server version: 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.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

EDIT

编辑

After reading this, it states that

阅读本文后，它指出

"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."

“与早期版本相比，MySQL 5.6 及更高版本中的架构更改使更多工作负载适合禁用自适应哈希索引，尽管默认情况下它仍处于启用状态。”

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.

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

---

---

Night update:

夜间更新：

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 JOINwas slow. (40000 records in 8seconds, which usually was done in less than 1).

更新很顺利。不到6小时。全文索引更新没有问题，但是我仍然发现简单的更新查询JOIN很慢。（40000 条记录以8秒为单位，通常在不到 的时间内完成1）。

Will continue today to try and fine tune it.

今天将继续尝试和微调它。

采纳答案by Mad Dog Tannen

The problem was with innodb_adaptive_hash_index

问题是 innodb_adaptive_hash_index

innodb_adaptive_hash_index=0and a restart solved the problem.

innodb_adaptive_hash_index=0并重新启动解决了问题。

As ive stated in the question

正如我在问题中所说

"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."

“与早期版本相比，MySQL 5.6 及更高版本中的架构更改使更多工作负载适合禁用自适应哈希索引，尽管默认情况下它仍处于启用状态。”

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

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

回答by Sanya Snex

Had such a problem too. The database broke without reason several times a day. I'm not sure if this helped me, but my solution is to optimize all tables. Three days so far, this problem no longer appears.

也遇到过这样的问题。数据库每天无故崩溃好几次。我不确定这是否对我有帮助，但我的解决方案是优化所有表。三天到现在，这个问题不再出现。

There are many methods for optimizing all tables, but I'll give you an example of how to do this using PHP via the linux console

优化所有表的方法有很多，但我将举例说明如何通过 linux 控制台使用 PHP 执行此操作

        #!/bin/php -n
        <?php
    // /bin/php -n /sysmyx/mysql/hand_optimize_all_tables.php
    dl('mysqlnd.so');
    dl('mysqli.so');
$timestart  = time();
$n=0;
$con=mysqli_connect("localhost","roootmysql","passss");

if (mysqli_connect_errno())  {  echo "mysql error".PHP_EOL;
exit;
}
mysqli_query($con,"SET GLOBAL innodb_buffer_pool_dump_now = 1");
$res = mysqli_query($con,"SHOW DATABASES");
while ($row = mysqli_fetch_assoc($res)) {
$db_name=$row['Database'];
if ($db_name!="mysql" && $db_name!="information_schema" && $db_name!="performance_schema" && $db_name!="" && $db_name!="sys") {
echo '*'.$db_name.'*'.PHP_EOL;
//!!!!!!!!!!!!!!!!!!!!!!!// $query="SHOW TABLE STATUS FROM $db_name where Data_free>0;";
$query="SHOW TABLE STATUS FROM $db_name";
$tabbll=mysqli_query($con,$query);
while ($row2 = mysqli_fetch_assoc($tabbll)) {
$n++;
$opt_table='`'.$db_name.'`.`'.$row2['Name'].'`';
$query2="OPTIMIZE TABLE $opt_table";
$time1 = time();
mysqli_query($con,$query2);
$time2 = time();
$time3 = $time2-$time1;
echo $n.' '.$time3.' '.$row2['Data_free'].' '.$opt_table.PHP_EOL;
}}}
mysqli_query($con,"SET GLOBAL innodb_buffer_pool_load_now = 1");
mysqli_close($con);
$timeend  = time();
$time  = $timeend-$timestart;
?>

Also part of the settings my.cnf

也是设置 my.cnf 的一部分

innodb_thread_concurrency=0
flush_time=0
innodb_adaptive_hash_index=0
innodb_adaptive_hash_index_parts=1
innodb_purge_threads=1
innodb_fatal_semaphore_wait_threshold=60

UPDATE 17-07-2019

更新 17-07-2019

I found the problem that caused me this error.

我发现了导致我出现此错误的问题。

The problem was that I had a table of 4,000 rows. This table received about 1000 updates every second. Also, from this table at the same time, there are about 500 selections every second. Usually, the time of selection is 0.006 seconds, but after several days the selection time becomes 5 seconds. After that, the moment that thousands of selects were gathered in the queue and there was a moment of error "A long semaphore wait".

问题是我有一个 4,000 行的表。该表每秒收到大约 1000 次更新。同时，从这个表中，每秒大约有 500 个选择。通常，选择时间为 0.006 秒，但几天后选择时间变为 5 秒。在那之后，在队列中聚集了数千个选择的那一刻，并且出现了“A long semaphore wait”错误的时刻。

Possible solutions:

可能的解决方案：

1) Make another table structure, check the indixes, split the table into several tables.

1）制作另一个表结构，检查索引，将表拆分为几个表。

2 )Do every few hours optimize the table.

2）每隔几个小时做一次优化表。

3) Сome up with a caching system for this table

3) 为这张表设计一个缓存系统

Possible search problems:

可能的搜索问题：

A useful script that will help you see what queries are gathered at the time of mysql crash. Run the script every minute through the cron.

一个有用的脚本，将帮助您查看在 mysql 崩溃时收集了哪些查询。每分钟通过 cron 运行脚本。

#!/bin/bash 
USER=$(</sys_snting/mysql_user)
PASSWORD=$(</sys_snting/mysql_pass)
num=$(mysql --user=$USER --password=$PASSWORD -s -N -e "SELECT count(*) FROM information_schema.processlist ;")
if [ $num -ge 500 ] ; then
mysql --user=$USER --password=$PASSWORD -e "show full processlist" > /media/bug/$(date +%Y%m%d%H%M%S)_$num.txt
echo $num

# Kill selections that can lead to "A long semaphore wait"
# mysql --user=$USER --password=$PASSWORD -N -e "SELECT Id FROM information_schema.processlist where INFO like '%SELECT \`d_narfe\` FROM \`maitableep_com\`.\`5000_active\` WHERE%';" | while IFS= read -r loop
# do
#     echo "$loop"
# mysqladmin --user=$USER --password=$PASSWORD  kill $loop
# done 
fi