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:
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.
