tcp settings
I'm wondering if there's a disconnect between my servers postgresql.conf
and the system tcp settings? Are the config names supposed to match a
file in /proc/sys/net/ipv4?
In postgres 14 install's postgresql.conf on centos 7:
# - TCP settings -
# see "man tcp" for details
#tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;
# 0 selects the system default
#tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;
# 0 selects the system default
#tcp_keepalives_count = 0 # TCP_KEEPCNT;
# 0 selects the system default
#tcp_user_timeout = 0 # TCP_USER_TIMEOUT, in
milliseconds;
# 0 selects the system default
#client_connection_check_interval = 0 # time between checks for client
# disconnection while running queries;
# 0 for never
and in the /proc/sys/net/ipv4 I see no "keepalives" plural.
tcp_abort_on_overflow tcp_fastopen_key tcp_min_snd_mss
tcp_slow_start_after_idle
tcp_adv_win_scale tcp_fin_timeout
tcp_min_tso_segs tcp_stdurg
tcp_allowed_congestion_control tcp_frto tcp_moderate_rcvbuf
tcp_synack_retries
tcp_app_win tcp_invalid_ratelimit
tcp_mtu_probing tcp_syncookies
tcp_autocorking tcp_keepalive_intvl(75)
tcp_no_metrics_save tcp_syn_retries
tcp_available_congestion_control tcp_keepalive_probes(9)
tcp_notsent_lowat tcp_thin_dupack
tcp_base_mss tcp_keepalive_time(7200)
tcp_orphan_retries tcp_thin_linear_timeouts
tcp_challenge_ack_limit tcp_limit_output_bytes
tcp_reordering tcp_timestamps
tcp_congestion_control tcp_low_latency
tcp_retrans_collapse tcp_tso_win_divisor
tcp_dsack tcp_max_orphans
tcp_retries1 tcp_tw_recycle
tcp_early_retrans tcp_max_ssthresh
tcp_retries2 tcp_tw_reuse
tcp_ecn tcp_max_syn_backlog
tcp_rfc1337 tcp_window_scaling
tcp_fack tcp_max_tw_buckets
tcp_rmem tcp_wmem
tcp_fastopen tcp_mem tcp_sack
tcp_workaround_signed_windows
My issue is that psql is timing out with "SSL SYSCALL error: Connection
timed out". The TCP defaults for keepalive look fine to me but not what
I'm experiencing. I have PGCONNECT_TIMEOUT=0 in my env for psql.
Rob Sargent <robjsargent@gmail.com> writes:
I'm wondering if there's a disconnect between my servers postgresql.conf
and the system tcp settings? Are the config names supposed to match a
file in /proc/sys/net/ipv4?
Probably not --- we certainly didn't pick them with the intention
of matching any specific system's naming of the options. On my
Linux box it looks like the related files are named
/proc/sys/net/ipv4/tcp_keepalive_XXX
But in any case, if this is your problem:
My issue is that psql is timing out with "SSL SYSCALL error: Connection
timed out".
then keepalives aren't necessarily the solution anyway. When is
this failure occurring ... is it while trying to establish the
database connection in the first place? Or does it only happen
if you've left the psql session sit idle for a long while?
regards, tom lane
My issue is that psql is timing out with "SSL SYSCALL error: Connection
timed out".then keepalives aren't necessarily the solution anyway. When is
this failure occurring ... is it while trying to establish the
database connection in the first place? Or does it only happen
if you've left the psql session sit idle for a long while?regards, tom lane
Sitting idle for not even a long while. Under half hour I feel would do it. But I don’t think it times out while I’m “SQLing”
Rob Sargent <robjsargent@gmail.com> writes:
then keepalives aren't necessarily the solution anyway. When is
this failure occurring ... is it while trying to establish the
database connection in the first place? Or does it only happen
if you've left the psql session sit idle for a long while?
Sitting idle for not even a long while. Under half hour I feel would do it. But I don’t think it times out while I’m “SQLing”
OK, that does sound like something that reducing the keepalive
interval could help with. The traditional keepalive timeout
is a couple of hours, at least on my Linux box:
$ cat /proc/sys/net/ipv4/tcp_keepalive_time
7200
but it sounds like there's something between you and the database
server that will forget connections a lot quicker than that.
regards, tom lane
On Sep 20, 2022, at 10:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Rob Sargent <robjsargent@gmail.com> writes:
then keepalives aren't necessarily the solution anyway. When is
this failure occurring ... is it while trying to establish the
database connection in the first place? Or does it only happen
if you've left the psql session sit idle for a long while?Sitting idle for not even a long while. Under half hour I feel would do it. But I don’t think it times out while I’m “SQLing”
OK, that does sound like something that reducing the keepalive
interval could help with. The traditional keepalive timeout
is a couple of hours, at least on my Linux box:$ cat /proc/sys/net/ipv4/tcp_keepalive_time
7200but it sounds like there's something between you and the database
server that will forget connections a lot quicker than that.regards, tom lane
I was afraid you would conclude that. But its just plain ol’ psql (inside emacs) on interactive node directly to db server node, all in a “protected environment”. There could be network pieces in the middle that are getting uppity. I’ll ask /them/.
Thanks