Search code examples
mysqlsqlinnodbalter-tablefull-text-indexing

Warning: A long semaphore wait

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.

Solution

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