Thread: 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:
In postgres 14 install's postgresql.conf on centos 7:
# - TCP settings -and in the /proc/sys/net/ipv4 I see no "keepalives" plural.
# 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
tcp_abort_on_overflow tcp_fastopen_key tcp_min_snd_mss tcp_slow_start_after_idleMy 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.
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
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 > 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 I was afraid you would conclude that. But its just plain ol’ psql (inside emacs) on interactive node directly to db servernode, all in a “protected environment”. There could be network pieces in the middle that are getting uppity. I’llask /them/. Thanks