![background image](http://html1.mh-extra.com/html/oracle/5-0/5-0_reference-manual_1646196728.webp)
The MySQL Query Cache
708
cache to prune (delete) queries from the cache due to lack of memory. In this case, you should
decrease the value of
query_cache_min_res_unit
[484]
. The number of free blocks and
queries removed due to pruning are given by the values of the
Qcache_free_blocks
[530]
and
Qcache_lowmem_prunes
[530]
status variables.
• If most of your queries have large results (check the
Qcache_total_blocks
[530]
and
Qcache_queries_in_cache
[530]
status variables), you can increase performance by increasing
query_cache_min_res_unit
[484]
. However, be careful to not make it too large (see the
previous item).
8.6.3.4. Query Cache Status and Maintenance
To check whether the query cache is present in your MySQL server, use the following statement:
mysql>
SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
You can defragment the query cache to better utilize its memory with the
FLUSH QUERY CACHE
statement. The statement does not remove any queries from the cache.
The
RESET QUERY CACHE
statement removes all query results from the query cache. The
FLUSH
TABLES
statement also does this.
To monitor query cache performance, use
SHOW STATUS
to view the cache status variables:
mysql>
SHOW STATUS LIKE 'Qcache%';
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| Qcache_free_blocks | 36 |
| Qcache_free_memory | 138488 |
| Qcache_hits | 79570 |
| Qcache_inserts | 27087 |
| Qcache_lowmem_prunes | 3114 |
| Qcache_not_cached | 22989 |
| Qcache_queries_in_cache | 415 |
| Qcache_total_blocks | 912 |
+-------------------------+--------+
Descriptions of each of these variables are given in
Section 5.1.6, “Server Status Variables”
. Some
uses for them are described here.
The total number of
SELECT
queries is given by this formula:
Com_select
+ Qcache_hits
+ queries with errors found by parser
The
Com_select
value is given by this formula:
Qcache_inserts
+ Qcache_not_cached
+ queries with errors found during the column-privileges check
The query cache uses variable-length blocks, so
Qcache_total_blocks
[530]
and
Qcache_free_blocks
[530]
may indicate query cache memory fragmentation. After
FLUSH QUERY
CACHE
, only a single free block remains.
Every cached query requires a minimum of two blocks (one for the query text and one or more for the
query results). Also, every table that is used by a query requires one block. However, if two or more
queries use the same table, only one table block needs to be allocated.
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...