CODEWITHSUNDEEP
mariadb
Vikas Singhal
Vikas Singhal

Reputation: 836

mariadb going to 100% CPU usage even with query_cache_size = 0

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

Upvotes: 0

Views: 2777

Answers (1)

danblack
danblack

Reputation: 14666

have_query_cache is a compile time enabled and can't be changed. See also the query cache kb page.

It looks like you changed your configuration file to have query_cache_size=0 but then it wasn't until you just restarted it that it had an effect. Now that its restarted hopefully the performance is better.

A long time "closing tables" on dropping a database is odd unless there is a very large number of table and a table cache shortage (which is worth checking).

Upvotes: 1

Related Questions