Bookmark and Share
Query Cache Status and Maintenance
(Publish Date: 2009-8-10 2:58pm, Total Visits: 3969, Today: 1, This Week: 5, This Month: 13)

1. merge query cache fragments by using "flush query cache;"

mysql> show status like 'Qc%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 14508    |
| Qcache_free_memory      | 63018272 |
| Qcache_hits             | 7824117  |
| Qcache_inserts          | 11756404 |
| Qcache_lowmem_prunes    | 5666779  |
| Qcache_not_cached       | 1158723  |
| Qcache_queries_in_cache | 42146    |
| Qcache_total_blocks     | 99409    |
+-------------------------+----------+
8 rows in set (0.06 sec)

mysql> flush query cache;
Query OK, 0 rows affected (0.48 sec)

mysql> show status like 'Qc%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 62487240 |
| Qcache_hits             | 7824944  |
| Qcache_inserts          | 11757047 |
| Qcache_lowmem_prunes    | 5666779  |
| Qcache_not_cached       | 1158800  |
| Qcache_queries_in_cache | 42481    |
| Qcache_total_blocks     | 85570    |
+-------------------------+----------+

2. The RESET QUERY CACHE statement removes all query results from the query cache. The FLUSH TABLES statement also does this.

3. some formulas for query cache

1) query cache hit rate = Qcache_hits / (Qcache_hist+Com_select)

2) The total number of SELECT queries is equal to:

 Com_select
+ Qcache_hits
+ queries with errors found by parser

The Com_select value is equal to:

 Qcache_inserts
+ Qcache_not_cached
+ queries with errors found during columns/rights

3) The average size of the queries = (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache

4. Qcache_free_blocks and Qcache_lowmem_prunes

if Qcache_free_blocks approaches Qcache_total_blocks / 2, your query cache is severely fragmented. If the Qcache_lowmem_prunes status variable is increasing and you have a lot of free blocks, fragmentation is causing queries to be deleted from the cache prematurely. In this case, enlarge query_cache_limit or query_cache_size, or both of them.

5. query_cache_min_res_unit

The query cache allocates blocks with a minimum size given by the query_cache_min_res_unit 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:

    * The default value of query_cache_min_res_unit 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 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. The number of free blocks and queries removed due to pruning are given by the values of the Qcache_free_blocks and Qcache_lowmem_prunes status variables.

    * If most of your queries have large results (check the Qcache_total_blocks and Qcache_queries_in_cache status variables), you can increase performance by increasing query_cache_min_res_unit. However, be careful to not make it too large (see the previous item).


[Total Users: 1]

I want to comment on it

Display: 1 - 0 of 1, Total Pages: 0