The MySQL Query Cache
707
new query cache size is 0
mysql>
SET GLOBAL query_cache_size = 41984;
Query OK, 0 rows affected (0.00 sec)
mysql>
SHOW VARIABLES LIKE 'query_cache_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| query_cache_size | 41984 |
+------------------+-------+
For the query cache to actually be able to hold any query results, its size must be set larger:
mysql>
SET GLOBAL query_cache_size = 1000000;
Query OK, 0 rows affected (0.04 sec)
mysql>
SHOW VARIABLES LIKE 'query_cache_size';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| query_cache_size | 999424 |
+------------------+--------+
1 row in set (0.00 sec)
The
query_cache_size
[485]
value is aligned to the nearest 1024 byte block. The value reported
may therefore be different from the value that you assign.
If the query cache size is greater than 0, the
query_cache_type
[485]
variable influences how it
works. This variable can be set to the following values:
• A value of
0
or
OFF
prevents caching or retrieval of cached results.
• A value of
1
or
ON
enables caching except of those statements that begin with
SELECT
SQL_NO_CACHE
.
• A value of
2
or
DEMAND
causes caching of only those statements that begin with
SELECT
SQL_CACHE
.
Setting the
GLOBAL
query_cache_type
[485]
value determines query cache behavior for all clients
that connect after the change is made. Individual clients can control cache behavior for their own
connection by setting the
SESSION
query_cache_type
[485]
value. For example, a client can
disable use of the query cache for its own queries like this:
mysql>
SET SESSION query_cache_type = OFF;
If you set
query_cache_type
[485]
at server startup (rather than at runtime with a
SET
statement),
only the numeric values are permitted.
To control the maximum size of individual query results that can be cached, set the
query_cache_limit
[484]
system variable. The default value is 1MB.
When a query is to be cached, its result (the data sent to the client) is stored in the query cache
during result retrieval. Therefore the data usually is not handled in one big chunk. The query cache
allocates blocks for storing this data on demand, so when one block is filled, a new block is allocated.
Because memory allocation operation is costly (timewise), the query cache allocates blocks with a
minimum size given by the
query_cache_min_res_unit
[484]
system variable. When a query
is executed, the last result block is trimmed to the actual data size so that unused memory is freed.
Depending on the types of queries your server executes, you might find it helpful to tune the value of
query_cache_min_res_unit
[484]
:
• The default value of
query_cache_min_res_unit
[484]
is 4KB. This should be adequate for most
cases.
• If you have a lot of queries with small results, the default block size may lead to memory
fragmentation, as indicated by a large number of free blocks. Fragmentation can force the query
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
