Learning Mysql

You can configure the size of the server’s query cache by modifying the query_cache_size variable. The larger the cache, the more queries that can be cached.
Like most other buffers, this follows the law of diminishing returns doubling the query cache size is unlikely to double the effectiveness of the cache. You can check the server’s cache settings as follows:
mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 3999744 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
6 rows inset sec)
Here, caching is available (
have_query_cache is YES, and the query cache size is KB. When the query cache size is nonzero, the query_cache_type setting determines which queries should be cached with this set to ON, almost all
SELECT
queries are cached.
There are main two exceptions queries that explicitly disable caching with the
SQL_NO_CACHE
keyword immediately after the SELECT, and queries that use functions that vary with time and user—for example, queries that include the function
CURRENT_TIMESTAMP( The query_cache_limit variable indicates the largest result to store for any given query,
while query_cache_min_res_unit specifies the allocation units in the cache (the default is generally fine. Finally, query_cache_wlock_invalidate determines whether an active write lock granted to one client will prevent other clients from reading cached results.
Of these settings, you will typically only need to ensure that caching is available
(
query_cache_type is ON) and set an appropriate value for query_cache_size
:
mysql> SET query_cache_type = ON;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL query_cache_size = 40000000;
Query OK, 0 rows affected (0.01 sec)
You can then check on how queries are being read from the cache:
mysql> SHOW STATUS LIKE 'qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 39826928 |
| Qcache_hits | 7 |
| Qcache_inserts | 128 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 10 |

Download 4.24 Mb.

Share with your friends: