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

Nikhil Shetty nikhil.dba04 at gmail.com
Tue Sep 1 17:24:39 JST 2020


Hi Tatsuo,

Thank you for the update, I will check and try to do it from my end if
possible.

I had one last query before closing this, I want to understand when it is
advisable to enable serialize_accept. In my case, we are expecting around
1k concurrent connections to the database nad I have gone through document "
https://www.pgpool.net/docs/latest/en/html/runtime-config-connection-pooling.html"
to run pgbench and check the value for "including connections
establishing".  I ran the test but i have no idea what value to look for
,below is my test result. I have enabled serialize_accept because when
these many connections come to database my pgpool server has a stable load
average. When serialize_accept is disabled, load average on pgpool server
goes beyond 1000 which is not acceptable for production workload

[enterprisedb at drplpocppr1 ~]$ pgbench -n -S -h 192.168.16.184 -p 9100 -c
1000 -C -S -T 300 db1
Password:
transaction type: multiple scripts
scaling factor: 10
query mode: simple
number of clients: 1000
number of threads: 1
duration: 300 s
number of transactions actually processed: 9223
latency average = 32534.298 ms
tps = 30.736794 (including connections establishing)
tps = 30.767516 (excluding connections establishing)
SQL script 1: <builtin: select only>
 - weight: 1 (targets 50.0% of total)
 - 4598 transactions (49.9% of total, tps = 15.323406)
 - latency average = 30540.832 ms
 - latency stddev = 6324.742 ms
SQL script 2: <builtin: select only>
 - weight: 1 (targets 50.0% of total)
 - 4625 transactions (50.1% of total, tps = 15.413387)
 - latency average = 30745.421 ms
 - latency stddev = 5910.625 ms

1. I would like to understand whether " 30.736794 (including connections
establishing) " is a good value to enable serialize_accept.
2. Why does load average goes high if serialize_accept is off, is it
because of the thundering herd problem as mentioned in documentation


