Making query cache contention more obvious
The newest release of Percona Server includes a trivial change that I think will be extremely valuable. This is the addition of a new thread state, “Waiting on query cache mutex.” Fixing the query cache to make it scalable is hard. Fixing the server to report when the query cache is a bottleneck is not hard. It has historically been very difficult for users to diagnose what’s wrong with their server when the query cache is locking it intermittently. Now it will be trivial: they will look at SHOW PROCESSLIST and the evidence will be unmistakable.
I just prefer to configure and build without query cache.
Antony Curtis
16 Sep 10 at 1:15 am
Baron, this is good news. It has always been hard to prove that the query cache makes for trouble.
However, even better would be a status variable like “Qcache_lock_waits”, similar to table locks or innodb locks. This ways it would be easier to see+measure the contention on the qcache. Catching it on the processlist is more difficult if you do sample monitoring.
Shlomi Noach
16 Sep 10 at 1:21 am
PERFORMANCE_SCHEMA will show the number of query cache mutex waits, the amount of time waiting for the mutex, and whether any sessions are currently waiting on it.. :)
Mark Leith
17 Sep 10 at 2:21 am
Good!
Shlomi Noach
17 Sep 10 at 5:08 am