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?
Asked by dr_
(1334 rep)
Dec 9, 2016, 08:10 AM
Last activity: Nov 13, 2023, 09:45 PM
Last activity: Nov 13, 2023, 09:45 PM