MySQL Bugs: #24474: mysqld-nt.exe seems to leak on query-cache-size=0 and query-cache-type=1

Bug #24474	mysqld-nt.exe seems to leak on query-cache-size=0 and query-cache-type=1
Submitted:	21 Nov 2006 14:28	Modified:	29 Mar 2007 19:54
Reporter:	Yahoo Serious (Silver Quality Contributor)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.21-community-nt	OS:	Windows (Windows 2000 SP4)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any
Tags:	qc

Description:
When query-cache-size is 0, but query-cache-type is ON (1), mysqld-nt.exe seems to leak.

I am trying to locate a memory leak at a customer machine.  Trying to reproduce it I am running it on my desktop machine.  Both have MySQL 4.1.21-community-nt (service) and MySQL ODBC 3.51 Driver DSN.  My Delphi application does not seem to leak (according to AQtime and Task Manager).

mysqld-nt.exe does seem to leak in Memory Usage according to Task Manager (Virtual Memory was practically constant).  Since the increase in Memory Usage seemed to reduce every run (starting at 5 MB, then 2.5, 1.7, etc), I thought this might be the query-cache (which was pruning too much on my small system anyway).  

So I set query-cache-size to 0 to disable it.  This made the increase in Memory Usage more consistent, increasing about 2 MB every run.  
(If I disconnect all connections, including other applications like Query Browser and Administrator, nothing changes in Memory Usage.  It is not released again.  However, I do not know anything about possible connection pooling.)

If I also set query-cache-type to 0 (OFF), the Memory Usage is practically constant (no increase).

(I also checked Available Physical Memory in the Task Manager, which seemed to confirm.  However on a desktop machine other stuff will also use that...)

How to repeat:
Leave query-cache-type=1.
Set query-cache-size=0.
Note the Memory Usage according to Task Manager.
Run a lot of pretty big queries.
 (I query all street names of 1.000 randomly selected towns, with a couple of associated text and integer fields from the same table).
Note the increase in Memory Usage according to Task Manager.

(Repeat N times)

Suggested fix:
Locate the problem and fix it :-)

Thank you for the bug report. Could you please when the task manager shows
the MySQL server process increasing its memory usage, issue and copy here
the output of:

SHOW STATUS LIKE 'Qcache%';

RESET QUERY CACHE;

SHOW STATUS LIKE 'Qcache%';

Thanks in advance.

All values are 0.

In the first test SHOW STATUS did reduce the Memory Usage once (not the whole 'leak'-amount though), but I could not reproduce that.  Also RESET in the first test run did freeze the leak, but I could only reproduce that once (so I am afraid I forgot to reset my random-seed).  

If I use the same random-seed for consecutive runs, the Memory Usage does not increase, which may imply (hidden) usage of the query cache?!

The queries I use are:
1.  One query to randomly select some placenames
  SELECT * FROM
    (SELECT DISTINCT PlaceNameId 
     FROM runtimestreetphrases 
     GROUP BY PlaceNameId) AS PlaceNameIds
  ORDER BY RAND(7) LIMIT 300;
2.  N times a query to select all streets per place
  SELECT * FROM runtimestreetphrases 
  WHERE PlaceNameId = <PlaceNameId>
  ORDER BY ConceptValue

Thank you for the bug report.

I think there also is/was a leak in the query cache when it is on (query-cache-size<>0).  I am/was hoping that looking at this simpler reported situation (query-cache-size=0) would lead to a fix for both problems.  

Since you managed to verify the leak for query-cache-size=0, but this does not seem to get fixed yet, maybe you can also reliably (consistently reproducable) verify it for query-cache-size<>0?  That looks like an even more serious bug to me.  

Although I realize this is an old version, I hope you will realize that it is often easier to upgrade to a new minor version than to a new major version (often technically, but mostly 'politically').  So I think (hope) that will be worth the effort of fixing the bug.

I see no real evidence of a leak here. If there were a leak, we'd see VM growth.

We'll reopen if we find more evidence.

I am puzzled:
 - there should not be any caching, so memory-usage should not change
 - it was verified, 
 - now you see no memory-usage change?
Then, how did one verify without memory-usage-change?!

Anyway, in my live situation, it leaks with query-cache-size 0 and query-cache-type ON (as reported), it does not leak with query-cache-size 0and query-cache-type OFF (as reported), so I'll stick to my work-around and hope you can unpuzzle me, and hopefully fix this bug.