There are CLOSE_WAIT, TIME_WAIT states in the postgresql connection client

ssh 192.168.56.100

# netstat -antp|grep -i "5672" |grep -v "ESTABLISHED" 
tcp        1      0 192.168.56.100:52674       192.168.56.150:5672         CLOSE_WAIT  116553/postgres: pe
tcp        1      0 192.168.56.100:37251       192.168.56.150:5672         CLOSE_WAIT  46480/postgres: pei
tcp        1      0 192.168.56.100:47048       192.168.56.150:5672         CLOSE_WAIT  118735/postgres: pe
tcp       95      0 192.168.56.100:56547       192.168.56.150:5672         CLOSE_WAIT  94628/postgres: pei
tcp        0      0 192.168.56.100:55290       192.168.56.150:5672         TIME_WAIT   - 

The details of the network state can be baidu. Here's just a brief description.

CLOSE_WAIT passive shutdown
After closing the connection at 192.168.56.150, FIN has been received locally, but the connection is in CLOSE_WAIT state when FIN has not yet been sent.
This resource has always been occupied by programs.

TIME_WAIT Active Closing
192.168.56.100 The state maintained by the party actively closing the connection.
The solution is to enable the server to quickly recycle and reuse those TIME_WAIT resources.

TIME_WAIT state can be optimized by adjusting the parameters of os and postgresql
1)os parameters

# sysctl -a |grep -i tcp_keepalive

net.ipv4.tcp_keepalive_intvl = 30
net.ipv4.tcp_keepalive_probes = 9
net.ipv4.tcp_keepalive_time = 600

//Attach default values for the system
net.ipv4.tcp_keepalive_intvl = 15
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_time = 7200

2)postgresql parameter

select ps.name,ps.setting,ps.unit,ps.category,ps.short_desc
from pg_settings ps
where 1=1
and ps.category like '%Client Connection Defaults / Other Defaults%'
;

          name           | setting | unit |                  category                   |                        short_desc                        
-------------------------+---------+------+---------------------------------------------+----------------------------------------------------------
 dynamic_library_path    | $libdir |      | Client Connection Defaults / Other Defaults | Sets the path for dynamically loadable modules.
 gin_fuzzy_search_limit  | 0       |      | Client Connection Defaults / Other Defaults | Sets the maximum allowed result for exact search by GIN.
 local_preload_libraries |         |      | Client Connection Defaults / Other Defaults | Lists shared libraries to preload into each backend.
 tcp_keepalives_count    | 0       |      | Client Connection Defaults / Other Defaults | Maximum number of TCP keepalive retransmits.
 tcp_keepalives_idle     | 0       | s    | Client Connection Defaults / Other Defaults | Time between issuing TCP keepalives.
 tcp_keepalives_interval | 0       | s    | Client Connection Defaults / Other Defaults | Time between TCP keepalive retransmits.
(6 Row record)

tcp_keepalives_idle (integer)
Specifies how many seconds after inactivity a keep alive message is sent to the client over TCP.
A value of 0 denotes the use of default values.
This parameter can only be used on systems or Windows that support TCP_KEEPIDLE or TCP_KEEPALIVE symbols.
On other systems, it must be zero. In sessions connected through Unix domain sockets, this parameter is ignored and always read as zero.

Note: On Windows, if the value is 0, the system will set the parameter to 2 hours, because Windows does not support reading system default values.

tcp_keepalives_interval (integer)
Specifies how many seconds later to resend a TCP keepalive message that has not yet been notified by the client.
A value of 0 denotes the use of system defaults.
This parameter can only be used on systems or Windows that support TCP_KEEPINTVL symbols.
On other systems, it must be zero. In sessions connected through Unix domain sockets, this parameter is ignored and always read as zero.

Note: On Windows, if the value is 0, the system will set this parameter to 1 second, because Windows does not support reading system default values.

tcp_keepalives_count (integer)
Specifies the number of TCP keepalive s allowed to be lost before the server connection to the client is deemed dead.
A value of 0 denotes the use of system defaults.
This parameter can only be used on systems that support TCP_KEEPCNT symbols.
On other systems, it must be zero. In sessions connected through Unix domain sockets, this parameter is ignored and always read as zero.

Note: Windows does not support this parameter and must be zero.

If the parameters tcp_keepalives_count, tcp_keepalives_idle, tcp_keepalives_interval are set to non-zero values, then use the parameters of postgresql, otherwise use the parameters corresponding to os.

Parametric correspondence, first as postgresql parameter, second as os parameter

tcp_keepalives_idle       tcp_keepalive_time
tcp_keepalives_interval   tcp_keepalive_intvl
tcp_keepalives_count      tcp_keepalive_probes 

Tags: Windows PostgreSQL Unix ssh

Posted on Thu, 13 Dec 2018 06:18:07 -0500 by snipe7kills