List:MySQL on Win32« Previous MessageNext Message »
From:Geoffroy Cogniaux Date:June 2 2007 10:47pm
Subject:RE: MySQL 5.0 creates 100,000 Window handles
View as plain text  
	
You left in extreme tests. I would like to be able to answer that if it were
so problematic, it would have gone up for a long time in a list of the known
limits. But I do not know.

It seems that if you're really falling in this extreme case (many many very
small different queries in a big cache), regularly flushing query cache
appears to be a good maintenance plan.

It let me want to test what happens with a very big InnoDB table used in a
complex transaction such as deep update.

In any case, if you want to change your simple XP workstation into requested
server, it will be heroic.

Geoffroy

-----Message d'origine-----
De : mos [mailto:mos99@stripped] 
Envoyé : samedi 2 juin 2007 23:49
À : mysql@stripped
Cc : win32@stripped
Objet : RE: MySQL 5.0 creates 100,000 Window handles

Geoffroy,
       Thanks for the reply. Of course by now I figured out what's causing 
the large # of handles being allocated by Windoze XP.  It's the query 
cache. Each query that gets added to the query cache uses 2 handles. If the 
query cache is large, say 150MB, as the cache fills up more and more 
handles are allocated. I was able to create a test program and generated 
hundreds of thousands of simple queries that returned a small # of rows 
from 1 large table (there were no cache hits because I wanted to fill the 
query cache up with as many unique queries as possible). I let it run 
overnight and in the morning Windows had allocated over 600,000 handles! It 
would have been more but the query cache was full  by then. If I were to 
increase the query_cache_size then I'm sure I could get XP to allocate over 
1 million handles. Flushing the query cache of course releases the handles. 
I'm not sure in the same thing happens in Linux or not.  Does it? Should I 
be worried running MySQL on an XP box that has to run 24/7?

Mike

At 02:20 PM 6/2/2007, Geoffroy Cogniaux wrote:
>Hi,
>
>In fact, this is a good question. Therefore, I gonna try to answer.
>
> > 1) What is MySQL using the handles for?
>Handles are nothing and all on Windows. Nothing because they are only
>pointers to hidden internal struct. And all, because, Handle are everywhere
>if you try to develop Win32 App.
>There are 3 kinds:
>Users: Window, Cursor, Menu,...
>GDI: all graphic objects such as Brush, Pen,...
>Kernel: Access token (ACL), Console input, Event, File, Heap, Mutex, Pipe,
>Process, Semaphore, Socket, Thread, Timer, ...
>A database server is therefore a great consumer of kernel Handles.
>
> > 4) How does a MySQL server handle millions of queries on a large table?
> > Will it hit an upper limit of Handles that it can allocate?
>The per-process theoretical limit on kernel handles is 2^24. However,
>handles are stored in the paged pool (kernel reserved memory), so the
actual
>number of handles you can create is based on available memory. So, the
>number of handles that you can create on 32-bit Windows is significantly
>lower than 2^24. Example on Windows2000, max pool size is 300 MB (I don't
>know on most recent windows versions).
>But be sure MySQL server can handle millions of queries on a large table.
>
> > 2) Is there any way to stop MySQL from consuming so many Windows
> > resources?
>I investigated and can summarize with a simple test I made with a 5.0.37
>compiled with all storage engines (and verified with a 4.1.21): MySQL
>allocates 43000 handles. I recompiled it without InnoDB and BDB, and MySQL
>allocates now 108 handles at startup. I'm not an "indeep" Innodb's
>specialist neither BDB, but I know they have row locking mechanism in
>difference of MyIsam but I'm sure that they are great consumers of Mutex.
>
> > Windows of course runs slow with this many handles allocated.
>Yes, not because of number of handles (logical resources) but because of
>physical resources, especially RAM, and perhaps by-design in OS kernel.
>
>
>Regards,
>Geoffroy
>
>-----Message d'origine-----
>De : Geoffroy Cogniaux [mailto:geoffroy.cogniaux@stripped]
>Envoyé : dimanche 27 mai 2007 23:13
>À : 'mos'; mysql@stripped
>Objet : RE: MySQL 5.0 creates 100,000 Window handles
>
>Hi,
>
>Try win32@stripped instead.
>
>Bye.
>Geof.
>
>-----Message d'origine-----
>De : mos [mailto:mos99@stripped]
>Envoyé : vendredi 25 mai 2007 06:41
>À : mysql@stripped
>Objet : MySQL 5.0 creates 100,000 Window handles
>
>I noticed if my program executes a lot of Select statements, Windows XP
>will slow down when the program completes. I did some investigating and
>mysqld-nt.exe has close to 100,000 handles created when my program ends
>(shown in Task Manager and SysInternals Process Explorer). As each Select
>statement is executed, 2 handles are created. These handles will stay
>allocated until the MySQL server is stopped (stopping my program won't free
>up the handles). Windows of course runs slow with this many handles
>allocated.
>
>1) What is MySQL using the handles for?
>2) Is there any way to stop MySQL from consuming so many Windows resources?
>3) Is it like this on Linux?
>4) How does a MySQL server handle millions of queries on a large table?
>Will it hit an upper limit of Handles that it can allocate?
>
>Note: it does not appear to allocate more handles if the query is found in
>the query cache.
>
>Mike
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1



Thread