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:[EMAIL PROTECTED] Envoyé : samedi 2 juin 2007 23:49 À : mysql@lists.mysql.com Cc : [EMAIL PROTECTED] 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:[EMAIL PROTECTED] >Envoyé : dimanche 27 mai 2007 23:13 >À : 'mos'; mysql@lists.mysql.com >Objet : RE: MySQL 5.0 creates 100,000 Window handles > >Hi, > >Try [EMAIL PROTECTED] instead. > >Bye. >Geof. > >-----Message d'origine----- >De : mos [mailto:[EMAIL PROTECTED] >Envoyé : vendredi 25 mai 2007 06:41 >À : mysql@lists.mysql.com >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/[EMAIL PROTECTED] > > > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
