[pgpool-general: 7175] Re: Idle connection in database

[Nikhil Shetty]

Hi,

Yes, we can see the pid

show pool_pools;
pool_pid |     start_time      | pool_id | backend_id | database |
username   |     create_time     | majorversion | minorversion |
pool_counter | pool_backendpid | pool_connected

18190    | 2020-08-01 06:14:41 | 0       | 1          | edb      |
enterprisedb | 2020-08-01 06:19:52 | 3            | 0            | 1
     | *29321*          | 0
18193    | 2020-08-01 06:14:41 | 0       | 1          | edb      |
enterprisedb | 2020-08-01 06:21:26 | 3            | 0            | 1
     | *29619          *| 0


select pid,query,usename,state from pg_stat_activity where
client_addr='128.199.224.132' group by usename,query,pid;
 pid  |    query     |   usename    | state
-------+--------------+--------------+-------
 *29321 *|  DISCARD ALL | enterprisedb | idle
 *29619* |  DISCARD ALL | enterprisedb | idle


Thanks and Regards,
Nikhil

On Fri, Jul 31, 2020 at 11:59 AM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> Hi Nikhil,
>
> > Hi Tatsuo,
> >
> > Thank for the information
> >
> >> No. client_idle_limit = 5mins means, clients will be disconnected
> >> after 5 minutes idle period, not backend connections.
> >
> > Agreed.
> >
> >> If you want to terminate idle connections from pgpool to backend, you
> >> should use connection_life_time or child_life_time.
> >
> >> In your case you already set 600 seconds (that is 10 minutes) to the
> >> parameters, and they would be triggered if no clients connect to
> >> pgpool within 10 minutes.
> >
> > This is not happening, we can see backend connections from 10-12 hours
> > before as well.
>
> If you execute "show pool_pools" in a session connected to pgpool, do
> you find backend pid (pool_backendpid) appearing in the
> pg_stat_actvity output (pid)?
>
> > state |    query     |          backend_start
> > -------+--------------+----------------------------------
> >  idle  |  DISCARD ALL | 27-JUL-20 18:05:35.409933 +05:30
> >  idle  |  DISCARD ALL | 27-JUL-20 18:05:35.409931 +05:30
> >  idle  |  DISCARD ALL | 27-JUL-20 18:05:35.4094 +05:30
> >  idle  |  DISCARD ALL | 27-JUL-20 18:05:35.409005 +05:30
> >  idle  |  DISCARD ALL | 27-JUL-20 18:05:35.40897 +05:30
> >  idle  |  DISCARD ALL | 27-JUL-20 18:05:35.407784 +05:30
> >  idle  |  DISCARD ALL | 27-JUL-20 18:05:35.407632 +05:30
> >  idle  |  DISCARD ALL | 27-JUL-20 18:05:35.407399 +05:30
> >  idle  |  DISCARD ALL | 27-JUL-20 18:05:35.407044 +05:30
> >  idle  |  DISCARD ALL | 27-JUL-20 18:05:35.344228 +05:30
> >  idle  |  DISCARD ALL | 27-JUL-20 18:00:35.30343 +05:30
> >  idle  |  DISCARD ALL | 27-JUL-20 18:00:35.300526 +05:30
> >  idle  |  DISCARD ALL | 27-JUL-20 18:00:35.300472 +05:30
> >  idle  |  DISCARD ALL | 27-JUL-20 18:00:35.300048 +05:30
> >  idle  |  DISCARD ALL | 27-JUL-20 18:00:35.29958 +05:30
> >  idle  |  DISCARD ALL | 27-JUL-20 18:00:35.299185 +05:30
> >  idle  |  DISCARD ALL | 27-JUL-20 18:00:35.299048 +05:30
> >  idle  |  DISCARD ALL | 27-JUL-20 18:00:35.298708 +05:30
> >  idle  |  DISCARD ALL | 27-JUL-20 18:00:35.296888 +05:30
> >
> > Thanks and Regards,
> > Nikhil
> >
> > On Tue, Jul 28, 2020 at 11:09 AM Tatsuo Ishii <ishii at sraoss.co.jp>
> wrote:
> >
> >> > Hi Team,
> >> >
> >> > We have set below parameters in pgpool.conf for connections:
> >> >
> >> >  - serialize_accept = on
> >> >  - client_idle_limit = 600
> >> >  - child_life_time = 0
> >> >  - child_max_connections = 10
> >> >  - connection_life_time = 600
> >> >  - num_init_children = 4000
> >> >  - max_pool = 1
> >> >
> >> > So after executing a query, as per client_idle_limit  of 5mins, idle
> >> > sessions should be disconnected after 5mins
> >>
> >> No. client_idle_limit = 5mins means, clients will be disconnected
> >> after 5 minutes idle period, not backend connections.
> >>
> >> > but we can still see a lot of
> >> > idle connections on the database from pgpool.
> >>
> >> If you want to terminate idle connections from pgpool to backend, you
> >> should use connection_life_time or child_life_time.
> >>
> >> In your case you already set 600 seconds (that is 10 minutes) to the
> >> parameters, and they would be triggered if no clients connect to
> >> pgpool within 10 minutes.
> >>
> >> Best regards,
> >> --
> >> Tatsuo Ishii
> >> SRA OSS, Inc. Japan
> >> English: http://www.sraoss.co.jp/index_en.php
> >> Japanese:http://www.sraoss.co.jp
> >>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20200801/e658b87d/attachment.htm>