Timeout parameters

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Timeout parameters

Nagaura, Ryohei
Hi, all.

I'd like to suggest introducing two parameters to handle client-server communication timeouts.
That is "tcp_user_timeout" and "socket_timeout" parameter.

I implemented "tcp_user_timeout" parameter
in both backend and frontend side.
This parameter enables us to
use TCP_USER_TIMEOUT option on linux.
If the parameter is specified, the process sets the value to
TCP_USER_TIMEOUT option.
In my opinion, this option is needed for the following situation:
If the server can't return an ack packet to the request from the client,
the client performs retransmission processing.
In this case TCP keepalive option can't work.
Therefore we need TCP USER TIMEOUT option.
Andrei Yahorau also refer to the necessity of this option in [1].

"socket_timeout" is the application layer timeout parameter
from when frontend issues SQL query
to when frontend receives the execution result from backend.
When this parameter is active and timeout occurs,
frontend close the socket.
It is a merit for client to set the maximum time
to wait for SQL.

I'm waiting for your opinions or reviews.

[1] https://www.postgresql.org/message-id/flat/OF4C8A68CE.A350F319-ON432582D0.0028A5FF-432582D0.002FEE28%40iba.by

Bes regards,
---------------------
Ryohei Nagaura


socket_timeout.patch (4K) Download Attachment
TCP_USER_TIMEOUT_in_backend.patch (5K) Download Attachment
TCP_USER_TIMEOUT_in_interface.patch (7K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Timeout parameters

AYahorau
Hello Ryohei,


I took a look at your changes and I have some notes.
I faced the same issue as you faced. In my opinion hanging of a client is quite critical case and it needs to be overcame.
TCP_USER_TIMEOUT option helps to overcome this problem and I agree with you that it needs to be supported within PostgreSQL.

Nevertheless, it is necessary to take into account that the option TCP_USER_TIMEOUT is supported by Linux kernel starting since 2.6.37. In a lower kernel version these changes will not take affect.

I am not sure that suggested by you “socket_timeout” option should be implemented.
I see that you have changed pqWait() function. In my opinion it contradicts a bit with the comment to this function:
“We also stop waiting and return if the kernel flags an exception condition on the socket.” It means that this function should wait for some condition (ready to read/write) forever. On the other side, there is a function pqWaitTimed() which does the same action but within a timeout. So, in my opinion such changes of this function can lead to the problem with backward compatibility: the caller process expects that it will wait forever but terminates unexpectedly by timeout.

As far as I understand PostgreSQL versioning policy, the implementation of new parameter requires modification of internal PostgreSQL structure. As you know it is not posssible without stopping a service which can be done during migration to the new major version of PostgreSQL which is expected to be released in September 2019.

As a workaround I suggest using asynchronous command processing
https://www.postgresql.org/docs/10/static/libpq-async.html.

Best regards,
Andrei Yahorau



From:        "Nagaura, Ryohei" <[hidden email]>
To:        "'[hidden email]'" <[hidden email]>,
Cc:        "[hidden email]" <[hidden email]>
Date:        23/10/2018 07:37
Subject:        Timeout parameters




Hi, all.

I'd like to suggest introducing two parameters to handle client-server communication timeouts.
That is "tcp_user_timeout" and "socket_timeout" parameter.

I implemented "tcp_user_timeout" parameter
in both backend and frontend side.
This parameter enables us to
use TCP_USER_TIMEOUT option on linux.
If the parameter is specified, the process sets the value to
TCP_USER_TIMEOUT option.
In my opinion, this option is needed for the following situation:
If the server can't return an ack packet to the request from the client,
the client performs retransmission processing.
In this case TCP keepalive option can't work.
Therefore we need TCP USER TIMEOUT option.
Andrei Yahorau also refer to the necessity of this option in [1].

"socket_timeout" is the application layer timeout parameter
from when frontend issues SQL query
to when frontend receives the execution result from backend.
When this parameter is active and timeout occurs,
frontend close the socket.
It is a merit for client to set the maximum time
to wait for SQL.

I'm waiting for your opinions or reviews.

[1]
https://www.postgresql.org/message-id/flat/OF4C8A68CE.A350F319-ON432582D0.0028A5FF-432582D0.002FEE28%40iba.by

Bes regards,
---------------------
Ryohei Nagaura

[attachment "socket_timeout.patch" deleted by Andrei Yahorau/IBA] [attachment "TCP_USER_TIMEOUT_in_backend.patch" deleted by Andrei Yahorau/IBA] [attachment "TCP_USER_TIMEOUT_in_interface.patch" deleted by Andrei Yahorau/IBA]

Reply | Threaded
Open this post in threaded view
|

RE: Timeout parameters

Nagaura, Ryohei
Hi Andrei,

Thank you for response.

> TCP_USER_TIMEOUT option helps to overcome this problem and I agree with
> you that it needs to be supported within PostgreSQL.
I'm glad to your agreement.

> Nevertheless, it is necessary to take into account that the option
> TCP_USER_TIMEOUT is supported by Linux kernel starting since 2.6.37. In
> a lower kernel version these changes will not take affect.
Does it mean how do we support Linux OS whose kernel version is less than 2.6.37?

> I am not sure that suggested by you “socket_timeout” option should be
> implemented.
> As a workaround I suggest using asynchronous command processing
> https://www.postgresql.org/docs/10/static/libpq-async.html
There are many applications implemented with synchronous API
(e.g. PQexec()), so "socket_timeout" is useful I think.

Best regards,
---------------------
Ryohei Nagaura

Reply | Threaded
Open this post in threaded view
|

RE: Timeout parameters

Nagaura, Ryohei
Hi Andrei,

First, I inform you that I may not contact for the following period:
From November 1st to November 19th

Second, I noticed my misunderstanding in previous mail.
> > Nevertheless, it is necessary to take into account that the option
> > TCP_USER_TIMEOUT is supported by Linux kernel starting since 2.6.37.
> > In a lower kernel version these changes will not take affect.
> Does it mean how do we support Linux OS whose kernel version is less than
> 2.6.37?
I understand that you pointed out my implementation.
I'll remake patch files when I return.

Finally, I write test method for each parameters here roughly.
You may use iptables command on linux when testing TCP_USER_TIMEOUT.
You may use pg_sleep(seconds) command in postgres.
I'll write the details after my returning.

Continue to discuss the socket_timeout, please.

Best regards,
---------------------
Ryohei Nagaura

Reply | Threaded
Open this post in threaded view
|

Re: Timeout parameters

Fabien COELHO-3
In reply to this post by Nagaura, Ryohei

Hello Ryohei,

> I'd like to suggest introducing two parameters to handle client-server
> communication timeouts.

I'm generally fine with giving more access to low-level parameters to
users. However, I'm not sure I understand the use case you have that needs
these new extensions.

> "socket_timeout" parameter.

About the "socket_timout" patch:

Patch does not apply cleanly because of a "trailing whitespace" in a
comment. Please remove spaces at the end of lines.

I'd like clarifications about the use case that needs this specific
feature, especially to understand why the server-side "statement_timeout"
setting is not right enough.

> "socket_timeout" is the application layer timeout parameter from when
> frontend issues SQL query to when frontend receives the execution result
> from backend. When this parameter is active and timeout occurs, frontend
> close the socket. It is a merit for client to set the maximum time to
> wait for SQL.

I think that there is some kind of a misnomer: this is not a socket-level
timeout, but a client-side query timeout, so it should be named
differently? I'm not sure how to name it, though.

I checked that the feature works at the psql level.

   sh> psql "socket_timeout=2"

   psql> SELECT 1;
   1

   psql> SELECT pg_sleep(3);
   timeout expired
   The connection to the server was lost. Attempting reset: Succeeded.

The timeout is per statement, if there are several statements, each get
its own timeout, just like server-side "statement_timeout".

I think that the way it works is a little extreme, basically the
connection is aborted from within pqWait, and then restarted from scratch.
I would not expect that from such a feature, but I'm not sure how to
cancel a query from libpq, but it is possible, eg:


  psql> SELECT pg_sleep(10);
  ^C Cancel request sent
  ERROR:  canceling statement due to user request

  psql>

Would that be better? It probably assumes that the connection is okay.

The implementation looks awkward, because part of the logic of pqWaitTimed
is reimplemented in pqWait. Also, I do not understand the computation
of finish_time, which seems to assume that pqWait is going to be called
immediately after sending a query, which may or may not be the case, and
if it is not the time() call there is not the start of the statement.

C style: all commas should be followed by a space (or newline).

There is no clear way to know about the value of the setting (SHOW, \set,
\pset...). Ok, this is already the case of other connection parameters.

Using "atoi" is a bad idea because it accepts trailing garbage and does
not detect overflows. Use the "parse_int_param" function instead.

There are no tests.

There is no documentation.

--
Fabien.

Reply | Threaded
Open this post in threaded view
|

RE: Timeout parameters

Nagaura, Ryohei
Hi, Fabien.

Thank you for your review.
And I'm very sorry to have kept you waiting so long.


About "socket_timeout"

> I'm generally fine with giving more access to low-level parameters to users.
> However, I'm not sure I understand the use case you have that needs these
> new extensions.
If you face the following situation, this parameter will be needed.
1. The connection between the server and the client has been established normally.
2. A server process has been received SQL statement.
3. The server OS can return an ack packet, but it takes time to execute the SQL statement
   Or return the result because the server process is very busy.
4. The client wants to close the connection while leaving the job to the server.
In this case, "statement_timeout" can't satisfy at line 4.

> I think that there is some kind of a misnomer: this is not a socket-level
> timeout, but a client-side query timeout, so it should be named differently?
Yes, I think so.

> I'm not sure how to name it, though.
Me too.

> I think that the way it works is a little extreme, basically the connection
> is aborted from within pqWait, and then restarted from scratch.
>
> There is no clear way to know about the value of the setting (SHOW, \set,
> \pset...). Ok, this is already the case of other connection parameters.
If this parameter can be needed, I would like to discuss design and optional functions.
How do you think?
I'll correct patch of "socket_timeout" after that.


About "TCP_USER_TIMEOUT"
I fixed on the previous feedback.
Would you review, please?

> There are no tests.
I introduce the test methods of TCP_USER_TIMEOUT.

Test of client-side TCP_USER_TIMEOUT:
[client operation]
1. Connect DB server.
        postgres=# psql postgresql://USERNAME:PASSWORD@hostname:port/dbname?tcp_user_timeout=15000
2. Get the port number by the following command:
        postgres=# select inet_client_port();
3. Close the client port from the other console of the client machine.
   Please rewrite "56750" to the number confirmed on line 2.
        $ iptables -I INPUT -p tcp --dport 56750 -j DROP
4. Query the following SQL:
        postgres=# select pg_sleep(10);
5. TCP USER TIMEOUT works correctly if an error message is output to the console.

Test of server-side TCP_USER_TIMEOUT:
[client operation]
1. Connect DB server.
2. Get the port number by the following command:
        postgres=# select inet_client_port();
3. Set the TCP_USER_TIMEOUT by the following command:
        postgres=# set tcp_user_timeout=15000;
4. Query the following SQL:
        postgres=# select pg_sleep(10);
5. Close the client port from the other console.
   Please rewrite "56750" to the number confirmed on line 2.
        $ iptables -I INPUT -p tcp --dport 56750 -j DROP
[server operation]
6. Verify the logfile.

> There is no documentation.
I made a patch of documentation of TCP USER TIMEOUT.

Best regards,
---------------------
Ryohei Nagaura


document.patch (2K) Download Attachment
TCP_backend.patch (5K) Download Attachment
TCP_interface.patch (4K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

RE: Timeout parameters

Nagaura, Ryohei
Hi,

There was an invisible space, so I removed it.
I registered with 2019-01 commitfest.

Best regards,
---------------------
Ryohei Nagaura

> -----Original Message-----
> From: Nagaura, Ryohei [mailto:[hidden email]]
> Sent: Thursday, December 6, 2018 2:20 PM
> To: 'Fabien COELHO' <[hidden email]>;
> '[hidden email]' <[hidden email]>
> Cc: Yahorau, A. (IBA) <[hidden email]>
> Subject: RE: Timeout parameters
>
> Hi, Fabien.
>
> Thank you for your review.
> And I'm very sorry to have kept you waiting so long.
>
>
> About "socket_timeout"
>
> > I'm generally fine with giving more access to low-level parameters to
> users.
> > However, I'm not sure I understand the use case you have that needs
> > these new extensions.
> If you face the following situation, this parameter will be needed.
> 1. The connection between the server and the client has been established
> normally.
> 2. A server process has been received SQL statement.
> 3. The server OS can return an ack packet, but it takes time to execute
> the SQL statement
>    Or return the result because the server process is very busy.
> 4. The client wants to close the connection while leaving the job to the
> server.
> In this case, "statement_timeout" can't satisfy at line 4.
>
> > I think that there is some kind of a misnomer: this is not a
> > socket-level timeout, but a client-side query timeout, so it should be
> named differently?
> Yes, I think so.
>
> > I'm not sure how to name it, though.
> Me too.
>
> > I think that the way it works is a little extreme, basically the
> > connection is aborted from within pqWait, and then restarted from scratch.
> >
> > There is no clear way to know about the value of the setting (SHOW,
> > \set, \pset...). Ok, this is already the case of other connection
> parameters.
> If this parameter can be needed, I would like to discuss design and optional
> functions.
> How do you think?
> I'll correct patch of "socket_timeout" after that.
>
>
> About "TCP_USER_TIMEOUT"
> I fixed on the previous feedback.
> Would you review, please?
>
> > There are no tests.
> I introduce the test methods of TCP_USER_TIMEOUT.
>
> Test of client-side TCP_USER_TIMEOUT:
> [client operation]
> 1. Connect DB server.
> postgres=# psql
> postgresql://USERNAME:PASSWORD@hostname:port/dbname?tcp_user_timeout=1
> 5000
> 2. Get the port number by the following command:
> postgres=# select inet_client_port();
> 3. Close the client port from the other console of the client machine.
>    Please rewrite "56750" to the number confirmed on line 2.
> $ iptables -I INPUT -p tcp --dport 56750 -j DROP 4. Query the
> following SQL:
> postgres=# select pg_sleep(10);
> 5. TCP USER TIMEOUT works correctly if an error message is output to the
> console.
>
> Test of server-side TCP_USER_TIMEOUT:
> [client operation]
> 1. Connect DB server.
> 2. Get the port number by the following command:
> postgres=# select inet_client_port();
> 3. Set the TCP_USER_TIMEOUT by the following command:
> postgres=# set tcp_user_timeout=15000;
> 4. Query the following SQL:
> postgres=# select pg_sleep(10);
> 5. Close the client port from the other console.
>    Please rewrite "56750" to the number confirmed on line 2.
> $ iptables -I INPUT -p tcp --dport 56750 -j DROP [server operation]
> 6. Verify the logfile.
>
> > There is no documentation.
> I made a patch of documentation of TCP USER TIMEOUT.
>
> Best regards,
> ---------------------
> Ryohei Nagaura


document_v2.patch (2K) Download Attachment
TCP_backend_v2.patch (5K) Download Attachment
TCP_interface_v2.patch (4K) Download Attachment