Changing vhost mysql settings

[TeunieP]

Hi, I have changed the value query_cache_size = 64M in \etc\my.cnf (previous value was 32M)
After that I have restarted Plesk (service psa restart).
I notice Plesk admin panel runs much smoother.

However when I look in one of my vhost/clients phpmyadmin variables this still shows query_cache_size = 32M.

How can I change the mysql query cache size for my clients? (not serverwide)

 

[Faris Raouf]

You cannot have per-vhost settings for MySQL as far as I'm aware.

Restarting plesk doesn't usually restart MySQL unless you use "service psa stopall" (followed by "service psa start").
It would be better to simply restart MySQL with "service mysqld restart". This will cause any changes you make to my.cnf to come into effect.


To better optimize MySQL settings to match your environment, try using mysqltuner (available as an rpm from certain repos).
Don't go overboard with changes though - if you give MySQL all the memory it could potentially use, you can end up using more than is available. You basically need a balance. So just take a look at what it says to start with, and make small changes. This is actually quite a complex subject, and you can potentially do more harm than good if you aren't careful.

Note that you can use the MySQL command "SHOW VARIABLES;" (or "SHOW VARIABLES LIKE '%size%';") to see what mysqld is currently set to use.

 

[TeunieP]

Thanks for your reply.
I have tried restarting just mysqld and also psa stop all but to no avail. It still shows cache size 32M in Phpmyadmin variables.
query_cache_size 33554432

So there must be a second location where this value is set...
I have no clue where to find it...

 

[TeunieP]

After complete server reboot query_cache_size shows 64M also for clients.
Strange that this setting is not applied with mysqld or PSA restart...

So reboot is required.
Solved!