How can I calculate the total time my MySQL database is spending Waiting for query cache lock ?

I've found two specific very simple tables that adding a SQL_NO_CACHE drastically improved the SELECT queries on them, and I want to analyze other tables in a more methodological approach.

My MySQL version is 5.5.15 and the InnoDB engine is 1.1.8
Query cache size is 536870912

Relevant Variables: enter image description here

  • What version of MySQL Server? (And, how large is your query cache?) Oct 28, 2013 at 18:09
  • @Michael-sqlbot Added your requests to the questions. Thx!
    – Noam
    Oct 28, 2013 at 18:44

1 Answer 1


Half a gigabyte is too large for the query cache. This write-up is several years old but still very applicable:


In MySQL 5.5., you can benchmark this using profiling but the real problem isn't how long any particular query waits for the lock, because the answers will generally be all across the board and largely unrelated to the query itself or the tables, because the query cache is accessed by a global mutex that every query contends for.

There is apparently supposed to be a timeout to prevent long waits, but which does not work correctly, according to Bug #68573.

Your fix would be a more reasonable query cache.

I dissected and described a little bit of the query cache's inner workings here.

  • So first thing you'd advise is lowering the size. To how much? Also, I've added other variables to my question, if those might seem off to you as well.
    – Noam
    Oct 29, 2013 at 9:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.