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

Nikhil Shetty nikhil.dba04 at gmail.com
Tue Aug 11 16:40:57 JST 2020


Hi Tatsuo,

We are checking the patch internally before we go ahead and deploy in
production.

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?

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/20200811/18b2f496/attachment-0001.html>


More information about the pgpool-general mailing list