Server Administration & Management

Turbo charging MySQL by setting up the query cache

To make sure MySQL uses the query cache, there are a few variables you need to set in the configuration file . Usually its my.cnf or my.ini file, so check on the server with the shell command locate , in our case, its my.cnf located in /etc so we opened it by using vi /etc/my.cnf

First, is the query_cache_type. There are three possible settings:

0 (for off, do not use),
1 (for on, cache queries) and
2 (on demand).

To ensure it is always on, place: query-cache-type = 1

in the configuration file.

The query_cache_type is set to ON now. However, there is one more to set, and that is the query_cache_size. If set to 0 (the default), the cache will be disabled. This variable determines the memory, in bytes, used for the query cache. For our purposes, we will set it to 20 MB:

query-cache-size = 20M

if you want to check out whats happening in the MySQL cache, just run this command :-

# mysql -e “SHOW STATUS LIKE ‘%qcache%’;”?


January 14, 2011 Posted by | MySQL | | 3 Comments