I have configured mariadb with following config:
query_cache_size = 0
query_cache_type = 0
But still mariadb hangs after a while with CPU 100%. I checked processlist and I get 'Waiting for query cache lock`' for 20+ processes inside mysql cli. All of them waiting for more than thousands of seconds. I have seen this so many times now. (To add to this, we are a shared hosting provider with many users accessing the database at the same time).
I also notice that there is always one process with
Query | 6301 | closing tables | DROP DATABASE `vafagimone5854_V2JewLtWqhsB7inA1cO3`
Is this a clue?
Also, when I ran SHOW VARIABLES LIKE 'have_query_cache'; :
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
and SHOW VARIABLES LIKE 'query_cache_size';:
+------------------+----------+
| Variable_name | Value |
+------------------+----------+
| query_cache_size | 16777216 |
+------------------+----------+
This is super weird as I have the cache disabled. Eventually, I killed mariadbd and restarted the server process, which is obviously not ideal.
But to my surprise the variables are ok now:
SHOW VARIABLES LIKE 'query_cache_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| query_cache_size | 0 |
+------------------+-------+
However this still shows cache is enabled
SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
I am super confused, is it possible someone is query_cache_size in runtime? and why is have_query_cache always 'YES'.