RE: Timeout parameters - Mailing list pgsql-hackers
| From | AYahorau@ibagroup.eu |
|---|---|
| Subject | RE: Timeout parameters |
| Date | |
| Msg-id | OF1013C67D.8BB51BC0-ON4325837D.004D7371-4325837D.0053B06C@iba.by Whole thread Raw |
| In response to | RE: Timeout parameters ("Tsunakawa, Takayuki" <tsunakawa.takay@jp.fujitsu.com>) |
| List | pgsql-hackers |
Hello,
> To wrap up, the relevant parameters work like this:
> * TCP keepalive and TCP user (retransmission) timeout: for network problems
> * statement_timeout: for long-running queries
> * socket_timeout (or send/recv_timeout): for saturated servers
Takayuki Tsunakawa, could you provide wider explanation of socket_timeout? I'm little bit misunderstanding in which cases this parameter is/can be used.
I would like to add some more information about TCP keepalive and TCP_USER_TIMEOUT mechanisms:
1) Both these mechanisms are used for termination socket connection in case of network problems.
2) This termination of tcp connection is done on operation system level (kernel) and not by application.
3) TCP keepalive and TCP_USER_TIMEOUT work differently and complement each other :
* TCP keepalive mechanism works when a socket is in idle state (there is no any transaction in this case)
* TCP_USER_TIMEOUT mechanism works when a socket is in active state (sending/receiving data).
So, TCP keepalive and TCP_USER_TIMEOUT provide full control under network state directly after creating a TCP socket and applying these parameters to it. Moreover, this control is delegate to the operation system (kernel) in case it supports such mechanisms.
If TCP_USER_TIMEOUT is not supported by PostgreSQL, it means that TCP connection are partly controlled by the operation system (kernel). In this case pqWaitTimed() should be used on the application layer for connection control in data transmission phase.
In my opinion, there is no any difference between server and client connection sides. To avoid mess in the configuration it seems reasonable to give the same name of this option for the client and server sides.
To my mind, this description of tcp_user_timeout option is not correct (See my comment about TCP_USER_TIMEOUT mechanism above).
As for me It better to specify the description as follows:
<listitem>
<para>
Define a wrapper for TCP_USER_TIMEOUT socket option of libpq connection.
</para>
<para>
Specifies the number of milliseconds after which a TCP connection can be aborted by the operation system due to network problems when the data is transmitting through this connection (sending/receiving). A value of 0 uses the system default. This parameter is supported only on systems that support TCP_USER_TIMEOUT or an equivalent socket option, and on Windows; on other systems, it must be zero. In sessions connected via a Unix-domain socket, this parameter is ignored and always reads as zero.
</para>
<note>
<para>
This parameter is not supported on Windows, and must be zero.
</para>
<para>
To enable full control under TCP connection use this option together with keepalive.
</para>
</note>
</listitem>
Best regards,
Andrei Yahorau
From: "Tsunakawa, Takayuki" <tsunakawa.takay@jp.fujitsu.com>
To: 'Fabien COELHO' <coelho@cri.ensmp.fr>, "Nagaura, Ryohei" <nagaura.ryohei@jp.fujitsu.com>,
Cc: "'pgsql-hackers@postgresql.org'" <pgsql-hackers@postgresql.org>, "AYahorau@ibagroup.eu" <AYahorau@ibagroup.eu>
Date: 27/12/2018 11:26
Subject: RE: Timeout parameters
From: Fabien COELHO [mailto:coelho@cri.ensmp.fr]
> I still do not understand the use-case specifics: for me, aborting the
> connection, or a softer cancelling the statement, will result in the
> server stopping the statement, so the server does NOT "continue the job",
> so I still do not see how it really differs from the server-side
> statement_timeout setting.
How about when the server is so saturated that statement_timeout cannot work? See SQLNET.SEND_TIMEOUT and SQLNET.RECV_TIMEOUT here:
https://docs.oracle.com/cd/E11882_01/network.112/e10835/sqlnet.htm#NETRF228
As these parameter names suggest, maybe we could use SEND_TIMEO and RECV_TIMEO socket options for setsockopt() instead of using pqWaitTimed().
To wrap up, the relevant parameters work like this:
* TCP keepalive and TCP user (retransmission) timeout: for network problems
* statement_timeout: for long-running queries
* socket_timeout (or send/recv_timeout): for saturated servers
FYI, PgJDBC has a parameter named socketTimeout:
https://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters
Regards
Takayuki Tsunakawa
> To wrap up, the relevant parameters work like this:
> * TCP keepalive and TCP user (retransmission) timeout: for network problems
> * statement_timeout: for long-running queries
> * socket_timeout (or send/recv_timeout): for saturated servers
Takayuki Tsunakawa, could you provide wider explanation of socket_timeout? I'm little bit misunderstanding in which cases this parameter is/can be used.
I would like to add some more information about TCP keepalive and TCP_USER_TIMEOUT mechanisms:
1) Both these mechanisms are used for termination socket connection in case of network problems.
2) This termination of tcp connection is done on operation system level (kernel) and not by application.
3) TCP keepalive and TCP_USER_TIMEOUT work differently and complement each other :
* TCP keepalive mechanism works when a socket is in idle state (there is no any transaction in this case)
* TCP_USER_TIMEOUT mechanism works when a socket is in active state (sending/receiving data).
So, TCP keepalive and TCP_USER_TIMEOUT provide full control under network state directly after creating a TCP socket and applying these parameters to it. Moreover, this control is delegate to the operation system (kernel) in case it supports such mechanisms.
If TCP_USER_TIMEOUT is not supported by PostgreSQL, it means that TCP connection are partly controlled by the operation system (kernel). In this case pqWaitTimed() should be used on the application layer for connection control in data transmission phase.
In my opinion, there is no any difference between server and client connection sides. To avoid mess in the configuration it seems reasonable to give the same name of this option for the client and server sides.
To my mind, this description of tcp_user_timeout option is not correct (See my comment about TCP_USER_TIMEOUT mechanism above).
| > + <listitem> > + <para> > + Specify in milliseconds the time to disconnect to the client > + when there is no ack packet from the client to the server's data transmission. > + This parameter is supported on linux version 2.6.37 or later. > + </para> > + <note> > + <para> > + This parameter is not supported on Windows. > + </para> > + </note> > + </listitem> |
As for me It better to specify the description as follows:
<listitem>
<para>
Define a wrapper for TCP_USER_TIMEOUT socket option of libpq connection.
</para>
<para>
Specifies the number of milliseconds after which a TCP connection can be aborted by the operation system due to network problems when the data is transmitting through this connection (sending/receiving). A value of 0 uses the system default. This parameter is supported only on systems that support TCP_USER_TIMEOUT or an equivalent socket option, and on Windows; on other systems, it must be zero. In sessions connected via a Unix-domain socket, this parameter is ignored and always reads as zero.
</para>
<note>
<para>
This parameter is not supported on Windows, and must be zero.
</para>
<para>
To enable full control under TCP connection use this option together with keepalive.
</para>
</note>
</listitem>
Best regards,
Andrei Yahorau
From: "Tsunakawa, Takayuki" <tsunakawa.takay@jp.fujitsu.com>
To: 'Fabien COELHO' <coelho@cri.ensmp.fr>, "Nagaura, Ryohei" <nagaura.ryohei@jp.fujitsu.com>,
Cc: "'pgsql-hackers@postgresql.org'" <pgsql-hackers@postgresql.org>, "AYahorau@ibagroup.eu" <AYahorau@ibagroup.eu>
Date: 27/12/2018 11:26
Subject: RE: Timeout parameters
From: Fabien COELHO [mailto:coelho@cri.ensmp.fr]
> I still do not understand the use-case specifics: for me, aborting the
> connection, or a softer cancelling the statement, will result in the
> server stopping the statement, so the server does NOT "continue the job",
> so I still do not see how it really differs from the server-side
> statement_timeout setting.
How about when the server is so saturated that statement_timeout cannot work? See SQLNET.SEND_TIMEOUT and SQLNET.RECV_TIMEOUT here:
https://docs.oracle.com/cd/E11882_01/network.112/e10835/sqlnet.htm#NETRF228
As these parameter names suggest, maybe we could use SEND_TIMEO and RECV_TIMEO socket options for setsockopt() instead of using pqWaitTimed().
To wrap up, the relevant parameters work like this:
* TCP keepalive and TCP user (retransmission) timeout: for network problems
* statement_timeout: for long-running queries
* socket_timeout (or send/recv_timeout): for saturated servers
FYI, PgJDBC has a parameter named socketTimeout:
https://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters
Regards
Takayuki Tsunakawa
pgsql-hackers by date: