Jan 11

If you want to get optimized and speedy response from your MySQL server then you need to add following two configurations directive to your MySQL server:

query_cache_size=SIZE
The amount of memory (SIZE) allocated for caching query results. The default value is 0, which disables the query cache.

query_cache_type=OPTION
Set the query cache type. Possible options are as follows:
0 : Don’t cache results in or retrieve results from the query cache.
1 : Cache all query results except for those that begin with SELECT S_NO_CACHE.
2 : Cache results only for queries that begin with SELECT SQL_CACHE

You can setup caching as follows:
$ mysql -u root –p

Output:

Enter password:
mysql>

Now setup cache size 16Mb:

mysql> SET GLOBAL query_cache_size = 16777216;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'query_cache_size';

Output:

+——————+———–+
| Variable_name     | Value         |
+——————+———–+
| query_cache_size| 16777216   |
+——————+———–+  

You can setup them in /etc/my.cnf (Red Hat) or /etc/mysql/my.cnf (Debian) file:
# vi /etc/my.cnf
Append config directives as follows:

query_cache_size = 268435456 query_cache_type=1 query_cache_limit=1048576

In above example the maximum size of individual query results that can be cached set to 1048576 using query_cache_limit system variable. Memory size in Kb.

written by MG


One Response to “Enable the query cache in MySQL to improve performance”

  1. 1. Anvesh Patel Says:

    Nice Article !
    I have already shared this article with my friends.
    I have also worked around this MySQL Query Cache and created my research note on my blog.
    Please visit my blog.
    For basic theory:
    http://www.dbrnd.com/2015/08/mysql-query-cache/
    For basic configuration:
    http://www.dbrnd.com/2015/08/mysql-query-cache-configuration/

Leave a Reply

*