On Tue, Sep 1, 2020 at 10:25 AM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> > Hi Tatsuo,
> >
> > How do I apply this patch? I have installed pgpool using yum install
>
> You need to re-create rpm packages. If you are not sure how to do it,
> probably you'd better to wait for next rpm releases. (to find release
> schedule please visit: https://pgpool.net/mediawiki/index.php/Roadmap)
>
> > Thanks and Regards,
> > Nikhil
> >
> > On Mon, Aug 31, 2020 at 11:44 AM Nikhil Shetty <nikhil.dba04 at gmail.com>
> > wrote:
> >
> >> Hi Tatsuo,
> >>
> >> Thanks for the patch.Can I test this patch for both 4.0 and 4.1 versions
> >> of Pgpool?
> >>
> >> Thanks and Regards,
> >> Nikhil
> >>
> >> On Mon, Aug 31, 2020 at 7:02 AM Tatsuo Ishii <ishii at sraoss.co.jp>
> wrote:
> >>
> >>> Ok, attached is a new version of patch. I confirmed that performance
> >>> was not changed.
> >>>
> >>> > Wait. It appears that the patch has a drawback: serious performance
> >>> > degradation.
> >>> >
> >>> > pgbench -c 10 -n -p 11000 -C -t 10 test
> >>> >
> >>> > With connection_life_time = 10
> >>> > tps = 3.316286 (including connections establishing)
> >>> > tps = 3.680803 (excluding connections establishing)
> >>> >
> >>> > Without connection_life_time = 10
> >>> > tps = 269.660238 (including connections establishing)
> >>> > tps = 280.479690 (excluding connections establishing)
> >>> >
> >>> > Let me think if I coould enhance this...
> >>> >
> >>> >> Hi Nikhil,
> >>> >>
> >>> >> Ok, here is a patch to fix the problem.
> >>> >>
> >>> >> If serialize_accept is enabled, pgpool process tries to acquire
> >>> >> semaphore locking so that there's only one process which can issue
> >>> >> accept(2). Unfortunately if connection_life_time is enabled, an
> alarm
> >>> >> is set right before the semaphore locking. So when the alarm fires,
> >>> >> nothing happens because the process is waiting for semaphore
> locking.
> >>> >>
> >>> >> To fix this, "the alarm has been set" flag is added. If the flag is
> >>> >> set, keep on checking for connection_life_time (plus margin) seconds
> >>> >> until the alarm is fired, then expires the connection to backend.
> >>> >>
> >>> >> Patch attached.
> >>> >>
> >>> >>>> Hi Tatsuo,
> >>> >>>>
> >>> >>>> I was thinking on that line initially that may be because of
> >>> >>>> serialize_accept it is not working but I didn't dwell into it much
> >>> and
> >>> >>>> didn't test it.
> >>> >>>>
> >>> >>>> Thanks for your input Tatsuo, you have been of immense help to
> find
> >>> this
> >>> >>>> problem.
> >>> >>>>
> >>> >>>> Will this fix, when it is done,  be included in all 4.0 and 4.1
> >>> versions?
> >>> >>>
> >>> >>> Not sure if there's actually a fix, but if there were a fix for
> this,
> >>> >>> it will be definitely included in 4.0 and 4.1 branches.
> >>> >>>
> >>> >>>> On Sun, Aug 30, 2020, 14:08 Tatsuo Ishii <ishii at sraoss.co.jp>
> wrote:
> >>> >>>>
> >>> >>>>> Ok, I found that if following is set:
> >>> >>>>>
> >>> >>>>> serialize_accept=on
> >>> >>>>>
> >>> >>>>> then, connection_life_time does not work. (The alarm is not
> >>> >>>>> fired). Don't know why but I am going to fix this.
> >>> >>>>>
> >>> >>>>> > Hi Tatsuo,
> >>> >>>>> >
> >>> >>>>> > I am using Pgpool v4.1.1.
> >>> >>>>> >
> >>> >>>>> > I have seen similiar behaviour in v4.0.9 as well.
> >>> >>>>> >
> >>> >>>>> > Thanks and Regards,
> >>> >>>>> > Nikhil
> >>> >>>>> >
> >>> >>>>> > On Sun, Aug 30, 2020, 13:11 Tatsuo Ishii <ishii at sraoss.co.jp>
> >>> wrote:
> >>> >>>>> >
> >>> >>>>> >> Ok, I have tried with your test data and pgpool settings.
> Also I
> >>> added
> >>> >>>>> >> log_disconnections to postgresql.conf so that when Pgpool-II
> >>> >>>>> >> disconnects backend.  However I was not able to reproduce your
> >>> >>>>> >> problem. Pgpool-II worked as expected (10 seconds after the
> test
> >>> >>>>> >> script ends, connection was terminated on all backends).
> >>> >>>>> >>
> >>> >>>>> >> What version of Pgpool-II are you using?
> >>> >>>>> >>
> >>> >>>>> >> > Hi Tatsuo,
> >>> >>>>> >> >
> >>> >>>>> >> >
> >>> >>>>> >> > No, because my primary has node ID 0 and standby has node
> ID 1.
> >>> >>>>> >> >
> >>> >>>>> >> > Patch will help if my primary node ID is not 0 rite?
> >>> >>>>> >> >
> >>> >>>>> >> > Thanks and Regards,
> >>> >>>>> >> > Nikhil
> >>> >>>>> >> >
> >>> >>>>> >> >
> >>> >>>>> >> > On Sun, Aug 30, 2020, 12:19 Tatsuo Ishii <
> ishii at sraoss.co.jp>
> >>> wrote:
> >>> >>>>> >> >
> >>> >>>>> >> >> Before these tests, have applied the patch?
> >>> >>>>> >> >>
> >>> >>>>> >> >> > Hi Tatsuo,
> >>> >>>>> >> >> >
> >>> >>>>> >> >> >
> >>> >>>>> >> >> > I have to reopen this issue because after a lot of
> >>> debugging and
> >>> >>>>> >> reading
> >>> >>>>> >> >> > documentation on connection_life_time though there is not
> >>> much of
> >>> >>>>> it,
> >>> >>>>> >> >> this
> >>> >>>>> >> >> > parameter doesn't work as it should.
> >>> >>>>> >> >> >
> >>> >>>>> >> >> > First I want to be correct in my understanding that this
> >>> parameter
> >>> >>>>> >> >> > terminates cached backend connections in the
> database.So, I
> >>> test a
> >>> >>>>> >> simple
> >>> >>>>> >> >> > scenario as follows:
> >>> >>>>> >> >> >
> >>> >>>>> >> >> > PGPOOL -  128.199.224.132
> >>> >>>>> >> >> >
> >>> >>>>> >> >> > *Parameters set for pooling*
> >>> >>>>> >> >> > serialize_accept=on
> >>> >>>>> >> >> > child_life_time=0
> >>> >>>>> >> >> > child_max_connections=0
> >>> >>>>> >> >> > client_idle_limit=0
> >>> >>>>> >> >> > connection_life_time=10
> >>> >>>>> >> >> >
> >>> >>>>> >> >> > *I am running a mixed.sql file which contain below
> >>> statements*
> >>> >>>>> >> >> > BEGIN;
> >>> >>>>> >> >> > SELECT CURRENT_TIMESTAMP;
> >>> >>>>> >> >> > INSERT  into t1 values(3);
> >>> >>>>> >> >> > INSERT  into t1 values(4);
> >>> >>>>> >> >> > select pg_sleep(5);
> >>> >>>>> >> >> > INSERT  into t1 values(5);
> >>> >>>>> >> >> > UPDATE t1 set id=4 where id=5;
> >>> >>>>> >> >> > END;
> >>> >>>>> >> >> >
> >>> >>>>> >> >> > After executing some inserts it will sleep for 5 seconds
> >>> and then
> >>> >>>>> >> execute
> >>> >>>>> >> >> > one update and an insert before closing the connection.
> >>> >>>>> >> >> >
> >>> >>>>> >> >> >
> >>> >>>>> >> >> > *1) Status of nodes*
> >>> >>>>> >> >> > -bash-4.2$ psql -h 128.199.224.132 -p 9999 -U
> enterprisedb
> >>> >>>>> >> >> > psql.bin (11.6.13)
> >>> >>>>> >> >> > Type "help" for help.
> >>> >>>>> >> >> >
> >>> >>>>> >> >> > edb=# show pool_nodes;
> >>> >>>>> >> >> >  node_id |    hostname     | port | status | lb_weight |
> >>> role   |
> >>> >>>>> >> >> > select_cnt | load_balance_node | replication_delay |
> >>> >>>>> >> replication_state |
> >>> >>>>> >> >> > replication_syn
> >>> >>>>> >> >> > c_state | last_status_change
> >>> >>>>> >> >> >
> >>> >>>>> >> >>
> >>> >>>>> >>
> >>> >>>>>
> >>>
> ---------+-----------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+----------------
> >>> >>>>> >> >> > --------+---------------------
> >>> >>>>> >> >> >  0       | 128.199.222.92  | 5445 | up     | 0.000000  |
> >>> primary |
> >>> >>>>> 0
> >>> >>>>> >> >> >    | false             | 0                 |
> >>>    |
> >>> >>>>> >> >> >         | 2020-08-30 04:58:24
> >>> >>>>> >> >> >  1       | 128.199.222.124 | 5445 | up     | 1.000000  |
> >>> standby |
> >>> >>>>> 0
> >>> >>>>> >> >> >    | true              | 0                 |
> >>>    |
> >>> >>>>> >> >> >         | 2020-08-30 04:58:24
> >>> >>>>> >> >> > (2 rows)
> >>> >>>>> >> >> >
> >>> >>>>> >> >> > *2) Running the mixed.sql file*
> >>> >>>>> >> >> > -bash-4.2$ psql -h 128.199.224.132 -p 9999 -U
> enterprisedb
> >>> -f
> >>> >>>>> >> mixed.sql
> >>> >>>>> >> >> > BEGIN
> >>> >>>>> >> >> >         current_timestamp
> >>> >>>>> >> >> > ----------------------------------
> >>> >>>>> >> >> >  30-AUG-20 10:31:47.396527 +05:30
> >>> >>>>> >> >> > (1 row)
> >>> >>>>> >> >> >
> >>> >>>>> >> >> > INSERT 0 1
> >>> >>>>> >> >> > INSERT 0 1
> >>> >>>>> >> >> >  pg_sleep
> >>> >>>>> >> >> > ----------
> >>> >>>>> >> >> >
> >>> >>>>> >> >> > (1 row)
> >>> >>>>> >> >> >
> >>> >>>>> >> >> > INSERT 0 1
> >>> >>>>> >> >> > UPDATE 1
> >>> >>>>> >> >> > COMMIT
> >>> >>>>> >> >> > -bash-4.2$
> >>> >>>>> >> >> >
> >>> >>>>> >> >> > *3) pool_pools output shows pgpool (pid - 31560) created
> one
> >>> >>>>> >> connection
> >>> >>>>> >> >> to
> >>> >>>>> >> >> > master(pid - 31550) and one to standby (pid - 28954) *
> >>> >>>>> >> >> >
> >>> >>>>> >> >> > edb=# show pool_pools;
> >>> >>>>> >> >> >  pool_pid |     start_time      | pool_id | backend_id |
> >>> database |
> >>> >>>>> >> >> > username   |     create_time     | majorversion |
> >>> minorversion |
> >>> >>>>> >> >> > pool_counter | pool_
> >>> >>>>> >> >> > backendpid | pool_connected
> >>> >>>>> >> >> >
> >>> >>>>> >> >>
> >>> >>>>> >>
> >>> >>>>>
> >>>
> ----------+---------------------+---------+------------+----------+--------------+---------------------+--------------+--------------+--------------+------
> >>> >>>>> >> >> > -----------+----------------
> >>> >>>>> >> >> >  31560    | 2020-08-30 04:51:54 | 0       | 0          |
> >>> edb      |
> >>> >>>>> >> >> > enterprisedb | 2020-08-30 05:01:47 | 3            | 0
> >>>       |
> >>> >>>>> 1
> >>> >>>>> >> >> >      | 31550
> >>> >>>>> >> >> >            | 0
> >>> >>>>> >> >> >  31560    | 2020-08-30 04:51:54 | 0       | 1          |
> >>> edb      |
> >>> >>>>> >> >> > enterprisedb | 2020-08-30 05:01:47 | 3            | 0
> >>>       |
> >>> >>>>> 1
> >>> >>>>> >> >> >      | 28954
> >>> >>>>> >> >> >            | 0
> >>> >>>>> >> >> >
> >>> >>>>> >> >> > *4) Status of process id 31550 on master.After the run,
> as
> >>> seen
> >>> >>>>> below
> >>> >>>>> >> on
> >>> >>>>> >> >> > the database is idle*
> >>> >>>>> >> >> >
> >>> >>>>> >> >> > -bash-4.2$ ps -ef |grep 224.132
> >>> >>>>> >> >> > enterpr+ 31550  9148  0 05:01 ?        00:00:00 postgres:
> >>> >>>>> enterprisedb
> >>> >>>>> >> >> edb
> >>> >>>>> >> >> > 128.199.224.132[49656] idle
> >>> >>>>> >> >> >
> >>> >>>>> >> >> > *5) Status of processid 31560 on pgpool.* *After the run,
> >>> as seen
> >>> >>>>> >> below
> >>> >>>>> >> >> > pgpool has released connection as soon as transaction
> >>> completes*
> >>> >>>>> >> >> > [root at pgpool-p pgpool4.1]# ps -ef |grep 31560
> >>> >>>>> >> >> > enterpr+ 31560 31557  0 04:51 ?        00:00:00 pgpool:
> >>> wait for
> >>> >>>>> >> accept
> >>> >>>>> >> >> lock
> >>> >>>>> >> >> >
> >>> >>>>> >> >> >
> >>> >>>>> >> >> > *6) After 10 seconds, this cached connection on database
> >>> should
> >>> >>>>> have
> >>> >>>>> >> been
> >>> >>>>> >> >> > terminated as per my understanding of
> connection_life_time
> >>> >>>>> parameter
> >>> >>>>> >> but
> >>> >>>>> >> >> as
> >>> >>>>> >> >> > seen below it has not terminated*
> >>> >>>>> >> >> > edb=# select * from pg_stat_activity where client_addr
> >>> >>>>> >> >> ='128.199.224.132';
> >>> >>>>> >> >> > -[ RECORD 1 ]----+---------------------------------
> >>> >>>>> >> >> > datid            | 67127
> >>> >>>>> >> >> > datname          | edb
> >>> >>>>> >> >> > pid              | 31550
> >>> >>>>> >> >> > usesysid         | 10
> >>> >>>>> >> >> > usename          | enterprisedb
> >>> >>>>> >> >> > application_name | psql.bin
> >>> >>>>> >> >> > client_addr      | 128.199.224.132
> >>> >>>>> >> >> > client_hostname  |
> >>> >>>>> >> >> > client_port      | 49656
> >>> >>>>> >> >> > backend_start    | 30-AUG-20 10:31:47.380345 +05:30
> >>> >>>>> >> >> > xact_start       |
> >>> >>>>> >> >> > query_start      | 30-AUG-20 10:31:52.411607 +05:30
> >>> >>>>> >> >> > state_change     | 30-AUG-20 10:31:52.411714 +05:30
> >>> >>>>> >> >> > wait_event_type  | Client
> >>> >>>>> >> >> > wait_event       | ClientRead
> >>> >>>>> >> >> > state            | idle
> >>> >>>>> >> >> > backend_xid      |
> >>> >>>>> >> >> > backend_xmin     |
> >>> >>>>> >> >> > query            |  DISCARD ALL
> >>> >>>>> >> >> > backend_type     | client backend
> >>> >>>>> >> >> >
> >>> >>>>> >> >> > Do not mind the timestamp, it is in IST for the above
> query.
> >>> >>>>> >> >> >
> >>> >>>>> >> >> > *7) From the pgpool logs, it sets the alarm after 10 sec
> but
> >>> >>>>> doesn't
> >>> >>>>> >> >> really
> >>> >>>>> >> >> > close the connection after 10 seconds*
> >>> >>>>> >> >> > Aug 30 05:01:52 Pgpool-p pgpool[*31560*]: [252-2]
> 2020-08-30
> >>> >>>>> 05:01:52:
> >>> >>>>> >> >> pid
> >>> >>>>> >> >> > 31560: DETAIL:  setting alarm after 10 seconds
> >>> >>>>> >> >> >
> >>> >>>>> >> >> > Attaching pgpool logs as well.
> >>> >>>>> >> >> >
> >>> >>>>> >> >> >
> >>> >>>>> >> >> > On Wed, Aug 12, 2020 at 9:02 PM Nikhil Shetty <
> >>> >>>>> nikhil.dba04 at gmail.com
> >>> >>>>> >> >
> >>> >>>>> >> >> > wrote:
> >>> >>>>> >> >> >
> >>> >>>>> >> >> >> 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
> >>> >>>>> >> >> >>> >>
> >>> >>>>> >> >> >>>
> >>> >>>>> >> >> >>
> >>> >>>>> >> >>
> >>> >>>>> >>
> >>> >>>>>
> >>> >>> _______________________________________________
> >>> >>> pgpool-general mailing list
> >>> >>> pgpool-general at pgpool.net
> >>> >>> http://www.pgpool.net/mailman/listinfo/pgpool-general
> >>> > _______________________________________________
> >>> > 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/20200901/53ea9b24/attachment-0001.html>


More information about the pgpool-general mailing list