MySQL Bugs: #32149: Long semaphore wait for adaptive hash latch

Bug #32149	Long semaphore wait for adaptive hash latch
Submitted:	6 Nov 2007 18:29	Modified:	26 Jun 2009 19:13
Reporter:	Mark Callaghan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.0	OS:	Any
Assigned to:	Tatiana Azundris Nuernberg	CPU Architecture:	Any
Tags:	adaptive, hash, innodb, latch, Semaphore, wait

Description:
A server crashed because of a long semaphore wait for the adaptive hash latch. The holder of the latch is sorting. Which means it is not running within InnoDB and might run for some time.

Is a session not forced to release InnoDB latches when it is sorting? There is code to force this when a query result is written back to the client. The same code should be called in this case.

Related bugs:
http://bugs.mysql.com/bug.php?id=29560
http://bugs.mysql.com/bug.php?id=20358

The latch holder:
---TRANSACTION 0 135874739, ACTIVE 839 sec, process no 11811, OS thread id 1201088864, thread declared inside InnoDB 488
mysql tables in use 14, locked 0
, holds adaptive hash latch
MySQL thread id 1053806, query id 37526001 10.X.X.X fe Sorting result
CREATE TEMPORARY TABLE scratch.tmpResultsTable (INDEX (A_1,A_2,A_3,A_4,A_5,A_6,A_7,A_8,A_9,A_10,A_11,A_12)) SELECT ZITD.1 AS A_1, ACC.2 AS A_2, CU.3 AS A_3, TZ.4 AS A_4, CAMP.5 AS A_5, AG.6 AS A_6, IF(C.7  >= 10000000 , IF(C.7  IN (27, 39) , REPLACE(C.8, '::', ' >> '), C.8
Trx read view will not see trx with id >= 0 135874740, sees < 0 135863686

The warning message:
InnoDB: Warning: a long semaphore wait:
--Thread 1238362464 has waited at btr0sea.c line 489 for 241.00 seconds the semaphore:
X-lock on RW-latch at 0x2b3c6228b0b8 created in file btr0sea.c line 139
a writer (thread id 1238362464) has reserved it in mode  wait exclusive
number of readers 1, waiters flag 1
Last time read locked in file btr0sea.c line 746
Last time write locked in file btr0sea.c line 1236
InnoDB: Warning: a long semaphore wait:
--Thread 1211738464 has waited at btr0sea.c line 1127 for 241.00 seconds the semaphore:
S-lock on RW-latch at 0x2b3c6228b0b8 created in file btr0sea.c line 139
a writer (thread id 1238362464) has reserved it in mode  wait exclusive
number of readers 1, waiters flag 1
Last time read locked in file btr0sea.c line 746
Last time write locked in file btr0sea.c line 1236
InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:
InnoDB: Pending preads 0, pwrites 0

The thread waiting for an X lock:
---TRANSACTION 0 135885160, ACTIVE 256 sec, process no 11811, OS thread id 1238362464 starting index read, thread declared inside InnoDB 100
mysql tables in use 3, locked 0
MySQL thread id 1054270, query id 37540780 X.X.X.X re Sending data
select sum(CU)
  from C inner join A using (CId) inner join CS using (CId) inner join FCA using (CId) where A.1 < now() - interval 7 / 40 * 140 *24 hour and
  A.2 > now() - interval 7 / 40 * 140 *24 hour - interval 7 * 24 hour  and IsF
='true' and FCATId=1 and CTime < A. + interval 14 day and Product='a'
Trx read view will not see trx with id >= 0 135885161, sees < 0 135874739

How to repeat:
NA

Suggested fix:
Force the session to release InnoDB latches when sorting. There is an InnoDB method to do this. I want to know where I should add a call to it (where does sort begin?).

InnoDB has a method to force latches to be released, innobase_release_temporary_latches, and this is called by ha_release_temporary_latches. Is a call to ha_release_temporary_latches needed prior to starting a sort?

Mark, thank you again for an ingenious bug report.

Assigning to Inaam. Maybe we should not keep the adaptive hash latch in queries whose plan includes a sort?

--Heikki

ha_release_temporary_latches is called by:
select_send::send_data -- to send rows back to a client
Query_cache::store_query -- to add a query result to the query cache
select_send::send_eof -- to send end of fetch to the client

The method filesort in filesort.cc can also call ha_release_temporary_latches.

But I suspect we will find new places that need this call in the future. It would be easier if the adaptive hash latch was always release when leaving InnoDB.

Mark,

You are right that we may end up finding more and more places in MySQL where holding the hash latch is not a good idea. This is a workaround and for now we probably will stick with it. That is we'll call ha_release_temporary_latches() in filesort() to fix this issue.

You asked why we need to keep this adaptive hash latch at all. I talked to Heikki about it and he explained that this is done to save on cpu wasted on repeated acquistion/release cycles. He did test the effectiveness of this approach (though a few years ago) on a case of select count(*) from a join and it showed around 20% improvement.

The true fix is, however, to make adaptive hash latch more scalable. And once that is achieved we probably will also do away with keeping the latch in MySQL code. That is on our TODO but, unfortunately, I am not in a position to say when, if at all, that will be done.

regards,
inaam

I requested MySQL team to include a call to ha_release_temporary_latches() in filesort. Tim has agreed that we can push it to 5.x after we get a nod from the Optimizer team.

I am un-assigning myself and setting category to Optimizer from InnoDB storage engine. Though the issue is with InnoDB adaptive hash latch, the fix/workaround is in filesort and that is to call ha_release_temporary_latches().

I wrote earlier to Tim and he agreed that once Optimizer team okays the change to call ha_release_temporary_latches() in filesort, it will be pushed in BK.

regards,
inaam

We have come across several other locations where the adaptive hash latch is held too long, sometimes causing the server to crash.  Thus far, we have added calls to ha_release_temporary_latches() in the following functions:

filesort(): this has been our most common problem.

select_insert::send_data(): We have had a problem with a few "create temporary table" queries that select many rows from an existing table. The query status remains in "Sending data" for a long time, holding the latch all the while.

create_myisam_from_heap(): We have seen queries hold the latch for a long time with status "converting HEAP to MyISAM".

ha_myisam::repair(): We have noticed threads with status "Repair with keycache" holding the adaptive search latch for a long time - I don't recall what kind of queries these were, but they must have accessed an innodb table in an earier phase to be holding the latch.

It would be nice if ha_release_temporary_latches() could be called every time the query transitions to a different phase (starts doing significantly different work).  I would guess that most phases either need the latch frequently, or do not use it at all - and when not using it at all, they are unable to release it (without the explicit innobase_release_temporary_latches() call).  

I noticed that the thd_proc_info function currently gets called in most of these places - it might be reasonable to convert this into a more general "phase transition" function that could update process info, release latches, and collect/report other statistics about the phase.  For example it might be interesting to learn which phases a server spends the most total time in, or the phase-time breakdown for a single query.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/41470

ChangeSet@1.2570, 2008-01-30 19:00:20-07:00, tsmith@ramayana.hindu.god +1 -0
  Bug #32149  Long semaphore wait for adaptive hash latch
  
  Fix by calling ha_release_temporary_latches() before ::filesort().

Patch queued to 5.0-, 5.1-, and 6.0-build team trees.

@Ben:  Thank you for your contributions to this bug's scope.  The fix that's been pushed is only for the filesort().

Your suggestion to systematically release the temporary latches throughout the server is a good one, but we want to add this current simple fix without further delay.

Regards,

Timothy

Pushed into 5.1.24-rc

Pushed into 5.0.58

Pushed into 6.0.5-alpha

Noted in 5.0.58, 5.1.24, 6.0.5 changelogs.

InnoDB adaptive hash latches could be held too long, resulting in a
server crash.

Fix also noted in the 5.1.23-ndb-6.3.11 changelog.

This bug is still open. We described several places in the code where the problem exists. Only one of those has been fixed.

A call to ha_release_temporary_latches() also needed in free_tmp_tables() in sql_select.cc. It can take a long time to remove a huge temp table depending on your filesystem.

A fix for 5.0 is here -- http://bazaar.launchpad.net/~mdcallag/+junk/5.0-map/revision/2693. I will try to submit this to the 5.0 launchpad branch.

A fix for 5.1 is here -- http://bazaar.launchpad.net/~mdcallag/+junk/5.1-map/revision/2749

I have also sent merge requests to dev-public@mysql.com using the public 5.0 and 5.1 branches

Add another to the list of places where Google reports problems (I overlooked a comment).  From reading through this bug report, I believe the following is a complete list:

- select_insert::send_data()
- create_myisam_from_heap()
- ha_myisam::repair()
- free_tmp_tables() in sql_select.cc

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/75655

2765 Tatiana A. Nurnberg	2009-06-05
      Bug#32149: Long semaphore wait for adaptive hash latch
      
      Holding on to the temporary inno hash index latch is an optimization in
      many cases, but a pessimization in some others.
      
      Release temporary latches for those corner cases we (or rather, or customers,
      thanks!) have identified, that is, when we are about to do something that
      might take a really long time, like REPAIR or filesort.
     @ sql/ha_myisam.cc
        Let go of (inno, for now) latch when doing MyISAM-repair.
        (optimize passes through repair.) ("Stuck" in "Repair with
        keycache".)
     @ sql/sql_insert.cc
        Let go of (inno, for now) latch when doing CREATE...SELECT
        in select_insert::send_data() -- it might take a while.
        ("stuck" in "Sending data")
     @ sql/sql_select.cc
        Release temporary (inno, for now) latch on
        - free_tmp_table() (this can take surprisingly long, "removing tmp table")
        - create_myisam_from_heap() (HEAP table overflowing onto disk as MyISAM,
          "converting HEAP to MyISAM")

Tatiana, does the CREATE ... SELECT case also apply to INSERT ... SELECT? Just so we can know what operations will see improved concurrency.

Docs requests for this one:

1. Please tweak the 14 March 2008 5.0.58/5.1.24/6.0.5 changelog entry to mention "Improves throughput for other queries when one is sorting".
2. In the new change please mention "If the adaptive hash index is on, improves throughput for other queries when one is removing a temporary table, changing a temporary table from memory to disk, using CREATE ... SELECT or performing a MyISAM repair on a table used within a transaction".

If we're doing anything to highlight performance improvements within the server both sets of changes merit inclusion. There have been enough performance improvements to merit a tag for them and it seems useful to do so. Let me know if you want help building a list of past changes to tag if you agree.

Pushed into 5.0.84 (revid:gkodinov@mysql.com-20090616082753-kwe0l8uoictxhojf) (version source revid:azundris@mysql.com-20090604222308-ab8xhwziwraigafw) (merge vers: 5.0.83) (pib:6)

Pushed into 5.1.36 (revid:joro@sun.com-20090616102155-3zhezogudt4uxdyn) (version source revid:azundris@mysql.com-20090604233008-e6nft7x0nbgm2v4t) (merge vers: 5.1.36) (pib:6)

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:azundris@mysql.com-20090605135423-phb9aa55ho8khc7d) (merge vers: 6.0.12-alpha) (pib:11)

Revised changelog entry for 5.0.58, 5.1.24, 6.0.5:

InnoDB adaptive hash latches could be held too long during filesort
operations, resulting in a server crash. Now the hash latch is
released when a query on InnoDB tables performs a filesort. This
eliminates the crash and may provide significant performance
improvements on systems on which many queries using filesorts with
temporary tables are being performed.

Changelog entry for 5.0.84, 5.1.36, 5.4.4:

The InnoDB adaptive hash latch is released (if held) for serveral
potentially long-running operations. This improves throughput for
other queries if the current query is removing a temporary table,
changing a temporary table from memory to disk, using CREATE TABLE
... SELECT, or performing a MyISAM repair on a table used within a
transaction.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)

Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)

Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Noted in 5.5.0 changelog.