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]
