Hi
The query cache uses variable-length blocks and the Qcache_total_blocks and
Qcache_free_blocks may indicate query cache memory fragmentation. After
FLUSH QUERY CACHE, only a single free block remains. So the variables
query_cache_min_res_unit, query_cache_limit, query_prealloc_size,
query_alloc_block_size determines the free and available number of blocks.
Every cached query requires a minimum of two blocks - for query text and 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 block
needs to be allocated.
The information provided by the Qcache_lowmem_prunes status variable can
help you tune the query cache size. It counts the number of queries that
have been removed from the cache to free up memory for caching new queries.
The query cache uses a least recently used (LRU) strategy to decide which
queries to remove from the cache.
If you have query with the size larger than the query_cache_size then the
query is not cached. 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.
Thanks
ViSolve DB Team
----- Original Message -----
From: "Christian Hammers" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Friday, September 01, 2006 1:52 PM
Subject: Understanding Query-Cache math...
Hello
I don't understand why
query_cache_size / query_cache_min_res_unit != Qcache_total_blocks and
Qcache_free_memory / query_cache_min_res_unit != Qcache_free_blocks
Can anybody enlight me so that I know if I have to increase the
Query-Cache or not?
mysql> SHOW status LIKE "Qcache_%";
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 10382 |
| Qcache_free_memory | 247491776 |
| Qcache_hits | 119254865 |
| Qcache_inserts | 5412923 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 58724815 |
| Qcache_queries_in_cache | 16002 |
| Qcache_total_blocks | 42464 |
+-------------------------+-----------+
mysql> SHOW variables LIKE "query_cache_%";
+------------------------------+------------+
| Variable_name | Value |
+------------------------------+------------+
| query_cache_limit | 8388608 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 268435456 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+------------+
thanks,
-christian-
--
Christian Hammers WESTEND GmbH | Internet-Business-Provider
Technik CISCO Systems Partner - Authorized Reseller
Lütticher Straße 10 Tel 0241/701333-11
[EMAIL PROTECTED] D-52064 Aachen Fax 0241/911879
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
