Re: Both master and slave stop accepting connections

[admin www.mercuriusgids.nl]

Are the clients connecting as user "root"? Don't do that. Give them their own login(s). And don't provide "SUPER" privilege to that login(s).

>> we need the super privilege in order to use init_connect ( which executes some default statements upon start of a connection )

That way, you can connect when there is a hang and do the following:

>> there are only 13 connection visible in mysql administrator. Mysql administrator maintains an open connection all time. When i look at the graphs, they all hang and have stopped.
>> tcpview also shows 14 connections ( probably one extra for the slave )
>> handles are 258k

>> testing maximum limit for handles during hang of mysql

C:\temp>testlimit -h

Testlimit v5.04 - test Windows limits
By Mark Russinovich - www.sysinternals.com

Creating handles...
Created 16744424 handles. Lasterror: 1450
Insufficient system resources exist to complete the requested service.
^C

>> peak working set 843000k, working set 791052 available memory 16GB


SHOW PROCESSLIST; -- to see what is going on.

>> cannot do that, even when i keep a session mysql.exe open with all timeouts, like interactive_timeout to say 38400. The connection really hangs when performing a statement and doesnt timeout.

KILL ... -- to kill individual threads
>> cannot kill

SHOW GLOBAL STATUS LIKE 'Max_used_connections'; (after it happens, but before it is restarted!) I suspect it will say 41.

SHOW GLOBAL STATUS LIKE '%timeout%';

Are the clients disconnecting when they are finished?

>> ofcourse :)

Are the clients coming from Apache or some other web server? In Apache, what is your setting for MaxClients? (I'll bet it is higher than max_connections.)

>> clients are indeed apache php clients. average request completes within 0.08 seconds. Its very fast software.

>> Maybe something to do with limits for windows? We process 16-40 million queries before it happens.

>> It seems like a mutex/semaphore problem. I will dump a stackdump of each thread running/locking up.