Warning: A long semaphore wait

Question

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. Drop the fulltext index
2. Update the fulltext table
3. Add the fulltext index

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!

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.html InnoDB: about forcing recovery.

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

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

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.

---

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

Answer 1

The problem was with innodb_adaptive_hash_index

innodb_adaptive_hash_index=0 and a restart solved the problem.

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

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

Answer 2

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

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

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

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

Possible solutions:

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

2 )Do every few hours optimize the table.

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

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.

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