Re: Terminate the idle sessions

On Tue, Aug 18, 2020 at 2:13 PM Li Japin <japinli(at)hotmail(dot)com> wrote:
> On Aug 18, 2020, at 9:19 AM, Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com> wrote:
> The same already happens for idle_in_transaction_session_timeout and
> we can use "ALTER ROLE/DATABASE SET" to dislable or loosen them, it's
> a bit cumbersome, though. I don't think we should (at least
> implicitly) disable those timeouts ad-hockerly for postgres_fdw.
>
> +1.

This seems like a reasonable feature to me.

The delivery of the error message explaining what happened is probably
not reliable, so to some clients and on some operating systems this
will be indistinguishable from a dropped network connection or other
error, but that's OK and we already have that problem with the
existing timeout-based disconnection feature.

The main problem I have with it is the high frequency setitimer()
calls. If you enable both statement_timeout and idle_session_timeout,
then we get up to huge number of system calls, like the following
strace -c output for a few seconds of one backend under pgbench -S
workload shows:

% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
39.45 0.118685 0 250523 setitimer
29.98 0.090200 0 125275 sendto
24.30 0.073107 0 126235 973 recvfrom
6.01 0.018068 0 20950 pread64
0.26 0.000779 0 973 epoll_wait
------ ----------- ----------- --------- --------- ----------------
100.00 0.300839 523956 973 total

There's a small but measurable performance drop from this, as also
discussed in another thread about another kind of timeout[1]. Maybe
we should try to fix that with something like the attached?

[1] https://www.postgresql.org/message-id/flat/77def86b27e41f0efcba411460e929ae%40postgrespro.ru