警告:长时间的信号量等待

Question

在过去的4天里，我的夜间更新遇到了巨大的问题，除了一个晚上，在这4天之间一切都很好。

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

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

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

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

InnoDB:警告:长时间的信号量等待：-线程8676已经在dict0boot.ic第36行等待了241.00秒信号量: Mutex在0000000053B0C1E8创建了文件dict0dict.cc行887，锁var 1服务员标志1 InnoDB：######启动InnoDB监视器30秒以打印诊断信息: InnoDB:未决前缀0，pwrite 0 InnoDB：######诊断信息打印到标准错误流InnoDB: InnoDB:信号量等待持续了> 600秒InnoDB:我们故意使服务器崩溃，因为它似乎是挂起的。2014-07-21 05:20:54 1384 InnoDB: srv0srv.cc文件行1748中线程4996中的断言失败 InnoDB:我们故意生成一个内存陷阱。InnoDB:向http://bugs.mysql.com提交详细的bug报告。InnoDB:如果您遇到重复的断言失败或崩溃，甚至在mysqld启动后立即出现InnoDB：，那么InnoDB表空间中可能会出现InnoDB:损坏。请参阅InnoDB：http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html InnoDB:关于强制恢复。

我刚刚重新启动了服务器，所以现在我在bugs.mysql.com中等待发布一个完整的bug报告。

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

我不知道从这里往哪里走，我不知道为什么这会突然发生。

从这里我应该提供什么样的细节？

• 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

也有过这样的问题。数据库一天断掉几次。我不确定这是否对我有帮助，但我的解决方案是优化所有表。到目前为止，这个问题已经三天没有出现了。

有许多优化所有表的方法，但我将给出如何通过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;
?>

也是设置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年

我发现了导致我犯这个错误的问题。

问题是我有一个4,000行的表。这个表每秒钟收到大约1000个更新。同时，从这个表中，每秒钟有大约500个选择。通常情况下，选择时间为0.006秒，但几天后，选择时间为5秒。在那之后，成千上万的选择被聚集在队列中的那一刻，出现了一个错误的“长信号量等待”的时刻。

可能的解决办法：

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

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

3)С为这个表提供了一个缓存系统

可能存在的搜索问题：

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