Re: Terminate the idle sessions

At Mon, 31 Aug 2020 12:51:20 +1200, Thomas Munro <thomas.munro@gmail.com> wrote in 
> On Tue, Aug 18, 2020 at 2:13 PM Li Japin <japinli@hotmail.com> wrote:
> > On Aug 18, 2020, at 9:19 AM, Kyotaro Horiguchi <horikyota.ntt@gmail.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