Re: Terminate the idle sessions

At Mon, 31 Aug 2020 12:51:20 +1200, Thomas Munro <thomas(dot)munro(at)gmail(dot)com> wrote in
> 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

I think it's worth doing. Maybe we can get rid of doing anything other
than removing an entry in the case where we disable a non-nearest
timeout.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center