Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
What version of MySQL Server? (And, how large is your query cache?) –  Michael - sqlbot Oct 28 '13 at 18:09
    
@Michael-sqlbot Added your requests to the questions. Thx! –  Noam Oct 28 '13 at 18:44
add comment

1 Answer 1

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

http://www.mysqlperformanceblog.com/2007/03/23/beware-large-query_cache-sizes/

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.

share|improve this answer
    
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 '13 at 9:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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