Bug #56822 Add a thread state for sessions waiting on the query cache lock
Submitted: 16 Sep 2010 17:23 Modified: 16 Nov 2010 2:20
Reporter: Davi Arnaut (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Query Cache Severity:S4 (Feature request)
Version:5.5 OS:Any
Assigned to: Davi Arnaut CPU Architecture:Any

[16 Sep 2010 17:23] Davi Arnaut
Description:
Add a thread state for sessions waiting on the query cache lock. This makes it easy to identify when threads are parked on the query cache lock -- that is, when query cache is being a bottleneck.

How to repeat:
See http://www.xaprb.com/blog/2010/09/15/making-query-cache-contention-more-obvious/

Suggested fix:
Set a thread state in Query_cache::lock and Query_cache::lock_and_suspend.
[30 Sep 2010 15:38] Valeriy Kravchuk
Bug #57144 was marked as a duplicate of this one.
[30 Sep 2010 16:46] Bugs System
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/119581

3211 Davi Arnaut	2010-09-30
      Bug#56822: Add a thread state for sessions waiting on the query cache lock
      
      The problem was that there was no obvious way to detect query
      cache contention as a thread waiting on the query cache lock
      didn't update its thread state.
      
      The solution is to update the thread state when the query cache
      lock needs to be acquired.  Whenever the lock is to be acquired,
      the thread state is updated to "Waiting for query cache lock"
      and is reset once the lock is granted or the wait is interrupted.
     @ sql/sql_cache.cc
        Set and reset the thread state whenever a attempt to lock the
        query cache is made.
[30 Sep 2010 17:16] Marc ALFF
The wait on the query cache mutex is already instrumented and visible in the performance schema.
Aggregates that show how much time a thread / the server waited on this mutex are also available, exposed by performance schema tables.

Adding a new thd->proc_info state for a mutex lock just seems wrong: if this pattern is followed, will every mutex lock then be also instrumented with a new thread state ?
The result will me a lot of duplication, and thread states that are less meaningful to a user.
[30 Sep 2010 17:35] Mark Leith
I agree, see the following for the right way to get this in 5.5 already:

http://www.markleith.co.uk/?p=377
[30 Sep 2010 18:29] Davi Arnaut
Marc,

This is only for the query cache. The contention there can be so damaging to a server that I think it warrants being visible in the thread state (i.e. show processlist and etc). There is absolutely no plan to introduce a thread state for every mutex. This is a exception, not the rule.

Peter, Mark,

I didn't say there was no other way, only that there was no obvious way. I'll remove the obvious and instead focus that we want to make it more evident. Also, the query cache lock is a bit different, the mutex is not held while the query cache is locked. So, this thread state is going to reflect when a thread is waiting to change the query cache state to locked, not just while it waits for the mutex. The mutex is only held for brief periods while the state of the query cache is checked.
[30 Sep 2010 18:38] Davi Arnaut
Also, I think we should probably also add a mention of P_S in the documentation of thread states, show processlist and the like. There are already a few thread states that are used to reflect the wait for a "lock", which can probably be better investigated with the help of P_S.
[30 Sep 2010 21:24] Bugs System
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/119591

3211 Davi Arnaut	2010-09-30
      Bug#56822: Add a thread state for sessions waiting on the query cache lock
      
      The problem was that threads waiting on the query cache lock
      are not easily seen due to the lack of a state indicating that
      the thread is waiting on the said lock. This made it difficult
      for users to quickly spot (for example, via SHOW PROCESSLIST)
      a query cache contention problem.
      
      The solution is to update the thread state when the query cache
      lock needs to be acquired. Whenever the lock is to be acquired,
      the thread state is updated to "Waiting for query cache lock"
      and is reset once the lock is granted or the wait is interrupted.
      The intention is to make query cache related hangs more evident.
      
      To further investigate query cache related locking problems, one
      may use PERFORMANCE_SCHEMA to track the overhead associated with
      the locking bits and determine which particular lock is being a
      contention point.
     @ sql/sql_cache.cc
        Set and reset the thread state whenever a attempt to lock the
        query cache is made.
[7 Oct 2010 22:51] Bugs System
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/120315

3215 Davi Arnaut	2010-10-07
      Bug#56822: Add a thread state for sessions waiting on the query cache lock
      
      The problem was that threads waiting on the query cache lock
      are not easily seen due to the lack of a state indicating that
      the thread is waiting on the said lock. This made it difficult
      for users to quickly spot (for example, via SHOW PROCESSLIST)
      a query cache contention problem.
      
      The solution is to update the thread state when the query cache
      lock needs to be acquired. Whenever the lock is to be acquired,
      the thread state is updated to "Waiting for query cache lock"
      and is reset once the lock is granted or the wait is interrupted.
      The intention is to make query cache related hangs more evident.
      
      To further investigate query cache related locking problems, one
      may use PERFORMANCE_SCHEMA to track the overhead associated with
      the locking bits and determine which particular lock is being a
      contention point.
     @ sql/sql_cache.cc
        Set and reset the thread state whenever a attempt to lock the
        query cache is made.
        
        Use DEBUG_SYNC instead of the now unnecessary wait_for_kill hack.
[7 Oct 2010 23:20] Davi Arnaut
Queued to mysql-5.5-bugteam.
[8 Oct 2010 0:05] Bugs System
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/120328

3216 Davi Arnaut	2010-10-07
      Bug#56822: Add a thread state for sessions waiting on the query cache lock
      
      Move Query_cache_wait_state declaration out of a debug block.
[8 Oct 2010 12:16] Bugs System
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/120366

3219 Davi Arnaut	2010-10-08
      Bug#56822: Add a thread state for sessions waiting on the query cache lock
      
      Only wait for a single debug signal at a time as the signal state
      is global. Also, do not activate the query cache debug sync points
      if the thread has no associated THD session.
     @ mysql-test/t/query_cache_debug.test
        Only wait for a single debug signal at a time as the signal state
        is global.
     @ sql/sql_cache.cc
        Do not execute the debug sync point if the thread has no associated
        THD session. This scenario happens for federated threads.
[9 Nov 2010 19:45] Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (merge vers: 5.5.7-rc) (pib:21)
[13 Nov 2010 16:12] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:33] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)
[16 Nov 2010 2:20] Paul DuBois
Noted in 5.5.7 changelog.

Added a new SHOW PROCESSLIST state, Waiting for query cache lock.
This indicates that a session is waiting to take the query cache lock
while it performs some query cache operation.