A.2.2.1. Connection to MySQL Server Failing on Windows
When you're running a MySQL server on Windows with many TCP/IP
connections to it, and you're experiencing that quite often
your clients get a Can't connect to MySQL
server error, the reason might be that Windows
doesn't allow for enough ephemeral (short-lived) ports to
serve those connections.
By default, Windows allows 5000 ephemeral (short-lived) TCP
ports to the user. After any port is closed it will remain in
a TIME_WAIT status for 120 seconds. This
status allows the connection to be reused at a much lower cost
than reinitializing a brand new connection. However, the port
will not be available again until this time expires.
With a small stack of available TCP ports (5000) and a high
number of TCP ports being open and closed over a short period
of time along with the TIME_WAIT status you
have a good chance for running out of ports. There are two
ways to address this problem:
IMPORTANT: The following procedure
involves modifying the Windows registry. Before you modify the
registry, make sure to back it up and make sure that you
understand how to restore the registry if a problem occurs.
For information about how to back up, restore, and edit the
registry, view the following article in the Microsoft
Knowledge Base:
https://support.microsoft.com/kb/256986/EN-US/.
Start Registry Editor (Regedt32.exe).
-
Locate the following key in the registry:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
-
On the Edit menu, click Add
Value, and then add the following registry
value:
Value Name: MaxUserPort
Data Type: REG_DWORD
Value: 65534
This sets the number of ephemeral ports available to any
user. The valid range is between 5000 and 65534 (decimal).
The default value is 0x1388 (5000 decimal).
-
On the Edit menu, click Add
Value, and then add the following registry
value:
Value Name: TcpTimedWaitDelay
Data Type: REG_DWORD
Value: 30
This sets the number of seconds to hold a TCP port
connection in TIME_WAIT state before
closing. The valid range is between 0 (zero) and 300
(decimal). The default value is 0x78 (120 decimal).
Quit Registry Editor.
Reboot the machine.
Note: Undoing the above should be as simple as deleting the
registry entries you've created.
