MySQL Bugs: #44359: Query cache gets fragmented when the query cache gets full

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:	None
Category:	MySQL Server: Query Cache	Severity:	S2 (Serious)
Version:	5.1.33	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Fragmentation, query cache

Description:
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.

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.

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".