Обсуждение: 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
> 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