Overcoming TCP/IP Connection Limits with PgBouncer and PostgreSQL on Windows
Challenge
When running PgBouncer and PostgreSQL on Windows, especially under high-load scenarios with frequent connect/disconnect cycles, you may encounter exhaustion of available TCP ports. This is due to many connections ending up in the TIME_WAIT state, preventing immediate reuse of those ports and potentially leading to connection failures.
PgBouncer helps reduce the frequency of backend (PostgreSQL) connections, but VBO Controller Proxies opens and closes connections rapidly to PgBouncer itself, what can lead to the Windows ports exhaustion.
Cause
- Available Ephemeral Port Exhaustion — By default, Windows allows for assigning 16,384 ports (49152-65535) for outgoing connections, known as the Dynamic Port Range. In heavy load environments with multiple connections, the available ephemeral ports may be exhausted.
- Ports Stuck in TIME_WAIT State — When a TCP connection is closed, the port assigned to that connection enters the TIME_WAIT state for 240 seconds (Windows default value). While in this state, the port cannot be reassigned to another connection. Therefore, when there is a high connection churn, this 4-minute delay in unused port reassignment can cause all available ephemeral ports to be used up.
Solution
This situation can be resolved by reducing the port wait timeout, increasing the number of ephemeral ports, or combining them.
Considerations
- It is advisable first to modify the TIME_WAIT timeout period, and then, only if issues persist, consider expanding the Dynamic Port Range (quantity of available ephemeral ports).
- The Windows default Dynamic Port Range of 49152-65535 aligns with RFC6335.6, and most operating systems and firewalls will expect that port range to be used by ephemeral ports for communication.
- Considering the maximum port number is 65535, the only way to expand the Dynamic Port Range is to change the Dynamic Port Range's starting port. That means selecting a port number in the range prescribed for registered ports, which are more commonly used for persistent service communication.
- When changing the Dynamic Port Range, we suggest initially using a starting port far higher than most services will utilize, such as port 15,000, to prevent port conflicts. The change from a Dynamic Port Range of 49152-65535 to 15000-65535 is an increase from 16,384 ports to 50,535, or a 208% increase in available ephemeral ports.
Reducing the TIME_WAIT Timeout Period (Recommended)
Create the following registry value to modify the length of time that a connection will stay in the TIME_WAIT state before being closed. The default is 240 seconds, which, with the default Dynamic Port Range, will lead to a limit of 68 connections per second. Changing this to 30 seconds will increase that to 546( # of ports / time out in seconds = connections per second || 16384/30= ~546) connections per second.
Key Location: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
Value Name: TcpTimedWaitDelay
Value Type: DWORD (32-Bit) Value
Value Data: 30
Note: Restart required to take effect.
PowerShell Command:
Increasing the Number of Available Ephemeral Ports (Conditional)
Before you change the Dynamic Port Range, please review all notes in the Considerations section above.
If issues persist after reducing the TCP Timed Wait Delay to 30 seconds. The following command will change the starting port of Dynamic Port Range from 49152 to 15000, resulting in just over triple the number of ports and raising the theoretical connections per second limit from 546( # of ports / time out in seconds = connections per second || 16384/30= ~546) to 1,684.
Note: Restart required to take effect.
If this KB article did not resolve your issue or you need further assistance with Veeam software, please create a Veeam Support Case.
To submit feedback regarding this article, please click this link: Send Article Feedback
To report a typo on this page, highlight the typo with your mouse and press CTRL + Enter.
Spelling error in text
Thank you!
Your feedback has been received and will be reviewed.
KB Feedback/Suggestion
This form is only for KB Feedback/Suggestions, if you need help with the software open a support case
Thank you!
Your feedback has been received and will be reviewed.
Oops! Something went wrong.
Please, try again later.