Question Change MariaDB query_cache_size value

[tulipe]

Server operating system version

CentOS 7

Plesk version and microupdate number

Plesk Obsidian Web Pro Edition Version 18.0.41 Update #1

Hello, I want to increase the query_cache_size value.
So I made the request :

plesk db;
SET GLOBAL query_cache_size=16777216; (Query OK, 0 rows affected)
service mariadb restart;

But when I make SHOW VARIABLES LIKE '%query_cache%'; the value is unchanged.
Do you have an idea please?
Thanks in advance

 

[Kaspar]

Using SET GLOBAL to change a variable only changes it for the current MySQL Server process. If the server restarts, changes are dismissed and global variable settings are read from the options file (my.cnf) upon startup.

So you'll have to add (or edit) the option in the my.cnf file.

More info at: How To Optimize MySQL with Query Cache on Ubuntu 18.04 | DigitalOcean

 

[tulipe]

Thx ;-)

 

[trialotto]

Thx ;-)

@tulipe

Be careful when messing about in my.cnf.


Please read up on the config variables and especially their effects upon resource usage, such as memory.

In addition, make sure that you understand the various config files in /etc/mysql/.. and the chronological order in which they are read and/or applied.

Most importantly, be aware of the fact that MySQL optimization is a dynamic process - the optimal config does not exist, in the sense that it has to be adapted to the situation and continuously over time.


As a result, always apply some "good practice" when changing something in the config files :

1 - copy the original x.cnf file to a file with a name like x.cnf.fallback or x.cnf.bak

2 - edit the x.cnf file (only if the backup/fallback file is in place)

3 - make a copy of /var/lib/mysql/ibdata1 (and potentially /var/lib/mysql/ib_logfile0 and /var/lib/mysql/ib_logfile1 - not necessary, but handy) into another directory than /var/lib/mysql : this ibdata1 file can get corrupted when doing some incorrect MySQL config changes....


With respect to your change in the config variable query_cache_size, please note that

4 - a value of 16M is or should already be present - if I am not mistaken, it is the default value and activated (in /etc/mysql/mariadb.conf.d/50-server.cnf)

5 - a config like

query_cache_limit = 2M
query_cache_size = 64M

(in /etc/mysql/mariadb.conf.d/50-server.cnf) is more meaningful, with

- query_cache_size increased to 64M in order to allow more caching,
- query_cache_limit increased to 2M (from default value 1M) in order to allow bigger individual requests to be cached (otherwise, changing query_cache_size could have a severe impact on your MySQL server)

and, again, please note that your server needs to be checked : server resources should not be overused when changing MySQL config,

6 - changing any (!) part of MySQL config also could require changes to other parts of the MySQL config,

7 - in terms of performance, gains can be achieved by having a look at

innodb_buffer_pool_size
innodb_buffer_pool_instances

config variables : please be careful when setting these config variables differently ....... it can cause server resource overusage,

8 - again, please read and understand the full concept of MySQL config variables and MySQL config optimization, BEFORE changing anything!


I hope the above helps a bit.....

Kind regards....