User Tools

Site Tools


mysql_caching

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:


+------------------+-------+
| 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.




Configuring query caching variables

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

set GLOBAL query_cache_size = 16777216;

set global query_cache_limit = 1010101;

Setting the variables to be permanent:

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

query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576



mysql_caching.txt · Last modified: 2024/05/23 07:26 by 127.0.0.1

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