[pgpool-general: 7198] Re: Idle connection in database
Nikhil Shetty
nikhil.dba04 at gmail.com
Thu Aug 13 00:32:05 JST 2020
Hi Tatsuo,
Thank you, I will check how this works.
On Tue, Aug 11, 2020 at 5:52 PM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> Hi Nikhil,
>
> > Hi Tatsuo,
> >
> > We are checking the patch internally before we go ahead and deploy in
> > production.
>
> Ok.
>
> > If I want to set only one of either connection_life_time or
> > client_idle_limit. What do you recommend? Should I set
> > client_idle_limit=120 and connection_life_time=0, do you foresee any
> > effects of these settings for connection pooling?
>
> In general connection_life_time is better because:
>
> 1. when client_idle_limit expires, pgpool needs to fork a new process,
> but connection_life_time does not.
>
> 2. when client_idle_limit expires, it looses mutiple connection pools
> (this only applicatable fro max_pool > 1 case) because the
> connection pools the process hold go away.
>
> However if you have very short and frequent sessions, more alarm
> system calls with connection_life_time are required and maybe
> client_idle_limit wins in this case.
>
> > Thank you for your time and support.
> >
> > Thanks and Regards,
> > Nikhil
> >
> > On Mon, Aug 10, 2020 at 11:58 AM Tatsuo Ishii <ishii at sraoss.co.jp>
> wrote:
> >
> >> Hi Nikhil,
> >>
> >> I have been investigating if there's any case when
> >> connection_life_time is not working. Actually it *is*. If primary node
> >> is not node 0, connection_life_time does not work. Attached is the
> >> patch to fix that. Please try, if you like.
> >>
> >> > According to your previous message, corresponding pgpool process was
> >> > not there, but PostgreSQL backend process were still running. I
> >> > suspect the backend process was waiting for TCP/IP connection was
> >> > terminated. But to know what was actually happening, I was waiting for
> >> > your response.
> >> >
> >> >>> "DISCARD ALL" and state idle. I will have to test again to check the
> >> socket
> >> >>> status of pid.I will get back on this
> >> >
> >> > What was that?
> >> >
> >> >> Hi Tatsuo,
> >> >>
> >> >>
> >> >> Ant reason why connection_idle_limit doesn't remove backend
> connection
> >> >> after time limit is crossed
> >> >>
> >> >> Thanks and Regards,
> >> >> Nikhil
> >> >>
> >> >> On Sun, Aug 2, 2020, 12:48 Nikhil Shetty <nikhil.dba04 at gmail.com>
> >> wrote:
> >> >>
> >> >>> Hi Tatsuo,
> >> >>>
> >> >>> I want to correct my statement from previous email:
> >> >>>
> >> >>> From ps status *I could not see *that the process (18190 and 18193)
> >> were
> >> >>> still present on pgpool but the backend process was still present
> with
> >> >>> "DISCARD ALL" and state idle. I will have to test again to check the
> >> socket
> >> >>> status of pid.I will get back on this
> >> >>>
> >> >>> On Sun, Aug 2, 2020 at 12:15 PM Nikhil Shetty <
> nikhil.dba04 at gmail.com>
> >> >>> wrote:
> >> >>>
> >> >>>> Hi Tatsuo,
> >> >>>>
> >> >>>> >> Assuming you executed "show pool_pools" long after 2020-08-01
> >> >>>> >> 06:21:26, that is very strange because connection_life_time
> should
> >> >>>> >> have been already expired. I wonder if pgpool tried to
> disconnect
> >> the
> >> >>>> >> connection but failed. To check what actually happend, can you
> >> check
> >> >>>> >> ps status of pgpool process 18190 and 18193?
> >> >>>>
> >> >>>> >> Also it would be nice you can examine the socket status of
> >> PostgreSQL
> >> >>>> >> backend 29321 and 29619, and pgpool socket status of process
> 18190
> >> and
> >> >>>> >> 18193?
> >> >>>>
> >> >>>> From ps status I could see that the process (18190 and 18193) were
> >> still
> >> >>>> present on pgpool. I will have to test again to check the socket
> >> status of
> >> >>>> pid.I will get back on this
> >> >>>>
> >> >>>> Meanwhile, I did some testing of my own for these two parameters(
> >> >>>> client_idle_limit and connection_life_time ) and their behaviour:
> >> >>>>
> >> >>>>
> >> >>>> *Test:*
> >> >>>>
> >> >>>> Database IP:128.199.222.92(master)
> >> >>>> Pgpool IP: 128.199.224.132
> >> >>>>
> >> >>>> *Scenario1:*
> >> >>>>
> >> >>>> max_pool=1
> >> >>>> num_init_children=100
> >> >>>> serialize_accept=on
> >> >>>> child_max_connections=0
> >> >>>> child_life_time=0
> >> >>>> connection_life_time=60
> >> >>>> client_idle_limit=0
> >> >>>>
> >> >>>> *1. Connected to Pgpool and ran a query with \watch 1:*
> >> >>>> select count(*) from pgbench_accounts ;
> >> >>>> \watch 1
> >> >>>> Sat 01 Aug 2020 07:25:54 AM UTC (every 1s)
> >> >>>>
> >> >>>> count
> >> >>>> ---------
> >> >>>> 6000000
> >> >>>>
> >> >>>> *2. Checking session in database, one session is active from
> pgpool*
> >> >>>> Sat 01 Aug 2020 07:26:14 AM UTC (every
> 1s)
> >> >>>>
> >> >>>> pid | query | usename |
> >> >>>> client_addr | count | state
> >> >>>>
> >> >>>>
> >>
> ------+-----------------------------------------+--------------+-----------------+-------+--------
> >> >>>> 3420 | select count(*) from pgbench_accounts ; | enterprisedb |
> >> >>>> 128.199.224.132 | 1 | active
> >> >>>> (1 row)
> >> >>>>
> >> >>>> *3. Stopped the query after sometime using Ctrl-C but session is
> still
> >> >>>> open*
> >> >>>> Sat 01 Aug 2020 07:27:35 AM UTC (every 1s)
> >> >>>>
> >> >>>> count
> >> >>>> ---------
> >> >>>> 6000000
> >> >>>> (1 row)
> >> >>>>
> >> >>>> ^Cedb=#
> >> >>>> edb=#
> >> >>>>
> >> >>>> *4. Checking session in database, state is now idle*
> >> >>>>
> >> >>>> Sat 01 Aug 2020 07:28:14 AM UTC (every
> 1s)
> >> >>>>
> >> >>>> pid | query | usename |
> >> >>>> client_addr | count | state
> >> >>>>
> >> >>>>
> >>
> ------+-----------------------------------------+--------------+-----------------+-------+-------
> >> >>>> 3420 | select count(*) from pgbench_accounts ; | enterprisedb |
> >> >>>> 128.199.224.132 | 1 | idle
> >> >>>>
> >> >>>>
> >> >>>> *5. Checking session in database after 2 minutes, I can still see
> the
> >> >>>> database session idle*
> >> >>>>
> >> >>>> Sat 01 Aug 2020 07:30:02 AM UTC (every
> 1s)
> >> >>>>
> >> >>>> pid | query | usename |
> >> >>>> client_addr | count | state
> >> >>>>
> >> >>>>
> >>
> ------+-----------------------------------------+--------------+-----------------+-------+-------
> >> >>>> 3420 | select count(*) from pgbench_accounts ; | enterprisedb |
> >> >>>> 128.199.224.132 | 1 | idle
> >> >>>>
> >> >>>> *6. Checking connection on pgpool server. connection is still open
> >> from
> >> >>>> pgpool to database*
> >> >>>>
> >> >>>> ps -ef|grep edb
> >> >>>> enterpr+ 24170 24162 0 06:51 ? 00:00:00 pgpool:
> enterprisedb
> >> edb
> >> >>>> 128.199.222.92(34402) idle
> >> >>>>
> >> >>>> *7. After being idle for more than 60 seconds, connections are
> still
> >> open
> >> >>>> from pgpool and on database.Connection is removed from pgpool
> server
> >> after
> >> >>>> I quit from the psql terminal but still present in the database as
> >> show
> >> >>>> below:*
> >> >>>> Sat 01 Aug 2020 07:34:26 AM UTC (every 1s)
> >> >>>>
> >> >>>> pid | query | usename | client_addr | count |
> state
> >> >>>>
> ------+--------------+--------------+-----------------+-------+-------
> >> >>>> 3420 | DISCARD ALL | enterprisedb | 128.199.224.132 | 1 |
> idle
> >> >>>>
> >> >>>>
> >> >>>>
> >> >>>> *Scenario2:*
> >> >>>>
> >> >>>> max_pool=1
> >> >>>> num_init_children=100
> >> >>>> serialize_accept=on
> >> >>>> child_max_connections=0
> >> >>>> child_life_time=0
> >> >>>> connection_life_time=0
> >> >>>> client_idle_limit=60
> >> >>>>
> >> >>>> *1. Connected to Pgpool and ran a query with \watch 1:*
> >> >>>> edb=# \! date
> >> >>>> Sat Aug 1 07:38:45 UTC 2020
> >> >>>> select count(*) from pgbench_accounts ;
> >> >>>> \watch 1
> >> >>>> edb=# select count(*) from pgbench_accounts ;
> >> >>>> count
> >> >>>> ---------
> >> >>>> 6000000
> >> >>>> (1 row)
> >> >>>>
> >> >>>>
> >> >>>> *2. Checking session in database, one session is active from
> pgpool*
> >> >>>>
> >> >>>> Sat 01 Aug 2020 07:39:55 AM UTC (every
> 1s)
> >> >>>>
> >> >>>> pid | query | usename |
> >> >>>> client_addr | count | state
> >> >>>>
> >> >>>>
> >>
> -------+-----------------------------------------+--------------+-----------------+-------+--------
> >> >>>> 13427 | select count(*) from pgbench_accounts ; | enterprisedb |
> >> >>>> 128.199.224.132 | 1 | active
> >> >>>>
> >> >>>>
> >> >>>> *3. Stopped the query after sometime using Ctrl-C but session is
> still
> >> >>>> open*
> >> >>>> Sat 01 Aug 2020 07:41:39 AM UTC (every 1s)
> >> >>>>
> >> >>>> count
> >> >>>> ---------
> >> >>>> 6000000
> >> >>>> ^Cedb=#
> >> >>>> edb=#
> >> >>>>
> >> >>>> *4. Checking session in database, state is now idle*
> >> >>>>
> >> >>>> Sat 01 Aug 2020 07:42:12 AM UTC (every
> 1s)
> >> >>>>
> >> >>>> pid | query | usename |
> >> >>>> client_addr | count | state
> >> >>>>
> >> >>>>
> >>
> -------+-----------------------------------------+--------------+-----------------+-------+-------
> >> >>>> 13427 | select count(*) from pgbench_accounts ; | enterprisedb |
> >> >>>> 128.199.224.132 | 1 | idle
> >> >>>> (1 row)
> >> >>>>
> >> >>>>
> >> >>>> *5. Checking session in database after 2 minutes, there is no
> session
> >> in
> >> >>>> the database.*
> >> >>>>
> >> >>>> Sat 01 Aug 2020 07:42:42 AM UTC (every 1s)
> >> >>>>
> >> >>>> pid | query | usename | client_addr | count | state
> >> >>>> -----+-------+---------+-------------+-------+-------
> >> >>>> (0 rows)
> >> >>>>
> >> >>>>
> >> >>>> *6. Checking connection on pgpool server. No connection open in
> >> pgpool to
> >> >>>> database*
> >> >>>> ps -ef|grep edb
> >> >>>>
> >> >>>>
> >> >>>> *Observations:*
> >> >>>>
> >> >>>> With client_idle_limit of 60 seconds, all connections are closed
> after
> >> >>>> being idle for more than 1 minute. I can see psql session in
> database
> >> is
> >> >>>> still present as shown below, because
> >> >>>> I did not quit from psql terminal yet:
> >> >>>>
> >> >>>> [root at master ~]# ps -ef |grep 128.199.224.132
> >> >>>> enterpr+ 13193 28563 0 07:38 pts/0 00:00:00 /bin/bash
> /bin/psql -p
> >> >>>> 9999 -h 128.199.224.132
> >> >>>> enterpr+ 13198 13193 0 07:38 pts/0 00:00:00 /bin/psql.bin -p
> 9999
> >> -h
> >> >>>> 128.199.224.132
> >> >>>>
> >> >>>>
> >> >>>> From scenario 2( client_idle_limit = 60), we can say that when
> >> >>>> client_idle_limit is triggered client is disconnected, no database
> >> >>>> connection is in use but the session still remains on server.If i
> >> start
> >> >>>> running query
> >> >>>> on same psql session, it will first reset connection and then run
> the
> >> >>>> query, this will again create a new connection to the database.
> >> >>>>
> >> >>>> From scenario 1(connection_life_time = 60), there is no real
> benefit I
> >> >>>> see. I assumed the connections(DISCARD ALL) present in database
> will
> >> be
> >> >>>> reused but each time I connected to the database, it created a new
> >> >>>> connection as seen below:
> >> >>>>
> >> >>>> pid | query | usename | client_addr | count |
> state
> >> >>>>
> >> -------+--------------+--------------+-----------------+-------+-------
> >> >>>> 12895 | | enterprisedb | 128.199.224.132 | 1 |
> idle
> >> >>>> 3420 | DISCARD ALL | enterprisedb | 128.199.224.132 | 1 |
> idle
> >> >>>> 12531 | DISCARD ALL | enterprisedb | 128.199.224.132 | 1 |
> idle
> >> >>>> 12636 | DISCARD ALL | enterprisedb | 128.199.224.132 | 1 |
> idle
> >> >>>> 12698 | DISCARD ALL | enterprisedb | 128.199.224.132 | 1 |
> idle
> >> >>>> 12751 | DISCARD ALL | enterprisedb | 128.199.224.132 | 1 |
> idle
> >> >>>> 12773 | DISCARD ALL | enterprisedb | 128.199.224.132 | 1 |
> idle
> >> >>>> 12862 | DISCARD ALL | enterprisedb | 128.199.224.132 | 1 |
> idle
> >> >>>> 12878 | DISCARD ALL | enterprisedb | 128.199.224.132 | 1 |
> idle
> >> >>>> (9 rows)
> >> >>>>
> >> >>>>
> >> >>>> Thanks and Regards,
> >> >>>> Nikhil
> >> >>>>
> >> >>>>
> >> >>>>
> >> >>>>
> >> >>>>
> >> >>>> On Sat, Aug 1, 2020 at 6:45 PM Tatsuo Ishii <ishii at sraoss.co.jp>
> >> wrote:
> >> >>>>
> >> >>>>> > 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
> >> >>>>>
> >> >>>>> Assuming you executed "show pool_pools" long after 2020-08-01
> >> >>>>> 06:21:26, that is very strange because connection_life_time should
> >> >>>>> have been already expired. I wonder if pgpool tried to disconnect
> the
> >> >>>>> connection but failed. To check what actually happend, can you
> check
> >> >>>>> ps status of pgpool process 18190 and 18193?
> >> >>>>>
> >> >>>>> Also it would be nice you can examine the socket status of
> PostgreSQL
> >> >>>>> backend 29321 and 29619, and pgpool socket status of process 18190
> >> and
> >> >>>>> 18193?
> >> >>>>>
> >> >>>>> Best regards,
> >> >>>>> --
> >> >>>>> Tatsuo Ishii
> >> >>>>> SRA OSS, Inc. Japan
> >> >>>>> English: http://www.sraoss.co.jp/index_en.php
> >> >>>>> Japanese:http://www.sraoss.co.jp
> >> >>>>>
> >> >>>>
> >> > _______________________________________________
> >> > pgpool-general mailing list
> >> > pgpool-general at pgpool.net
> >> > http://www.pgpool.net/mailman/listinfo/pgpool-general
> >>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20200812/7bfeb006/attachment-0001.html>
More information about the pgpool-general
mailing list