I was reading my hoster’s member forums recently and one topic there mentioned that normally the default installation of MySql database on our virtual servers doesn’t have cache enabled. As I didn’t look deep into those things yet, and on my home PC I just take the defaults and don’t care, I did a quick search on the Internet, which took me to the standard MySql documentation and a short article explaining the basics. Then, as suspected, the checks of MySql settings on the Kyusl.com server has shown that the cache is really not enabled. It looks like the hosting company provides a “minimum” install, assuming that folks who purchase virtual server hosting with almost root access will do the tuning on their own.
The whole thing doesn’t take long. I just had to ssh to my server and add three lines to /etc/my.cnf (it’s on RedHat, on Debian-based systems like Ubuntu it should be /etc/mysql/my.cnf). They have to be placed somewhere under [mysqld] section:
query_cache_limit = 1048576
query_cache_size = 16777216
query_cache_type = 1
After that, you need either restart MySql daemon (mysqld stop then mysqld start) or, if you don’t mind, the PC or VPS server. When you log into the mysql client after restart, do a quick check if your changes are active now:
mysql> SHOW VARIABLES LIKE ‘query_cache%’;
NOTE: both quotes in the statement above should be the same “normal” single quote. Don’t copy-paste – this won’t work. Just type it yourself.
The result should report something like:
+------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 16777216 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+----------+
By the way, the default installation that you get with Ubuntu Linux has the cache enabled by default.
