User Tools

Site Tools


mysql_caching

This is an old revision of the document!


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)

SHOW VARIABLES LIKE 'query_cache_size'; 

You can also use the following command for more information:
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:

mysql -e 'show variables;' | grep query_cache_size

Output should look similar to:

mysql> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     |
+------------------+-------+


Setting caching size

Important variables that are linked to caching:

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.

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

query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576



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

SET GLOBAL query_cache_size = 16777216;


mysql_caching.1428484351.txt.gz · Last modified: 2024/05/23 07:26 (external edit)

Except where otherwise noted, content on this wiki is licensed under the following license: Public Domain
Public Domain Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki