Bug #44359 Query cache gets fragmented when the query cache gets full
Submitted: 20 Apr 2009 6:44 Modified: 14 Sep 2009 12:50
Reporter: Rajarshi Chowdhury Email Updates:
Status: No Feedback Impact on me:
Category:MySQL Server: Query Cache Severity:S2 (Serious)
Version:5.1.33 OS:Any
Assigned to: CPU Architecture:Any
Tags: Fragmentation, query cache

[20 Apr 2009 6:44] Rajarshi Chowdhury
Query cache gets fragmented when the query cache gets full (and sometimes if there is space left also!). In one of my tests with 500MB QC, after about 3 minutes of sysbench read-only run there was only 800KB space left to allocate to insert new queries. There were no Qcache_lowmem_prunes, so upto this point the number of queries inserted and number of queries present in the cache was same (475612 in this case). After this (as free space ran out) Qcache_lowmem_prunes started happening and within a few seconds Qcache_free_blocks=26286, Qcache_free_memory=60 MB, Qcache_inserts=496232, Qcache_lowmem_prunes=77721, Qcache_queries_in_cache=418511. There are free blocks and enough memory, but still lowmem_prunes are being triggered. If the query cache size is 50 MB, after 15 minutes of run almost all of the memory is fragmented and there is practially no use of Query cache.

How to repeat:
Run a sysbench OLTP read only test for a few minutes using special distribution and continuously monitor variables like %qcache%.

Suggested fix:
The command FLUSH QUERY CACHE packs the query cache. May be we can use this or some variation of this when the fragmentation reaches a certain point.
[14 Aug 2009 12:50] Sveta Smirnova
Thank you for the feedback.

Fragmented query cache is not a bug itself. But if it is fragmenting abnormally this can be considered as a bug.

Please provide output of "show variables like 'query_cache%';" so we can use for tests same settings a you use.
[14 Sep 2009 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".