title: Warning: A long semaphore wait
body: <p>For the past 4 days I have had massive problems with my <strong>nightly updates</strong>, except for 1 night were it all went fine in between these 4 days.</p>
  
  <p>During these updates i update a couple of fulltext indexes. I do it in this manner. </p>
  
  <blockquote>
    <ol>
    <li>Drop the fulltext index</li>
    <li>Update the fulltext table</li>
    <li>Add the fulltext index</li>
    </ol>
  </blockquote>
  
  <p>This has been working <strong>perfect for over 2 years</strong>. 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 <strong>9-12</strong> hours!</p>
  
  <p><strong>Last night the server crashed intentionally by the engine, this was in the error log</strong></p>
  
  <blockquote>
    <p>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</p>
    
    <p>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</p>
    
    <p>InnoDB: We intentionally generate a memory trap. InnoDB: Submit a
    detailed bug report to <a href="http://bugs.mysql.com" rel="noreferrer">http://bugs.mysql.com</a>. 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:
    <a href="http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html" rel="noreferrer">http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html</a>
    InnoDB: about forcing recovery.</p>
  </blockquote>
  
  <p>Ive just restarted the server and it went fine so for now im waiting to post a full bug report in bugs.mysql.com</p>
  
  <p>I discovered something on this <a href="http://www.percona.com/forums/questions-discussions/mysql-and-percona-server/12785-innodb-warning-a-long-semaphore-wait-mysql-crash-%EF%BC%81%EF%BC%81%EF%BC%81%EF%BC%81" rel="noreferrer">page</a> , and it seems to be the same kind of problem, but no further messages tho.</p>
  
  <p>I dont know where to go from here, I dont know why this is all of a sudden happening.</p>
  
  <p>What kind of details must I provide from here?</p>
  
  <ul>
  <li>Mysql server version: 5.6.13  </li>
  <li>sort_buffer_size = 2M </li>
  <li>innodb_buffer_pool_size = 53G  </li>
  <li>innodb_log_buffer_size = 4M</li>
  <li>innodb_flush_log_at_trx_commit = 0  </li>
  <li>innodb_log_file_size = 25G</li>
  </ul>
  
  <p><strong>EDIT</strong></p>
  
  <p>After reading <a href="http://dev.mysql.com/doc/refman/5.6/en/innodb-adaptive-hash.html" rel="noreferrer">this</a>, it states that </p>
  
  <blockquote>
    <p>"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."</p>
  </blockquote>
  
  <p>I have disabled the adaptive hash index using
   <code>SET GLOBAL innodb_adaptive_hash_index=0</code> 
  and I am now trying the first attempt to see if issue is fixed. Situation is just like at night.</p>
  
  <hr>
  
  <p><strong>Night update:</strong></p>
  
  <p>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 <code>JOIN</code> was slow. (40000 records in <code>8</code> seconds, which usually was done in less than <code>1</code>).</p>
  
  <p>Will continue today to try and fine tune it.</p>