Caching is always the best choice to improve the performance of website. MySQL caching queries is one of the most important steps.

1. Log in MySQL with root account or any accounts which have granted enough permissions.

2. Check the cache log:  show variables like 'have_query_cache'

3.  We’ll need to check more than one variable, so we may as well do it all at once by checking for the variable query%

Here’s the important items in the list and what they mean:     

query_cache_size – This is the size of the cache in bytes. Setting this value to 0 will effectively disable caching.    

query_cache_type – This value must be ON or 1 for query caching to be enabled by default.    

query_cache_limit – This is the maximum size query (in bytes) that will be cached. 

If the query_cache_size value is set to 0 or you just want to change it, you’ll need to run the following command, keeping in mind that the value is in bytes. For instance, if you wanted to allocate 8MB to the cache we’d use 1024 * 1024 * 8 = 8388608 as the value.

SET GLOBAL query_cache_size = 8388608;SET GLOBAL query_cache_limit = 1048576;SET GLOBAL query_cache_type = 1;

You’ll notice in the stats that I have plenty of free memory left. If your server shows a lot of lowmem prunes, you might need to consider increasing this value, but I wouldn’t spend too much memory on query caching for a web server… you need to leave memory available for apache, php, ruby, or whatever you are using.

Enable in Config File

If you want these changes to survive a reboot or restart of the mysql server, you’ll need to add them into your /etc/mysql/my.cnf configuration file for MySQL. Note that it might be in a different location on your installation.

Open up the file using a text editor in sudo or root mode, and then add these values if they don’t already exist in the file. If they do exist, just uncomment them.

query_cache_size = 268435456query_cache_type=1query_cache_limit=1048576

Comment