MySQL Bugs: #21487: Handle Leak on mysqld-nt.exe when using query cache

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

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.

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.

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.

Status Values of Version 5.0.22

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

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.

Status of INNODB Engine 5.0.24

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

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.

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!

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)

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.

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)

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.

setting to not a bug. the handles aren't "leaked", they are accounted for in the query cache.

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?

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?

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

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.

FYI: related comments added to Bug#45252 (http://bugs.mysql.com/bug.php?id=45252)