Databases Reference
In-Depth Information
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 in set (0.00 sec)
Here, caching is available (
have_query_cache
is
YES
), and the query cache size is 399,360
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 |
