Enabling Query Caching

There are two options for viewing the query caching on a server:

Option 1

When logged into mysql you can use the following command to view query cache size (note: 0 means that no caching has been enabled)

1
show VARIABLES LIKE 'query_cache_size';

You can also use the following command for more information:
1
2
3
4
5
6
7
8
9
10
11
12
mysql> show variables like 'query%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_alloc_block_size       | 8192    |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1239040 |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
| query_prealloc_size          | 8192    |
+------------------------------+---------+

Option 2

If you have configured your .my.cnf file then you are able to perform the following command from your shell without needing to enter into mysql:

1
mysql -e 'show variables;' | grep query_cache_size

Output should look similar to:

1
2
3
4
5
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     |
+------------------+-------+

Setting caching size

Important variables that are linked to caching:

1
2
3
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.



Configuring query caching variables

The command below is an example of setting the global caching of mysql to 16MB.

1
set GLOBAL query_cache_size = 16777216;

1
set global query_cache_limit = 1010101;

Setting the variables to be permanent:

An example of editing the /etc/my.cnf file is:

1
2
3
query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576

1
 

1