Bug #21487 Handle Leak on mysqld-nt.exe when using query cache
Submitted: 7 Aug 2006 18:15 Modified: 22 Sep 2006 9:23
Reporter: Roland Volkmann Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.24,4.1.21,4.0.26 OS:Windows (windows)
Assigned to: CPU Architecture:Any
Tags: Event, Handle, Leak, query cache, Semaphore

[7 Aug 2006 18:15] Roland Volkmann
Description:
Current Version 5.0.24 of MySQL Server Engine (mysqld-nt.exe) doesn't release all allocated handles, so number of open Handles rises. I use INNODB Database with 103 tables.

When checking details with Process Explorer from Sysinternals, most of the open Handles are of type "Event" and "Semaphore".

I stopped Engine and downgraded to version 5.0.22 after 2 hours with 25 active threads, where number of open handles reached 19000.

To get an impression of work load, here the numbers out of INNODB Status after 6 hours (after downgrade to version 5.0.22):
Number of rows inserted 1404932, updated 553, deleted 3257, read 1057697048

How to repeat:
Running MySQL-Server 5.0.24 with some active TCP/IP-Connections, using select, update and insert statements. Use Task Manager of Windows 2003 Server or Process Explorer from Sysinternals and watch Handle-count of mysqld-nt.exe.
[8 Aug 2006 0:07] Roland Volkmann
One more Info:

The handle leak is shown only if query cache is turned on (I use query_cache_size=16M). As soon as query_cache_size=0 (query cache turned off), no rising of open handles can be seen.
[8 Aug 2006 0:28] MySQL Verification Team
Thank you for the bug report. Could you please describe your hardware and
when you notice the number of handles open you reported issue the
show engine innodb status\G command and print here its output, you can
use the  'Hide comment from public' option if needed.

Thanks in advance.
[8 Aug 2006 15:44] Roland Volkmann
Status Values of Version 5.0.22

Attachment: Innodb_5022.TXT (text/plain), 25.71 KiB.

[8 Aug 2006 15:53] Roland Volkmann
Thank you for Reply.

Here the Data of my System:

IBM eServer xSeries 225
2 x Xeon 2,8 GHz (HT on)
2048 MB ECC-RAM
IBM ServeRAID-5i
with 3 x IBM-ESXS MAP3735M as RAID5
Windows 2003 Server Standard Edition, SP1
all available Fixes installed.

Please find attached the file "Innodb_5022.TXT" with the requested status info. It's from the currently running version 5.0.22, because I downgraded yesterday.

On preparing this post, I found handle count also rising on version 5.0.22, but not as fast as with version 5.0.24.

Because it's a production server, I can upgrade again only this evening after regular office hours. So I will send the status data of version 5.0.24 tmorrow after one day activity with version 5.0.24.

With best regards,
Roland.
[10 Aug 2006 21:16] Roland Volkmann
Status of INNODB Engine 5.0.24

Attachment: Innodb_5024.TXT (text/plain), 15.97 KiB.

[10 Aug 2006 21:28] Roland Volkmann
Please find attached the status of INNODB Engine 5.0.24 after two days of regular work. The number of open handles is rising over the day, and sometimes it falls down to around 5000. There seems to be a correlation of open handles to inserted queries in the query cache, as long as the number is rising. But when lots of open handles are closed within very short time, there is no relevant change of query cache statistics, and no special queries are sent from users.

I started some analysis on a test machine running Windows XP Prof., where the same effects can be seen.
[17 Aug 2006 1:27] Louis Solomon
I've noticed recently (and haven't had time yet to explore), that 4.1.9 mysql-max-nt.exe also has crazy amounts of hanldes (like 15,000), and that's when it's not doing much!
[31 Aug 2006 6:19] Edward Kung
I'm also experiencing this.
Win2k3 Standard on Opteron 2x246
Running MySQL 4.1.21 and MySQL 5.0.24, both show thousands of handles (15000-20000 each)
[12 Sep 2006 12:07] MySQL Verification Team
run "mysqld-nt.exe --query_cache_size=64M --query_cache_type" and it opened > 69000 handles on 5.0.24a

Attachment: bug21487_testcase.php (application/octet-stream, text), 920 bytes.

[12 Sep 2006 12:10] MySQL Verification Team
Seems a semaphore is created for each Qcache_total_blocks.
SHOW GLOBAL STATUS LIKE 'Qcache_total_blocks' should how many semaphores the qcache is using (approximately).

RESET QUERY CACHE will immediately reset it, and handle count will drop again:

>handle.exe -s -p 1452

Handle v3.10
Copyright (C) 1997-2005 Mark Russinovich
Sysinternals - www.sysinternals.com

Handle type summary:
  Desktop         : 1
  Directory       : 3
  Event           : 4291
  File            : 33
  IoCompletion    : 4
  Key             : 12
  Mutant          : 28
  Port            : 1
  Section         : 2
  Semaphore       : 65588
  Thread          : 18
  WindowStation   : 2

mysql> show global status like 'qc%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 33545024 |
| Qcache_hits             | 742076   |
| Qcache_inserts          | 98308    |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 25       |
| Qcache_queries_in_cache | 32768    |
| Qcache_total_blocks     | 65538    |
+-------------------------+----------+
8 rows in set (0.00 sec)
[12 Sep 2006 12:30] MySQL Verification Team
This looks like a design decision of Query Cache to me.  I see no real leak, since RESET QUERY CACHE removes the semaphores.  Will ask Sanja to comment.
[22 Sep 2006 9:23] MySQL Verification Team
setting to not a bug. the handles aren't "leaked", they are accounted for in the query cache.
[14 Mar 2008 13:47] Alexc Franz
It is really a bug- i have handlecount ~40000 with querycache disabled.....
Even if i reinstall it without the databases. It is still tge same Handlecount...(5.0.45) it is even growing!!!!What can i do?
[14 Mar 2008 13:59] MySQL Verification Team
Please if handle count and innodb_buffer_pool_size are related.  Still, unless you see a steady and indefinite increase in handles, this isn't a "leak".
These aren't file handles, so I'm not sure what bad side effects you're seeing?
[14 Mar 2008 14:00] MySQL Verification Team
You may check what type of handles are being used by using the handles.exe program: http://technet.microsoft.com/en-us/sysinternals/bb896655.aspx
[11 May 2008 16:39] Peter Belm
innodb_buffer_pool_size and the handle count are definitely related.
With an innodb_buffer_pool_size of 8MB and above I get over 30,000 handles being used, with 7MB only 4,000 are used.
[4 Nov 2011 8:57] Jason Garrett
FYI: related comments added to Bug#45252 (http://bugs.mysql.com/bug.php?id=45252)