1

I'm managing a e-commerce site which uses a popular online shopping cart software running on MySQL 5.6. Yesterday I noticed that SHOW PROCESSLIST reports that 990 of 1000 queries are waiting for a query cache lock:

mysql> show processlist;
+----------+------------+---------------------+-------------+---------+------+--------------------------------+------------------------------------------------------------------------------------------------------+
| Id       | User       | Host                | db          | Command | Time | State                          | Info                                                                                                 |
+----------+------------+---------------------+-------------+---------+------+--------------------------------+------------------------------------------------------------------------------------------------------+
| 12224065 | sqluser    | 10.13.13.13:21716   | mydatabase  | Query   |    0 | Waiting for query cache lock   | SELECT `data` FROM mytable WHERE `foo` = 'bar'                                                       |
(...)

However, the Time is always 0 and the process Ids change all the time. My understanding is that the query waits for a table lock but the lock is released after less than one second.

Is this a normal/acceptable behaviour or could it be worth to do some fine tuning on the query cache, perhaps removing it completely?

2
  • what size of query cache? (personally - I prefer disable it)
    – a_vlad
    Commented Dec 9, 2016 at 9:52
  • About 64 Mb, for a 25 Gb database (data+indexes).
    – dr_
    Commented Dec 9, 2016 at 10:07

1 Answer 1

2

This is a very clear sign that your MySQL query cache is doing more harm than good on your workload. There is a serialisation bottleneck where whenever any table is written to, if there is a query that touched that table cached in the query cache, the query cache has to be locked and purged of any cached queries that refer to those tables. In many cases that does more harm than good.

Query cache only provides tangible benefit on databases where writes are few and infrequent.

Disclosure: I wrote the referenced article.

0

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.