Differences

This shows you the differences between two versions of the page.

--- mysql_caching [2015/04/08 07:44] – created luke7858
+++ mysql_caching [2024/05/23 07:26] (current) – external edit 127.0.0.1
@@ Line 4: / Line 4: @@
 When logged into mysql you can use the following command to view query cache size (note: 0 means that no caching has been enabled)
 <sxh bash>
-SHOW VARIABLES LIKE 'query_cache_size';
+show VARIABLES LIKE 'query_cache_size';
+</sxh>
+\\
+You can also use the following command for more information:
+<sxh bash>
+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    |
++------------------------------+---------+
 </sxh>
 \\
 === 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:
+<sxh bash>
+mysql -e 'show variables;' | grep query_cache_size
+</sxh>
 Output should look similar to:
 <sxh bash>
-mysql> SHOW VARIABLES LIKE 'query_cache_size';
 +------------------+-------+
 | Variable_name    | Value |
@@ Line 20: / Line 39: @@
 </sxh>
 \\
+--------------------------------------------------------------
 === Setting caching size ===
-The command below is an example of setting the global caching of mysql to 16MB.
+===Important variables that are linked to caching:===
 <sxh bash>
-SET GLOBAL query_cache_size = 16777216;
+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.
 </sxh>
+\\
+----------------------------------------------------------
+\\
+=== Configuring query caching variables ===
+The command below is an example of setting the global caching of mysql to 16MB.
 <sxh bash>
+set GLOBAL query_cache_size = 16777216;
 </sxh>
 <sxh bash>
+set global query_cache_limit = 1010101;
 </sxh>
+\\
+=== Setting the variables to be permanent: ===
 An example of editing the **/etc/my.cnf** file is:
@@ Line 41: / Line 69: @@
 query_cache_type=1
 query_cache_limit=1048576
+</sxh>
+<sxh bash>
+</sxh>
+<sxh bash>
 </sxh>