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

[21 Nov 2006 14:28] Yahoo Serious
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 :-)
[23 Nov 2006 18:03] MySQL Verification Team
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.
[24 Nov 2006 12:22] Yahoo Serious
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
[16 Jan 2007 0:50] MySQL Verification Team
Thank you for the bug report.
[2 Feb 2007 10:47] Yahoo Serious
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.
[23 Mar 2007 18:20] Damien Katz
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.
[29 Mar 2007 19:54] Yahoo Serious
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.