警告:长信号量等待

Question

在过去的4天里，我的每晚更新都有很多问题，除了1晚，这4天之间一切都很好。

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

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

这已经工作了完美超过2年。通常更新时间在3-4小时左右，这对于每晚更新的数据量来说是正常的。但自周五以来，更新时间一直在9-12小时之间!

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

InnoDB: Warning: a long semaphore wait:线程8676在dic0boot等待。在第36行241.00秒的信号量:互斥锁在0000000053B0C1E8创建文件dict0dict.cc第887行，锁变量1，等待标记1 InnoDB: ######启动InnoDB监控30秒打印诊断信息:InnoDB: Pending preads

InnoDB: ######诊断信息打印到标准错误流InnoDB: Error: semaphore wait has> 600 seconds故意使服务器崩溃，因为它似乎挂起了。2014-07-21 05:20:54 1384 InnoDB: Assertion failure in thread 4996 in文件srv0srv。Cc行1748

InnoDB:我们有意生成一个内存陷阱。InnoDB:提交详细的bug报告到http://bugs.mysql.com。InnoDB:如果你得到重复断言失败或崩溃，甚至InnoDB:立即mysqld启动时，可能会有InnoDB损坏表空间。请参考InnoDB:http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.htmlInnoDB: about forced recovery.

我刚刚重启了服务器，一切正常，所以现在我等待在bugs.mysql.com上发布一个完整的错误报告

我在这个页面上发现了一些东西，似乎是同样的问题，但没有进一步的消息。

我不知道该怎么办，我不知道为什么这一切突然发生了。

从这里我需要提供什么样的细节?

• 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及更高版本的架构变化带来了更多的工作负载比以前更适合禁用自适应散列索引版本，尽管它在默认情况下仍然是启用的。"

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

---

晚上更新:

更新进行得很好。不到6小时。全文索引更新没有问题，但是我仍然发现使用JOIN进行简单的更新查询很慢。(8秒内完成40000条记录，通常在1秒内完成)。

今天将继续尝试微调它。

Answer 1

问题出在innodb_adaptive_hash_index

innodb_adaptive_hash_index=0和重启解决了这个问题。

如问题

所述

" MySQL 5.6及更高版本的架构变化带来了更多的工作负载比以前更适合禁用自适应散列索引版本，尽管它在默认情况下仍然是启用的。"

这对我来说很有效，因为我再也没有遇到过同样的问题。

Answer 2

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

有很多方法可以优化所有的表，但我将给你一个例子，如何使用PHP通过linux控制台

        #!/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;
?>

也是设置的一部分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

更新17-07-2019

我找到导致这个错误的原因了。

问题是我有一个4000行的表。该表每秒收到大约1000次更新。同时，从这个表中，每秒大约有500个选择。通常，选择时间为0.006秒，但几天后选择时间变为5秒。在此之后，当队列中聚集了数千个选择时，会出现错误" a long semaphore wait"。

可能的解决方案:

1)创建另一个表结构，检查索引，将表拆分为几个表。

2)每隔几个小时对表进行优化。

3) Сome为这个表设置一个缓存系统

可能的搜索问题:

一个有用的脚本，可以帮助您查看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