Keep Server Online
If you find the Apache Lounge, the downloads and overall help useful, please express your satisfaction with a donation.
or
A donation makes a contribution towards the costs, the time and effort that's going in this site and building.
Thank You! Steffen
Your donations will help to keep this site alive and well, and continuing building binaries. Apache Lounge is not sponsored.
| |
|
 Topic: MySQL Connection Problems |
|
| Author |
|
Thog
Joined: 12 Feb 2007
Posts: 75
Location: Montreal
|
 Posted: Tue 25 Mar '08 4:31 Post subject: MySQL Connection Problems |
 |
|
If i use mysql_connect and not mysql_pconnect I get the below errors. Not all the time though. Every 3-4 minutes they will appear for 30 seconds and then go away.
My connection count is at 1500. And I never see that many connections. I read something about how windows keeps a connection open to 120 to 240 seconds when you open and close a TCP connection. They also said maybe to add some fields to the registry but when I do it I get.
*** DEAD *** events in the logs... It starts a query and dies or something... Not sure... But nothing works...
Any ideas? With pconnect everything works fine...
Here is the errors... Remember they only appear like 10% of the time, for like 30 seconds then it's all good for another small amount of time...
[2008-03-24 22:23:56] 2003: Can't connect to MySQL server on 'localhost' (10048)
[2008-03-24 22:23:56] 2003: Can't connect to MySQL server on 'localhost' (10048)
[2008-03-24 22:23:56] 2003: Can't connect to MySQL server on 'localhost' (10048)
Even with pconnect i get states of *** DEAD *** ... I'm sure that has something to do with it... |
|
| Back to top |
|
James Blond
Moderator
Joined: 19 Jan 2006
Posts: 7373
Location: Germany, Next to Hamburg
|
| Posted: Tue 25 Mar '08 10:31 Post subject: |
|
|
the mysql_p(ersistent)connect reuses the connection. The mysql_connect opens a new connection on every statement.
On heavy PHP / MySQL action it is recomment to use the persistent connection. Also you should tune up your SQL statements. Do not use to much joins.
Another option is to take a look at your mysql settings. How many connection allowed / cache size / timeout etc. |
|
| Back to top |
|
Thog
Joined: 12 Feb 2007
Posts: 75
Location: Montreal
|
| Posted: Tue 25 Mar '08 15:03 Post subject: |
|
|
wait_timeout = 60
max_connection = 1000
There is no reason for it to run out of connections while using mysql_connect because I only ever see 10 connections max at once when viewing the process list. I think it has to do with windows keeping the connection open even when mysql closes it.
Something about adding TCPTimedWaitDelay to the registry with a value of 30 or 10 but when I do that nothing works at all.
http://dev.mysql.com/doc/refman/6.0/en/can-not-connect-to-server.html |
|
| Back to top |
|
tdonovan
Moderator
Joined: 17 Dec 2005
Posts: 611
Location: Milford, MA, USA
|
| Posted: Wed 26 Mar '08 4:09 Post subject: |
|
|
It certainly sounds like TCP "port exhaustion".
When it happens, if you type the DOS command: and all the ports between 1025 and 5000 are in use - that's what it is.
If they all (or nearly all) are in a TIME_WAIT state, then you have opened & closed too many client connections in the last 240 seconds, even if only 10 or 20 of them remain ESTABLISHED.
It is this 240 seconds of TIME_WAIT before a port number can be re-used that you can change to a shorter period with the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\TcpTimedWaitDelay registry setting.
I routinely set this to 30. It should be twice the time of the slowest TCP connection you ever expect to make, but 30 seconds is a sensible minimum value.
It isn't clear what you mean by "when I do that nothing works at all" - MySQL doesn't respond? Apache won't start? You lose all network connectivity?
There is a Microsoft article about "port exhaustion" here. It mentions BizTalk Applications as a cause - but any application which opens and closes too many TCP client connections in a short period of time can cause port exhaustion.
I have to agree with James Blond - persistent connections make more sense for most server applications. mysql_connect is OK if you are sure you will only connect to the database occasionally, but mysql_pconnect avoids a lot of unnecessary MySQL-client connection opens and closes.
-tom- |
|
| Back to top |
|
|
|
|
|
|
