query_cache_size is reset periodically without my will

Question

I have a website that receives a lot of searches, so I need a cache.

The VPS is under the plesk license and the OS is Centos.

I have used the following command to implement a cache in mysql several days ago:

SET GLOBAL query_cache_size = 16777216;

The problem is that after some days the query_cache_size is reset automatically:

mysql> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     |
+------------------+-------+
1 row in set (0.00 sec)

This is the file /etc/my.cnf:

[mysqld]
bind-address = 127.0.0.1
local-infile = 0
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
user = mysql

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

query_cache_size = 16777216
query_cache_type = 1
query_cache_limit = 1048576

How can I fix this problem?

Answer 1

In general the query_cache is not very useful. Amongst it's problems are global mutex, eviction of cached results occurs when you update a table (all the cached results for said table, ALL of them).

I would look at investigating what is going on with that instance. It might be that the provider manages it with some config management tool (Chef, Puppet, Saltstack, Ansible) and the MySQL variables are being set to a value that the owner thinks will protect the memory use on the hypervisor.

If you have console access check the Linux processlist for signs of an agent running locally that could point to Chef or Puppet. Depending on your level of experience and the amount of control you have on your machine, it could be useful to set up some audit logging to check who/what is connecting to your instance and altering the config.

https://mariadb.com/kb/en/library/mariadb-audit-plugin-installation/ https://github.com/mcafee/mysql-audit/wiki/Installation

You might be able to brute force it's state by using the event scheduler inside mysql (select your preferred interval).

USE mysql;
CREATE EVENT set_query_cache
ON SCHEDULE EVERY 5 SECOND
ON COMPLETION PRESERVE
DO
   SET GLOBAL query_cache_size = 16777216;

Answer 2

The variable query_cache_size is again set to 0 despite there is the event in mysql every 5 seconds:

select EVENT_DEFINITION,INTERVAL_VALUE from events;
+----------------------------------------+----------------+
| EVENT_DEFINITION                       | INTERVAL_VALUE |
+----------------------------------------+----------------+
| SET GLOBAL query_cache_size = 16777216 | 5              |
+----------------------------------------+----------------+

SHOW VARIABLES LIKE 'query_cache_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     |
+------------------+-------+

Answer 3

You never enabled the event scheduler.

You need to enable it and maybe recreate the event script.

STEP 01 : Edit my.cnf

Please edit these lines in my.cnf

[mysql]
event_scheduler=ON

STEP 02 : Enabled it manually

Login to mysql, and run the following:

mysql> SET GLOBAL event_scheduler=1;
mysql> SET GLOBAL query_cache_size=16777216;

STEP 03 : Logout and Login and Check

Logout of mysql

mysql> exit

Login to mysql

$ mysql -uroot -p

Check the variables

mysql> SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
mysql> SHOW GLOBAL VARIABLES LIKE 'query_cache_size';

If these give you ON (or 1) and 16777216, you are done.

STEP 04 : Drop and recreate the event

Please do not add events to the mysql system database.

Create another database, make it start 10 seconds after event creation

USE mysql;
DROP EVENT IF EXISTS set_query_cache;
CREATE DATABASE mystuff;
USE mystuff
CREATE EVENT set_query_cache
ON SCHEDULE EVERY 5 SECOND
STARTS (NOW() + INTERVAL 10 SECOND)
ON COMPLETION PRESERVE
DO
    SET GLOBAL query_cache_size = 16777216;

GIVE IT A TRY !!!