<div dir="ltr">Hi Tatsuo,<div><br></div><div>>You need to compare the number before/after serialize_accept is enabled.<br><br>>Another point, although not directly related to serialize_accept, you<br>>must be carefull that pgbench does not eat all CPU resource. If so,<br>>that could be a bottle neck of the number. In that case, you can run<br>>multiple pgbench sessions on different machines and sum up the TPS<br>>number.<span class="gmail-im" style="color:rgb(80,0,80)"><br><br>Thank you, I will check on this</span></div><div><span class="gmail-im" style="color:rgb(80,0,80)"><br>> 2. Why does load average goes high if serialize_accept is off, is it<br>> because of the thundering herd problem as mentioned in documentation<br></span>>Yes, exactly. <br></div><div><br></div><div>ok.this means we have no choice but to enable serialize_accept.</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Sep 2, 2020 at 7:27 AM Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Hi Nikhil,<br>
<br>
> Hi Tatsuo,<br>
> <br>
> Thank you for the update, I will check and try to do it from my end if<br>
> possible.<br>
> <br>
> I had one last query before closing this, I want to understand when it is<br>
> advisable to enable serialize_accept. In my case, we are expecting around<br>
> 1k concurrent connections to the database nad I have gone through document "<br>
> <a href="https://www.pgpool.net/docs/latest/en/html/runtime-config-connection-pooling.html" rel="noreferrer" target="_blank">https://www.pgpool.net/docs/latest/en/html/runtime-config-connection-pooling.html</a>"<br>
> to run pgbench and check the value for "including connections<br>
> establishing". I ran the test but i have no idea what value to look for<br>
> ,below is my test result. I have enabled serialize_accept because when<br>
> these many connections come to database my pgpool server has a stable load<br>
> average. When serialize_accept is disabled, load average on pgpool server<br>
> goes beyond 1000 which is not acceptable for production workload<br>
> <br>
> [enterprisedb@drplpocppr1 ~]$ pgbench -n -S -h 192.168.16.184 -p 9100 -c<br>
> 1000 -C -S -T 300 db1<br>
> Password:<br>
> transaction type: multiple scripts<br>
> scaling factor: 10<br>
> query mode: simple<br>
> number of clients: 1000<br>
> number of threads: 1<br>
> duration: 300 s<br>
> number of transactions actually processed: 9223<br>
> latency average = 32534.298 ms<br>
> tps = 30.736794 (including connections establishing)<br>
> tps = 30.767516 (excluding connections establishing)<br>
> SQL script 1: <builtin: select only><br>
> - weight: 1 (targets 50.0% of total)<br>
> - 4598 transactions (49.9% of total, tps = 15.323406)<br>
> - latency average = 30540.832 ms<br>
> - latency stddev = 6324.742 ms<br>
> SQL script 2: <builtin: select only><br>
> - weight: 1 (targets 50.0% of total)<br>
> - 4625 transactions (50.1% of total, tps = 15.413387)<br>
> - latency average = 30745.421 ms<br>
> - latency stddev = 5910.625 ms<br>
> <br>
> 1. I would like to understand whether " 30.736794 (including connections<br>
> establishing) " is a good value to enable serialize_accept.<br>
<br>
You need to compare the number before/after serialize_accept is enabled.<br>
<br>
Another point, although not directly related to serialize_accept, you<br>
must be carefull that pgbench does not eat all CPU resource. If so,<br>
that could be a bottle neck of the number. In that case, you can run<br>
multiple pgbench sessions on different machines and sum up the TPS<br>
number.<br>
<br>
> 2. Why does load average goes high if serialize_accept is off, is it<br>
> because of the thundering herd problem as mentioned in documentation<br>
<br>
Yes, exactly.<br>
<br>
> On Tue, Sep 1, 2020 at 10:25 AM Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>> wrote:<br>
> <br>
>> > Hi Tatsuo,<br>
>> ><br>
>> > How do I apply this patch? I have installed pgpool using yum install<br>
>><br>
>> You need to re-create rpm packages. If you are not sure how to do it,<br>
>> probably you'd better to wait for next rpm releases. (to find release<br>
>> schedule please visit: <a href="https://pgpool.net/mediawiki/index.php/Roadmap" rel="noreferrer" target="_blank">https://pgpool.net/mediawiki/index.php/Roadmap</a>)<br>
>><br>
>> > Thanks and Regards,<br>
>> > Nikhil<br>
>> ><br>
>> > On Mon, Aug 31, 2020 at 11:44 AM Nikhil Shetty <<a href="mailto:nikhil.dba04@gmail.com" target="_blank">nikhil.dba04@gmail.com</a>><br>
>> > wrote:<br>
>> ><br>
>> >> Hi Tatsuo,<br>
>> >><br>
>> >> Thanks for the patch.Can I test this patch for both 4.0 and 4.1 versions<br>
>> >> of Pgpool?<br>
>> >><br>
>> >> Thanks and Regards,<br>
>> >> Nikhil<br>
>> >><br>
>> >> On Mon, Aug 31, 2020 at 7:02 AM Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>><br>
>> wrote:<br>
>> >><br>
>> >>> Ok, attached is a new version of patch. I confirmed that performance<br>
>> >>> was not changed.<br>
>> >>><br>
>> >>> > Wait. It appears that the patch has a drawback: serious performance<br>
>> >>> > degradation.<br>
>> >>> ><br>
>> >>> > pgbench -c 10 -n -p 11000 -C -t 10 test<br>
>> >>> ><br>
>> >>> > With connection_life_time = 10<br>
>> >>> > tps = 3.316286 (including connections establishing)<br>
>> >>> > tps = 3.680803 (excluding connections establishing)<br>
>> >>> ><br>
>> >>> > Without connection_life_time = 10<br>
>> >>> > tps = 269.660238 (including connections establishing)<br>
>> >>> > tps = 280.479690 (excluding connections establishing)<br>
>> >>> ><br>
>> >>> > Let me think if I coould enhance this...<br>
>> >>> ><br>
>> >>> >> Hi Nikhil,<br>
>> >>> >><br>
>> >>> >> Ok, here is a patch to fix the problem.<br>
>> >>> >><br>
>> >>> >> If serialize_accept is enabled, pgpool process tries to acquire<br>
>> >>> >> semaphore locking so that there's only one process which can issue<br>
>> >>> >> accept(2). Unfortunately if connection_life_time is enabled, an<br>
>> alarm<br>
>> >>> >> is set right before the semaphore locking. So when the alarm fires,<br>
>> >>> >> nothing happens because the process is waiting for semaphore<br>
>> locking.<br>
>> >>> >><br>
>> >>> >> To fix this, "the alarm has been set" flag is added. If the flag is<br>
>> >>> >> set, keep on checking for connection_life_time (plus margin) seconds<br>
>> >>> >> until the alarm is fired, then expires the connection to backend.<br>
>> >>> >><br>
>> >>> >> Patch attached.<br>
>> >>> >><br>
>> >>> >>>> Hi Tatsuo,<br>
>> >>> >>>><br>
>> >>> >>>> I was thinking on that line initially that may be because of<br>
>> >>> >>>> serialize_accept it is not working but I didn't dwell into it much<br>
>> >>> and<br>
>> >>> >>>> didn't test it.<br>
>> >>> >>>><br>
>> >>> >>>> Thanks for your input Tatsuo, you have been of immense help to<br>
>> find<br>
>> >>> this<br>
>> >>> >>>> problem.<br>
>> >>> >>>><br>
>> >>> >>>> Will this fix, when it is done, be included in all 4.0 and 4.1<br>
>> >>> versions?<br>
>> >>> >>><br>
>> >>> >>> Not sure if there's actually a fix, but if there were a fix for<br>
>> this,<br>
>> >>> >>> it will be definitely included in 4.0 and 4.1 branches.<br>
>> >>> >>><br>
>> >>> >>>> On Sun, Aug 30, 2020, 14:08 Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>><br>
>> wrote:<br>
>> >>> >>>><br>
>> >>> >>>>> Ok, I found that if following is set:<br>
>> >>> >>>>><br>
>> >>> >>>>> serialize_accept=on<br>
>> >>> >>>>><br>
>> >>> >>>>> then, connection_life_time does not work. (The alarm is not<br>
>> >>> >>>>> fired). Don't know why but I am going to fix this.<br>
>> >>> >>>>><br>
>> >>> >>>>> > Hi Tatsuo,<br>
>> >>> >>>>> ><br>
>> >>> >>>>> > I am using Pgpool v4.1.1.<br>
>> >>> >>>>> ><br>
>> >>> >>>>> > I have seen similiar behaviour in v4.0.9 as well.<br>
>> >>> >>>>> ><br>
>> >>> >>>>> > Thanks and Regards,<br>
>> >>> >>>>> > Nikhil<br>
>> >>> >>>>> ><br>
>> >>> >>>>> > On Sun, Aug 30, 2020, 13:11 Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>><br>
>> >>> wrote:<br>
>> >>> >>>>> ><br>
>> >>> >>>>> >> Ok, I have tried with your test data and pgpool settings.<br>
>> Also I<br>
>> >>> added<br>
>> >>> >>>>> >> log_disconnections to postgresql.conf so that when Pgpool-II<br>
>> >>> >>>>> >> disconnects backend. However I was not able to reproduce your<br>
>> >>> >>>>> >> problem. Pgpool-II worked as expected (10 seconds after the<br>
>> test<br>
>> >>> >>>>> >> script ends, connection was terminated on all backends).<br>
>> >>> >>>>> >><br>
>> >>> >>>>> >> What version of Pgpool-II are you using?<br>
>> >>> >>>>> >><br>
>> >>> >>>>> >> > Hi Tatsuo,<br>
>> >>> >>>>> >> ><br>
>> >>> >>>>> >> ><br>
>> >>> >>>>> >> > No, because my primary has node ID 0 and standby has node<br>
>> ID 1.<br>
>> >>> >>>>> >> ><br>
>> >>> >>>>> >> > Patch will help if my primary node ID is not 0 rite?<br>
>> >>> >>>>> >> ><br>
>> >>> >>>>> >> > Thanks and Regards,<br>
>> >>> >>>>> >> > Nikhil<br>
>> >>> >>>>> >> ><br>
>> >>> >>>>> >> ><br>
>> >>> >>>>> >> > On Sun, Aug 30, 2020, 12:19 Tatsuo Ishii <<br>
>> <a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>><br>
>> >>> wrote:<br>
>> >>> >>>>> >> ><br>
>> >>> >>>>> >> >> Before these tests, have applied the patch?<br>
>> >>> >>>>> >> >><br>
>> >>> >>>>> >> >> > Hi Tatsuo,<br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> > I have to reopen this issue because after a lot of<br>
>> >>> debugging and<br>
>> >>> >>>>> >> reading<br>
>> >>> >>>>> >> >> > documentation on connection_life_time though there is not<br>
>> >>> much of<br>
>> >>> >>>>> it,<br>
>> >>> >>>>> >> >> this<br>
>> >>> >>>>> >> >> > parameter doesn't work as it should.<br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> > First I want to be correct in my understanding that this<br>
>> >>> parameter<br>
>> >>> >>>>> >> >> > terminates cached backend connections in the<br>
>> database.So, I<br>
>> >>> test a<br>
>> >>> >>>>> >> simple<br>
>> >>> >>>>> >> >> > scenario as follows:<br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> > PGPOOL - 128.199.224.132<br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> > *Parameters set for pooling*<br>
>> >>> >>>>> >> >> > serialize_accept=on<br>
>> >>> >>>>> >> >> > child_life_time=0<br>
>> >>> >>>>> >> >> > child_max_connections=0<br>
>> >>> >>>>> >> >> > client_idle_limit=0<br>
>> >>> >>>>> >> >> > connection_life_time=10<br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> > *I am running a mixed.sql file which contain below<br>
>> >>> statements*<br>
>> >>> >>>>> >> >> > BEGIN;<br>
>> >>> >>>>> >> >> > SELECT CURRENT_TIMESTAMP;<br>
>> >>> >>>>> >> >> > INSERT into t1 values(3);<br>
>> >>> >>>>> >> >> > INSERT into t1 values(4);<br>
>> >>> >>>>> >> >> > select pg_sleep(5);<br>
>> >>> >>>>> >> >> > INSERT into t1 values(5);<br>
>> >>> >>>>> >> >> > UPDATE t1 set id=4 where id=5;<br>
>> >>> >>>>> >> >> > END;<br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> > After executing some inserts it will sleep for 5 seconds<br>
>> >>> and then<br>
>> >>> >>>>> >> execute<br>
>> >>> >>>>> >> >> > one update and an insert before closing the connection.<br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> > *1) Status of nodes*<br>
>> >>> >>>>> >> >> > -bash-4.2$ psql -h 128.199.224.132 -p 9999 -U<br>
>> enterprisedb<br>
>> >>> >>>>> >> >> > psql.bin (11.6.13)<br>
>> >>> >>>>> >> >> > Type "help" for help.<br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> > edb=# show pool_nodes;<br>
>> >>> >>>>> >> >> > node_id | hostname | port | status | lb_weight |<br>
>> >>> role |<br>
>> >>> >>>>> >> >> > select_cnt | load_balance_node | replication_delay |<br>
>> >>> >>>>> >> replication_state |<br>
>> >>> >>>>> >> >> > replication_syn<br>
>> >>> >>>>> >> >> > c_state | last_status_change<br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >><br>
>> >>> >>>>> >><br>
>> >>> >>>>><br>
>> >>><br>
>> ---------+-----------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+----------------<br>
>> >>> >>>>> >> >> > --------+---------------------<br>
>> >>> >>>>> >> >> > 0 | 128.199.222.92 | 5445 | up | 0.000000 |<br>
>> >>> primary |<br>
>> >>> >>>>> 0<br>
>> >>> >>>>> >> >> > | false | 0 |<br>
>> >>> |<br>
>> >>> >>>>> >> >> > | 2020-08-30 04:58:24<br>
>> >>> >>>>> >> >> > 1 | 128.199.222.124 | 5445 | up | 1.000000 |<br>
>> >>> standby |<br>
>> >>> >>>>> 0<br>
>> >>> >>>>> >> >> > | true | 0 |<br>
>> >>> |<br>
>> >>> >>>>> >> >> > | 2020-08-30 04:58:24<br>
>> >>> >>>>> >> >> > (2 rows)<br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> > *2) Running the mixed.sql file*<br>
>> >>> >>>>> >> >> > -bash-4.2$ psql -h 128.199.224.132 -p 9999 -U<br>
>> enterprisedb<br>
>> >>> -f<br>
>> >>> >>>>> >> mixed.sql<br>
>> >>> >>>>> >> >> > BEGIN<br>
>> >>> >>>>> >> >> > current_timestamp<br>
>> >>> >>>>> >> >> > ----------------------------------<br>
>> >>> >>>>> >> >> > 30-AUG-20 10:31:47.396527 +05:30<br>
>> >>> >>>>> >> >> > (1 row)<br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> > INSERT 0 1<br>
>> >>> >>>>> >> >> > INSERT 0 1<br>
>> >>> >>>>> >> >> > pg_sleep<br>
>> >>> >>>>> >> >> > ----------<br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> > (1 row)<br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> > INSERT 0 1<br>
>> >>> >>>>> >> >> > UPDATE 1<br>
>> >>> >>>>> >> >> > COMMIT<br>
>> >>> >>>>> >> >> > -bash-4.2$<br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> > *3) pool_pools output shows pgpool (pid - 31560) created<br>
>> one<br>
>> >>> >>>>> >> connection<br>
>> >>> >>>>> >> >> to<br>
>> >>> >>>>> >> >> > master(pid - 31550) and one to standby (pid - 28954) *<br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> > edb=# show pool_pools;<br>
>> >>> >>>>> >> >> > pool_pid | start_time | pool_id | backend_id |<br>
>> >>> database |<br>
>> >>> >>>>> >> >> > username | create_time | majorversion |<br>
>> >>> minorversion |<br>
>> >>> >>>>> >> >> > pool_counter | pool_<br>
>> >>> >>>>> >> >> > backendpid | pool_connected<br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >><br>
>> >>> >>>>> >><br>
>> >>> >>>>><br>
>> >>><br>
>> ----------+---------------------+---------+------------+----------+--------------+---------------------+--------------+--------------+--------------+------<br>
>> >>> >>>>> >> >> > -----------+----------------<br>
>> >>> >>>>> >> >> > 31560 | 2020-08-30 04:51:54 | 0 | 0 |<br>
>> >>> edb |<br>
>> >>> >>>>> >> >> > enterprisedb | 2020-08-30 05:01:47 | 3 | 0<br>
>> >>> |<br>
>> >>> >>>>> 1<br>
>> >>> >>>>> >> >> > | 31550<br>
>> >>> >>>>> >> >> > | 0<br>
>> >>> >>>>> >> >> > 31560 | 2020-08-30 04:51:54 | 0 | 1 |<br>
>> >>> edb |<br>
>> >>> >>>>> >> >> > enterprisedb | 2020-08-30 05:01:47 | 3 | 0<br>
>> >>> |<br>
>> >>> >>>>> 1<br>
>> >>> >>>>> >> >> > | 28954<br>
>> >>> >>>>> >> >> > | 0<br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> > *4) Status of process id 31550 on master.After the run,<br>
>> as<br>
>> >>> seen<br>
>> >>> >>>>> below<br>
>> >>> >>>>> >> on<br>
>> >>> >>>>> >> >> > the database is idle*<br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> > -bash-4.2$ ps -ef |grep 224.132<br>
>> >>> >>>>> >> >> > enterpr+ 31550 9148 0 05:01 ? 00:00:00 postgres:<br>
>> >>> >>>>> enterprisedb<br>
>> >>> >>>>> >> >> edb<br>
>> >>> >>>>> >> >> > 128.199.224.132[49656] idle<br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> > *5) Status of processid 31560 on pgpool.* *After the run,<br>
>> >>> as seen<br>
>> >>> >>>>> >> below<br>
>> >>> >>>>> >> >> > pgpool has released connection as soon as transaction<br>
>> >>> completes*<br>
>> >>> >>>>> >> >> > [root@pgpool-p pgpool4.1]# ps -ef |grep 31560<br>
>> >>> >>>>> >> >> > enterpr+ 31560 31557 0 04:51 ? 00:00:00 pgpool:<br>
>> >>> wait for<br>
>> >>> >>>>> >> accept<br>
>> >>> >>>>> >> >> lock<br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> > *6) After 10 seconds, this cached connection on database<br>
>> >>> should<br>
>> >>> >>>>> have<br>
>> >>> >>>>> >> been<br>
>> >>> >>>>> >> >> > terminated as per my understanding of<br>
>> connection_life_time<br>
>> >>> >>>>> parameter<br>
>> >>> >>>>> >> but<br>
>> >>> >>>>> >> >> as<br>
>> >>> >>>>> >> >> > seen below it has not terminated*<br>
>> >>> >>>>> >> >> > edb=# select * from pg_stat_activity where client_addr<br>
>> >>> >>>>> >> >> ='128.199.224.132';<br>
>> >>> >>>>> >> >> > -[ RECORD 1 ]----+---------------------------------<br>
>> >>> >>>>> >> >> > datid | 67127<br>
>> >>> >>>>> >> >> > datname | edb<br>
>> >>> >>>>> >> >> > pid | 31550<br>
>> >>> >>>>> >> >> > usesysid | 10<br>
>> >>> >>>>> >> >> > usename | enterprisedb<br>
>> >>> >>>>> >> >> > application_name | psql.bin<br>
>> >>> >>>>> >> >> > client_addr | 128.199.224.132<br>
>> >>> >>>>> >> >> > client_hostname |<br>
>> >>> >>>>> >> >> > client_port | 49656<br>
>> >>> >>>>> >> >> > backend_start | 30-AUG-20 10:31:47.380345 +05:30<br>
>> >>> >>>>> >> >> > xact_start |<br>
>> >>> >>>>> >> >> > query_start | 30-AUG-20 10:31:52.411607 +05:30<br>
>> >>> >>>>> >> >> > state_change | 30-AUG-20 10:31:52.411714 +05:30<br>
>> >>> >>>>> >> >> > wait_event_type | Client<br>
>> >>> >>>>> >> >> > wait_event | ClientRead<br>
>> >>> >>>>> >> >> > state | idle<br>
>> >>> >>>>> >> >> > backend_xid |<br>
>> >>> >>>>> >> >> > backend_xmin |<br>
>> >>> >>>>> >> >> > query | DISCARD ALL<br>
>> >>> >>>>> >> >> > backend_type | client backend<br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> > Do not mind the timestamp, it is in IST for the above<br>
>> query.<br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> > *7) From the pgpool logs, it sets the alarm after 10 sec<br>
>> but<br>
>> >>> >>>>> doesn't<br>
>> >>> >>>>> >> >> really<br>
>> >>> >>>>> >> >> > close the connection after 10 seconds*<br>
>> >>> >>>>> >> >> > Aug 30 05:01:52 Pgpool-p pgpool[*31560*]: [252-2]<br>
>> 2020-08-30<br>
>> >>> >>>>> 05:01:52:<br>
>> >>> >>>>> >> >> pid<br>
>> >>> >>>>> >> >> > 31560: DETAIL: setting alarm after 10 seconds<br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> > Attaching pgpool logs as well.<br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> > On Wed, Aug 12, 2020 at 9:02 PM Nikhil Shetty <<br>
>> >>> >>>>> <a href="mailto:nikhil.dba04@gmail.com" target="_blank">nikhil.dba04@gmail.com</a><br>
>> >>> >>>>> >> ><br>
>> >>> >>>>> >> >> > wrote:<br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> >> Hi Tatsuo,<br>
>> >>> >>>>> >> >> >><br>
>> >>> >>>>> >> >> >> Thank you, I will check how this works.<br>
>> >>> >>>>> >> >> >><br>
>> >>> >>>>> >> >> >><br>
>> >>> >>>>> >> >> >> On Tue, Aug 11, 2020 at 5:52 PM Tatsuo Ishii <<br>
>> >>> <a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>><br>
>> >>> >>>>> >> >> wrote:<br>
>> >>> >>>>> >> >> >><br>
>> >>> >>>>> >> >> >>> Hi Nikhil,<br>
>> >>> >>>>> >> >> >>><br>
>> >>> >>>>> >> >> >>> > Hi Tatsuo,<br>
>> >>> >>>>> >> >> >>> ><br>
>> >>> >>>>> >> >> >>> > We are checking the patch internally before we go<br>
>> ahead<br>
>> >>> and<br>
>> >>> >>>>> >> deploy in<br>
>> >>> >>>>> >> >> >>> > production.<br>
>> >>> >>>>> >> >> >>><br>
>> >>> >>>>> >> >> >>> Ok.<br>
>> >>> >>>>> >> >> >>><br>
>> >>> >>>>> >> >> >>> > If I want to set only one of either<br>
>> >>> connection_life_time or<br>
>> >>> >>>>> >> >> >>> > client_idle_limit. What do you recommend? Should I<br>
>> set<br>
>> >>> >>>>> >> >> >>> > client_idle_limit=120 and connection_life_time=0, do<br>
>> >>> you<br>
>> >>> >>>>> foresee<br>
>> >>> >>>>> >> any<br>
>> >>> >>>>> >> >> >>> > effects of these settings for connection pooling?<br>
>> >>> >>>>> >> >> >>><br>
>> >>> >>>>> >> >> >>> In general connection_life_time is better because:<br>
>> >>> >>>>> >> >> >>><br>
>> >>> >>>>> >> >> >>> 1. when client_idle_limit expires, pgpool needs to<br>
>> fork a<br>
>> >>> new<br>
>> >>> >>>>> >> process,<br>
>> >>> >>>>> >> >> >>> but connection_life_time does not.<br>
>> >>> >>>>> >> >> >>><br>
>> >>> >>>>> >> >> >>> 2. when client_idle_limit expires, it looses mutiple<br>
>> >>> connection<br>
>> >>> >>>>> >> pools<br>
>> >>> >>>>> >> >> >>> (this only applicatable fro max_pool > 1 case)<br>
>> because<br>
>> >>> the<br>
>> >>> >>>>> >> >> >>> connection pools the process hold go away.<br>
>> >>> >>>>> >> >> >>><br>
>> >>> >>>>> >> >> >>> However if you have very short and frequent sessions,<br>
>> >>> more alarm<br>
>> >>> >>>>> >> >> >>> system calls with connection_life_time are required and<br>
>> >>> maybe<br>
>> >>> >>>>> >> >> >>> client_idle_limit wins in this case.<br>
>> >>> >>>>> >> >> >>><br>
>> >>> >>>>> >> >> >>> > Thank you for your time and support.<br>
>> >>> >>>>> >> >> >>> ><br>
>> >>> >>>>> >> >> >>> > Thanks and Regards,<br>
>> >>> >>>>> >> >> >>> > Nikhil<br>
>> >>> >>>>> >> >> >>> ><br>
>> >>> >>>>> >> >> >>> > On Mon, Aug 10, 2020 at 11:58 AM Tatsuo Ishii <<br>
>> >>> >>>>> <a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a><br>
>> >>> >>>>> >> ><br>
>> >>> >>>>> >> >> >>> wrote:<br>
>> >>> >>>>> >> >> >>> ><br>
>> >>> >>>>> >> >> >>> >> Hi Nikhil,<br>
>> >>> >>>>> >> >> >>> >><br>
>> >>> >>>>> >> >> >>> >> I have been investigating if there's any case when<br>
>> >>> >>>>> >> >> >>> >> connection_life_time is not working. Actually it<br>
>> *is*.<br>
>> >>> If<br>
>> >>> >>>>> primary<br>
>> >>> >>>>> >> >> node<br>
>> >>> >>>>> >> >> >>> >> is not node 0, connection_life_time does not work.<br>
>> >>> Attached is<br>
>> >>> >>>>> >> the<br>
>> >>> >>>>> >> >> >>> >> patch to fix that. Please try, if you like.<br>
>> >>> >>>>> >> >> >>> >><br>
>> >>> >>>>> >> >> >>> >> > According to your previous message, corresponding<br>
>> >>> pgpool<br>
>> >>> >>>>> >> process<br>
>> >>> >>>>> >> >> was<br>
>> >>> >>>>> >> >> >>> >> > not there, but PostgreSQL backend process were<br>
>> still<br>
>> >>> >>>>> running. I<br>
>> >>> >>>>> >> >> >>> >> > suspect the backend process was waiting for TCP/IP<br>
>> >>> >>>>> connection<br>
>> >>> >>>>> >> was<br>
>> >>> >>>>> >> >> >>> >> > terminated. But to know what was actually<br>
>> happening,<br>
>> >>> I was<br>
>> >>> >>>>> >> waiting<br>
>> >>> >>>>> >> >> >>> for<br>
>> >>> >>>>> >> >> >>> >> > your response.<br>
>> >>> >>>>> >> >> >>> >> ><br>
>> >>> >>>>> >> >> >>> >> >>> "DISCARD ALL" and state idle. I will have to<br>
>> test<br>
>> >>> again to<br>
>> >>> >>>>> >> check<br>
>> >>> >>>>> >> >> >>> the<br>
>> >>> >>>>> >> >> >>> >> socket<br>
>> >>> >>>>> >> >> >>> >> >>> status of pid.I will get back on this<br>
>> >>> >>>>> >> >> >>> >> ><br>
>> >>> >>>>> >> >> >>> >> > What was that?<br>
>> >>> >>>>> >> >> >>> >> ><br>
>> >>> >>>>> >> >> >>> >> >> Hi Tatsuo,<br>
>> >>> >>>>> >> >> >>> >> >><br>
>> >>> >>>>> >> >> >>> >> >><br>
>> >>> >>>>> >> >> >>> >> >> Ant reason why connection_idle_limit doesn't<br>
>> remove<br>
>> >>> backend<br>
>> >>> >>>>> >> >> >>> connection<br>
>> >>> >>>>> >> >> >>> >> >> after time limit is crossed<br>
>> >>> >>>>> >> >> >>> >> >><br>
>> >>> >>>>> >> >> >>> >> >> Thanks and Regards,<br>
>> >>> >>>>> >> >> >>> >> >> Nikhil<br>
>> >>> >>>>> >> >> >>> >> >><br>
>> >>> >>>>> >> >> >>> >> >> On Sun, Aug 2, 2020, 12:48 Nikhil Shetty <<br>
>> >>> >>>>> >> <a href="mailto:nikhil.dba04@gmail.com" target="_blank">nikhil.dba04@gmail.com</a><br>
>> >>> >>>>> >> >> ><br>
>> >>> >>>>> >> >> >>> >> wrote:<br>
>> >>> >>>>> >> >> >>> >> >><br>
>> >>> >>>>> >> >> >>> >> >>> Hi Tatsuo,<br>
>> >>> >>>>> >> >> >>> >> >>><br>
>> >>> >>>>> >> >> >>> >> >>> I want to correct my statement from previous<br>
>> email:<br>
>> >>> >>>>> >> >> >>> >> >>><br>
>> >>> >>>>> >> >> >>> >> >>> From ps status *I could not see *that the<br>
>> process<br>
>> >>> (18190<br>
>> >>> >>>>> and<br>
>> >>> >>>>> >> >> 18193)<br>
>> >>> >>>>> >> >> >>> >> were<br>
>> >>> >>>>> >> >> >>> >> >>> still present on pgpool but the backend process<br>
>> >>> was still<br>
>> >>> >>>>> >> >> present<br>
>> >>> >>>>> >> >> >>> with<br>
>> >>> >>>>> >> >> >>> >> >>> "DISCARD ALL" and state idle. I will have to<br>
>> test<br>
>> >>> again to<br>
>> >>> >>>>> >> check<br>
>> >>> >>>>> >> >> >>> the<br>
>> >>> >>>>> >> >> >>> >> socket<br>
>> >>> >>>>> >> >> >>> >> >>> status of pid.I will get back on this<br>
>> >>> >>>>> >> >> >>> >> >>><br>
>> >>> >>>>> >> >> >>> >> >>> On Sun, Aug 2, 2020 at 12:15 PM Nikhil Shetty <<br>
>> >>> >>>>> >> >> >>> <a href="mailto:nikhil.dba04@gmail.com" target="_blank">nikhil.dba04@gmail.com</a>><br>
>> >>> >>>>> >> >> >>> >> >>> wrote:<br>
>> >>> >>>>> >> >> >>> >> >>><br>
>> >>> >>>>> >> >> >>> >> >>>> Hi Tatsuo,<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> >> Assuming you executed "show pool_pools" long<br>
>> >>> after<br>
>> >>> >>>>> >> >> 2020-08-01<br>
>> >>> >>>>> >> >> >>> >> >>>> >> 06:21:26, that is very strange because<br>
>> >>> >>>>> >> connection_life_time<br>
>> >>> >>>>> >> >> >>> should<br>
>> >>> >>>>> >> >> >>> >> >>>> >> have been already expired. I wonder if<br>
>> pgpool<br>
>> >>> tried to<br>
>> >>> >>>>> >> >> >>> disconnect<br>
>> >>> >>>>> >> >> >>> >> the<br>
>> >>> >>>>> >> >> >>> >> >>>> >> connection but failed. To check what<br>
>> actually<br>
>> >>> happend,<br>
>> >>> >>>>> >> can<br>
>> >>> >>>>> >> >> you<br>
>> >>> >>>>> >> >> >>> >> check<br>
>> >>> >>>>> >> >> >>> >> >>>> >> ps status of pgpool process 18190 and 18193?<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> >> Also it would be nice you can examine the<br>
>> >>> socket<br>
>> >>> >>>>> status<br>
>> >>> >>>>> >> of<br>
>> >>> >>>>> >> >> >>> >> PostgreSQL<br>
>> >>> >>>>> >> >> >>> >> >>>> >> backend 29321 and 29619, and pgpool socket<br>
>> >>> status of<br>
>> >>> >>>>> >> process<br>
>> >>> >>>>> >> >> >>> 18190<br>
>> >>> >>>>> >> >> >>> >> and<br>
>> >>> >>>>> >> >> >>> >> >>>> >> 18193?<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> From ps status I could see that the process<br>
>> >>> (18190 and<br>
>> >>> >>>>> >> 18193)<br>
>> >>> >>>>> >> >> were<br>
>> >>> >>>>> >> >> >>> >> still<br>
>> >>> >>>>> >> >> >>> >> >>>> present on pgpool. I will have to test again to<br>
>> >>> check the<br>
>> >>> >>>>> >> >> socket<br>
>> >>> >>>>> >> >> >>> >> status of<br>
>> >>> >>>>> >> >> >>> >> >>>> pid.I will get back on this<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> Meanwhile, I did some testing of my own for<br>
>> these<br>
>> >>> two<br>
>> >>> >>>>> >> >> parameters(<br>
>> >>> >>>>> >> >> >>> >> >>>> client_idle_limit and connection_life_time )<br>
>> and<br>
>> >>> their<br>
>> >>> >>>>> >> >> behaviour:<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> *Test:*<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> Database IP:128.199.222.92(master)<br>
>> >>> >>>>> >> >> >>> >> >>>> Pgpool IP: 128.199.224.132<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> *Scenario1:*<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> max_pool=1<br>
>> >>> >>>>> >> >> >>> >> >>>> num_init_children=100<br>
>> >>> >>>>> >> >> >>> >> >>>> serialize_accept=on<br>
>> >>> >>>>> >> >> >>> >> >>>> child_max_connections=0<br>
>> >>> >>>>> >> >> >>> >> >>>> child_life_time=0<br>
>> >>> >>>>> >> >> >>> >> >>>> connection_life_time=60<br>
>> >>> >>>>> >> >> >>> >> >>>> client_idle_limit=0<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> *1. Connected to Pgpool and ran a query with<br>
>> >>> \watch 1:*<br>
>> >>> >>>>> >> >> >>> >> >>>> select count(*) from pgbench_accounts ;<br>
>> >>> >>>>> >> >> >>> >> >>>> \watch 1<br>
>> >>> >>>>> >> >> >>> >> >>>> Sat 01 Aug 2020 07:25:54 AM UTC (every 1s)<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> count<br>
>> >>> >>>>> >> >> >>> >> >>>> ---------<br>
>> >>> >>>>> >> >> >>> >> >>>> 6000000<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> *2. Checking session in database, one session<br>
>> is<br>
>> >>> active<br>
>> >>> >>>>> from<br>
>> >>> >>>>> >> >> >>> pgpool*<br>
>> >>> >>>>> >> >> >>> >> >>>> Sat 01 Aug 2020<br>
>> >>> 07:26:14 AM<br>
>> >>> >>>>> UTC<br>
>> >>> >>>>> >> >> >>> (every 1s)<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> pid | query<br>
>> |<br>
>> >>> >>>>> usename<br>
>> >>> >>>>> >> >> |<br>
>> >>> >>>>> >> >> >>> >> >>>> client_addr | count | state<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >><br>
>> >>> >>>>> >> >> >>><br>
>> >>> >>>>> >> >><br>
>> >>> >>>>> >><br>
>> >>> >>>>><br>
>> >>><br>
>> ------+-----------------------------------------+--------------+-----------------+-------+--------<br>
>> >>> >>>>> >> >> >>> >> >>>> 3420 | select count(*) from pgbench_accounts<br>
>> ; |<br>
>> >>> >>>>> >> enterprisedb<br>
>> >>> >>>>> >> >> |<br>
>> >>> >>>>> >> >> >>> >> >>>> 128.199.224.132 | 1 | active<br>
>> >>> >>>>> >> >> >>> >> >>>> (1 row)<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> *3. Stopped the query after sometime using<br>
>> Ctrl-C<br>
>> >>> but<br>
>> >>> >>>>> >> session<br>
>> >>> >>>>> >> >> is<br>
>> >>> >>>>> >> >> >>> still<br>
>> >>> >>>>> >> >> >>> >> >>>> open*<br>
>> >>> >>>>> >> >> >>> >> >>>> Sat 01 Aug 2020 07:27:35 AM UTC (every 1s)<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> count<br>
>> >>> >>>>> >> >> >>> >> >>>> ---------<br>
>> >>> >>>>> >> >> >>> >> >>>> 6000000<br>
>> >>> >>>>> >> >> >>> >> >>>> (1 row)<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> ^Cedb=#<br>
>> >>> >>>>> >> >> >>> >> >>>> edb=#<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> *4. Checking session in database, state is now<br>
>> >>> idle*<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> Sat 01 Aug 2020<br>
>> >>> 07:28:14 AM<br>
>> >>> >>>>> UTC<br>
>> >>> >>>>> >> >> (every<br>
>> >>> >>>>> >> >> >>> 1s)<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> pid | query<br>
>> |<br>
>> >>> >>>>> usename<br>
>> >>> >>>>> >> >> |<br>
>> >>> >>>>> >> >> >>> >> >>>> client_addr | count | state<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >><br>
>> >>> >>>>> >> >> >>><br>
>> >>> >>>>> >> >><br>
>> >>> >>>>> >><br>
>> >>> >>>>><br>
>> >>><br>
>> ------+-----------------------------------------+--------------+-----------------+-------+-------<br>
>> >>> >>>>> >> >> >>> >> >>>> 3420 | select count(*) from pgbench_accounts<br>
>> ; |<br>
>> >>> >>>>> >> enterprisedb<br>
>> >>> >>>>> >> >> |<br>
>> >>> >>>>> >> >> >>> >> >>>> 128.199.224.132 | 1 | idle<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> *5. Checking session in database after 2<br>
>> minutes,<br>
>> >>> I can<br>
>> >>> >>>>> >> still<br>
>> >>> >>>>> >> >> see<br>
>> >>> >>>>> >> >> >>> the<br>
>> >>> >>>>> >> >> >>> >> >>>> database session idle*<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> Sat 01 Aug 2020<br>
>> >>> 07:30:02 AM<br>
>> >>> >>>>> UTC<br>
>> >>> >>>>> >> >> (every<br>
>> >>> >>>>> >> >> >>> 1s)<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> pid | query<br>
>> |<br>
>> >>> >>>>> usename<br>
>> >>> >>>>> >> >> |<br>
>> >>> >>>>> >> >> >>> >> >>>> client_addr | count | state<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >><br>
>> >>> >>>>> >> >> >>><br>
>> >>> >>>>> >> >><br>
>> >>> >>>>> >><br>
>> >>> >>>>><br>
>> >>><br>
>> ------+-----------------------------------------+--------------+-----------------+-------+-------<br>
>> >>> >>>>> >> >> >>> >> >>>> 3420 | select count(*) from pgbench_accounts<br>
>> ; |<br>
>> >>> >>>>> >> enterprisedb<br>
>> >>> >>>>> >> >> |<br>
>> >>> >>>>> >> >> >>> >> >>>> 128.199.224.132 | 1 | idle<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> *6. Checking connection on pgpool server.<br>
>> >>> connection is<br>
>> >>> >>>>> >> still<br>
>> >>> >>>>> >> >> open<br>
>> >>> >>>>> >> >> >>> >> from<br>
>> >>> >>>>> >> >> >>> >> >>>> pgpool to database*<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> ps -ef|grep edb<br>
>> >>> >>>>> >> >> >>> >> >>>> enterpr+ 24170 24162 0 06:51 ? 00:00:00<br>
>> >>> pgpool:<br>
>> >>> >>>>> >> >> >>> enterprisedb<br>
>> >>> >>>>> >> >> >>> >> edb<br>
>> >>> >>>>> >> >> >>> >> >>>> 128.199.222.92(34402) idle<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> *7. After being idle for more than 60 seconds,<br>
>> >>> >>>>> connections<br>
>> >>> >>>>> >> are<br>
>> >>> >>>>> >> >> >>> still<br>
>> >>> >>>>> >> >> >>> >> open<br>
>> >>> >>>>> >> >> >>> >> >>>> from pgpool and on database.Connection is<br>
>> removed<br>
>> >>> from<br>
>> >>> >>>>> >> pgpool<br>
>> >>> >>>>> >> >> >>> server<br>
>> >>> >>>>> >> >> >>> >> after<br>
>> >>> >>>>> >> >> >>> >> >>>> I quit from the psql terminal but still present<br>
>> >>> in the<br>
>> >>> >>>>> >> >> database as<br>
>> >>> >>>>> >> >> >>> >> show<br>
>> >>> >>>>> >> >> >>> >> >>>> below:*<br>
>> >>> >>>>> >> >> >>> >> >>>> Sat 01 Aug 2020 07:34:26 AM UTC<br>
>> >>> (every 1s)<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> pid | query | usename |<br>
>> >>> client_addr |<br>
>> >>> >>>>> >> count |<br>
>> >>> >>>>> >> >> >>> state<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>><br>
>> >>> >>>>> >><br>
>> >>> ------+--------------+--------------+-----------------+-------+-------<br>
>> >>> >>>>> >> >> >>> >> >>>> 3420 | DISCARD ALL | enterprisedb |<br>
>> >>> 128.199.224.132 |<br>
>> >>> >>>>> >> 1 |<br>
>> >>> >>>>> >> >> >>> idle<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> *Scenario2:*<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> max_pool=1<br>
>> >>> >>>>> >> >> >>> >> >>>> num_init_children=100<br>
>> >>> >>>>> >> >> >>> >> >>>> serialize_accept=on<br>
>> >>> >>>>> >> >> >>> >> >>>> child_max_connections=0<br>
>> >>> >>>>> >> >> >>> >> >>>> child_life_time=0<br>
>> >>> >>>>> >> >> >>> >> >>>> connection_life_time=0<br>
>> >>> >>>>> >> >> >>> >> >>>> client_idle_limit=60<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> *1. Connected to Pgpool and ran a query with<br>
>> >>> \watch 1:*<br>
>> >>> >>>>> >> >> >>> >> >>>> edb=# \! date<br>
>> >>> >>>>> >> >> >>> >> >>>> Sat Aug 1 07:38:45 UTC 2020<br>
>> >>> >>>>> >> >> >>> >> >>>> select count(*) from pgbench_accounts ;<br>
>> >>> >>>>> >> >> >>> >> >>>> \watch 1<br>
>> >>> >>>>> >> >> >>> >> >>>> edb=# select count(*) from pgbench_accounts ;<br>
>> >>> >>>>> >> >> >>> >> >>>> count<br>
>> >>> >>>>> >> >> >>> >> >>>> ---------<br>
>> >>> >>>>> >> >> >>> >> >>>> 6000000<br>
>> >>> >>>>> >> >> >>> >> >>>> (1 row)<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> *2. Checking session in database, one session<br>
>> is<br>
>> >>> active<br>
>> >>> >>>>> from<br>
>> >>> >>>>> >> >> >>> pgpool*<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> Sat 01 Aug 2020<br>
>> >>> 07:39:55 AM<br>
>> >>> >>>>> UTC<br>
>> >>> >>>>> >> >> >>> (every 1s)<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> pid | query<br>
>> |<br>
>> >>> >>>>> usename<br>
>> >>> >>>>> >> >> |<br>
>> >>> >>>>> >> >> >>> >> >>>> client_addr | count | state<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >><br>
>> >>> >>>>> >> >> >>><br>
>> >>> >>>>> >> >><br>
>> >>> >>>>> >><br>
>> >>> >>>>><br>
>> >>><br>
>> -------+-----------------------------------------+--------------+-----------------+-------+--------<br>
>> >>> >>>>> >> >> >>> >> >>>> 13427 | select count(*) from pgbench_accounts<br>
>> ; |<br>
>> >>> >>>>> >> >> enterprisedb |<br>
>> >>> >>>>> >> >> >>> >> >>>> 128.199.224.132 | 1 | active<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> *3. Stopped the query after sometime using<br>
>> Ctrl-C<br>
>> >>> but<br>
>> >>> >>>>> >> session<br>
>> >>> >>>>> >> >> is<br>
>> >>> >>>>> >> >> >>> still<br>
>> >>> >>>>> >> >> >>> >> >>>> open*<br>
>> >>> >>>>> >> >> >>> >> >>>> Sat 01 Aug 2020 07:41:39 AM UTC (every 1s)<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> count<br>
>> >>> >>>>> >> >> >>> >> >>>> ---------<br>
>> >>> >>>>> >> >> >>> >> >>>> 6000000<br>
>> >>> >>>>> >> >> >>> >> >>>> ^Cedb=#<br>
>> >>> >>>>> >> >> >>> >> >>>> edb=#<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> *4. Checking session in database, state is now<br>
>> >>> idle*<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> Sat 01 Aug 2020<br>
>> >>> 07:42:12 AM<br>
>> >>> >>>>> UTC<br>
>> >>> >>>>> >> >> >>> (every 1s)<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> pid | query<br>
>> |<br>
>> >>> >>>>> usename<br>
>> >>> >>>>> >> >> |<br>
>> >>> >>>>> >> >> >>> >> >>>> client_addr | count | state<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >><br>
>> >>> >>>>> >> >> >>><br>
>> >>> >>>>> >> >><br>
>> >>> >>>>> >><br>
>> >>> >>>>><br>
>> >>><br>
>> -------+-----------------------------------------+--------------+-----------------+-------+-------<br>
>> >>> >>>>> >> >> >>> >> >>>> 13427 | select count(*) from pgbench_accounts<br>
>> ; |<br>
>> >>> >>>>> >> >> enterprisedb |<br>
>> >>> >>>>> >> >> >>> >> >>>> 128.199.224.132 | 1 | idle<br>
>> >>> >>>>> >> >> >>> >> >>>> (1 row)<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> *5. Checking session in database after 2<br>
>> minutes,<br>
>> >>> there<br>
>> >>> >>>>> is<br>
>> >>> >>>>> >> no<br>
>> >>> >>>>> >> >> >>> session<br>
>> >>> >>>>> >> >> >>> >> in<br>
>> >>> >>>>> >> >> >>> >> >>>> the database.*<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> Sat 01 Aug 2020 07:42:42 AM UTC (every 1s)<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> pid | query | usename | client_addr | count |<br>
>> >>> state<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> -----+-------+---------+-------------+-------+-------<br>
>> >>> >>>>> >> >> >>> >> >>>> (0 rows)<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> *6. Checking connection on pgpool server. No<br>
>> >>> connection<br>
>> >>> >>>>> >> open in<br>
>> >>> >>>>> >> >> >>> >> pgpool to<br>
>> >>> >>>>> >> >> >>> >> >>>> database*<br>
>> >>> >>>>> >> >> >>> >> >>>> ps -ef|grep edb<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> *Observations:*<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> With client_idle_limit of 60 seconds, all<br>
>> >>> connections are<br>
>> >>> >>>>> >> >> closed<br>
>> >>> >>>>> >> >> >>> after<br>
>> >>> >>>>> >> >> >>> >> >>>> being idle for more than 1 minute. I can see<br>
>> psql<br>
>> >>> >>>>> session in<br>
>> >>> >>>>> >> >> >>> database<br>
>> >>> >>>>> >> >> >>> >> is<br>
>> >>> >>>>> >> >> >>> >> >>>> still present as shown below, because<br>
>> >>> >>>>> >> >> >>> >> >>>> I did not quit from psql terminal yet:<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> [root@master ~]# ps -ef |grep 128.199.224.132<br>
>> >>> >>>>> >> >> >>> >> >>>> enterpr+ 13193 28563 0 07:38 pts/0 00:00:00<br>
>> >>> /bin/bash<br>
>> >>> >>>>> >> >> >>> /bin/psql -p<br>
>> >>> >>>>> >> >> >>> >> >>>> 9999 -h 128.199.224.132<br>
>> >>> >>>>> >> >> >>> >> >>>> enterpr+ 13198 13193 0 07:38 pts/0 00:00:00<br>
>> >>> >>>>> >> /bin/psql.bin<br>
>> >>> >>>>> >> >> -p<br>
>> >>> >>>>> >> >> >>> 9999<br>
>> >>> >>>>> >> >> >>> >> -h<br>
>> >>> >>>>> >> >> >>> >> >>>> 128.199.224.132<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> From scenario 2( client_idle_limit = 60), we<br>
>> can<br>
>> >>> say that<br>
>> >>> >>>>> >> when<br>
>> >>> >>>>> >> >> >>> >> >>>> client_idle_limit is triggered client is<br>
>> >>> disconnected, no<br>
>> >>> >>>>> >> >> database<br>
>> >>> >>>>> >> >> >>> >> >>>> connection is in use but the session still<br>
>> >>> remains on<br>
>> >>> >>>>> >> >> server.If i<br>
>> >>> >>>>> >> >> >>> >> start<br>
>> >>> >>>>> >> >> >>> >> >>>> running query<br>
>> >>> >>>>> >> >> >>> >> >>>> on same psql session, it will first reset<br>
>> >>> connection and<br>
>> >>> >>>>> >> then<br>
>> >>> >>>>> >> >> run<br>
>> >>> >>>>> >> >> >>> the<br>
>> >>> >>>>> >> >> >>> >> >>>> query, this will again create a new connection<br>
>> to<br>
>> >>> the<br>
>> >>> >>>>> >> database.<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> From scenario 1(connection_life_time = 60),<br>
>> there<br>
>> >>> is no<br>
>> >>> >>>>> real<br>
>> >>> >>>>> >> >> >>> benefit I<br>
>> >>> >>>>> >> >> >>> >> >>>> see. I assumed the connections(DISCARD ALL)<br>
>> >>> present in<br>
>> >>> >>>>> >> database<br>
>> >>> >>>>> >> >> >>> will<br>
>> >>> >>>>> >> >> >>> >> be<br>
>> >>> >>>>> >> >> >>> >> >>>> reused but each time I connected to the<br>
>> database,<br>
>> >>> it<br>
>> >>> >>>>> >> created a<br>
>> >>> >>>>> >> >> new<br>
>> >>> >>>>> >> >> >>> >> >>>> connection as seen below:<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> pid | query | usename |<br>
>> >>> client_addr |<br>
>> >>> >>>>> >> count<br>
>> >>> >>>>> >> >> |<br>
>> >>> >>>>> >> >> >>> state<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >><br>
>> >>> >>>>> >> >><br>
>> >>> >>>>><br>
>> >>> -------+--------------+--------------+-----------------+-------+-------<br>
>> >>> >>>>> >> >> >>> >> >>>> 12895 | | enterprisedb |<br>
>> >>> 128.199.224.132 |<br>
>> >>> >>>>> >> 1<br>
>> >>> >>>>> >> >> |<br>
>> >>> >>>>> >> >> >>> idle<br>
>> >>> >>>>> >> >> >>> >> >>>> 3420 | DISCARD ALL | enterprisedb |<br>
>> >>> 128.199.224.132 |<br>
>> >>> >>>>> >> 1<br>
>> >>> >>>>> >> >> |<br>
>> >>> >>>>> >> >> >>> idle<br>
>> >>> >>>>> >> >> >>> >> >>>> 12531 | DISCARD ALL | enterprisedb |<br>
>> >>> 128.199.224.132 |<br>
>> >>> >>>>> >> 1<br>
>> >>> >>>>> >> >> |<br>
>> >>> >>>>> >> >> >>> idle<br>
>> >>> >>>>> >> >> >>> >> >>>> 12636 | DISCARD ALL | enterprisedb |<br>
>> >>> 128.199.224.132 |<br>
>> >>> >>>>> >> 1<br>
>> >>> >>>>> >> >> |<br>
>> >>> >>>>> >> >> >>> idle<br>
>> >>> >>>>> >> >> >>> >> >>>> 12698 | DISCARD ALL | enterprisedb |<br>
>> >>> 128.199.224.132 |<br>
>> >>> >>>>> >> 1<br>
>> >>> >>>>> >> >> |<br>
>> >>> >>>>> >> >> >>> idle<br>
>> >>> >>>>> >> >> >>> >> >>>> 12751 | DISCARD ALL | enterprisedb |<br>
>> >>> 128.199.224.132 |<br>
>> >>> >>>>> >> 1<br>
>> >>> >>>>> >> >> |<br>
>> >>> >>>>> >> >> >>> idle<br>
>> >>> >>>>> >> >> >>> >> >>>> 12773 | DISCARD ALL | enterprisedb |<br>
>> >>> 128.199.224.132 |<br>
>> >>> >>>>> >> 1<br>
>> >>> >>>>> >> >> |<br>
>> >>> >>>>> >> >> >>> idle<br>
>> >>> >>>>> >> >> >>> >> >>>> 12862 | DISCARD ALL | enterprisedb |<br>
>> >>> 128.199.224.132 |<br>
>> >>> >>>>> >> 1<br>
>> >>> >>>>> >> >> |<br>
>> >>> >>>>> >> >> >>> idle<br>
>> >>> >>>>> >> >> >>> >> >>>> 12878 | DISCARD ALL | enterprisedb |<br>
>> >>> 128.199.224.132 |<br>
>> >>> >>>>> >> 1<br>
>> >>> >>>>> >> >> |<br>
>> >>> >>>>> >> >> >>> idle<br>
>> >>> >>>>> >> >> >>> >> >>>> (9 rows)<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> Thanks and Regards,<br>
>> >>> >>>>> >> >> >>> >> >>>> Nikhil<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>> On Sat, Aug 1, 2020 at 6:45 PM Tatsuo Ishii <<br>
>> >>> >>>>> >> >> <a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>><br>
>> >>> >>>>> >> >> >>> >> wrote:<br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> >>>>> > Hi,<br>
>> >>> >>>>> >> >> >>> >> >>>>> ><br>
>> >>> >>>>> >> >> >>> >> >>>>> > Yes, we can see the pid<br>
>> >>> >>>>> >> >> >>> >> >>>>> ><br>
>> >>> >>>>> >> >> >>> >> >>>>> > show pool_pools;<br>
>> >>> >>>>> >> >> >>> >> >>>>> > pool_pid | start_time | pool_id |<br>
>> >>> backend_id<br>
>> >>> >>>>> |<br>
>> >>> >>>>> >> >> >>> database |<br>
>> >>> >>>>> >> >> >>> >> >>>>> > username | create_time |<br>
>> >>> majorversion |<br>
>> >>> >>>>> >> >> minorversion<br>
>> >>> >>>>> >> >> >>> |<br>
>> >>> >>>>> >> >> >>> >> >>>>> > pool_counter | pool_backendpid |<br>
>> pool_connected<br>
>> >>> >>>>> >> >> >>> >> >>>>> ><br>
>> >>> >>>>> >> >> >>> >> >>>>> > 18190 | 2020-08-01 06:14:41 | 0 | 1<br>
>> >>> >>>>> |<br>
>> >>> >>>>> >> edb<br>
>> >>> >>>>> >> >> >>> |<br>
>> >>> >>>>> >> >> >>> >> >>>>> > enterprisedb | 2020-08-01 06:19:52 | 3<br>
>> >>> | 0<br>
>> >>> >>>>> >> >> >>> |<br>
>> >>> >>>>> >> >> >>> >> 1<br>
>> >>> >>>>> >> >> >>> >> >>>>> > | *29321* | 0<br>
>> >>> >>>>> >> >> >>> >> >>>>> > 18193 | 2020-08-01 06:14:41 | 0 | 1<br>
>> >>> >>>>> |<br>
>> >>> >>>>> >> edb<br>
>> >>> >>>>> >> >> >>> |<br>
>> >>> >>>>> >> >> >>> >> >>>>> > enterprisedb | 2020-08-01 06:21:26 | 3<br>
>> >>> | 0<br>
>> >>> >>>>> >> >> >>> |<br>
>> >>> >>>>> >> >> >>> >> 1<br>
>> >>> >>>>> >> >> >>> >> >>>>> > | *29619 *| 0<br>
>> >>> >>>>> >> >> >>> >> >>>>> ><br>
>> >>> >>>>> >> >> >>> >> >>>>> ><br>
>> >>> >>>>> >> >> >>> >> >>>>> > select pid,query,usename,state from<br>
>> >>> pg_stat_activity<br>
>> >>> >>>>> >> where<br>
>> >>> >>>>> >> >> >>> >> >>>>> > client_addr='128.199.224.132' group by<br>
>> >>> >>>>> usename,query,pid;<br>
>> >>> >>>>> >> >> >>> >> >>>>> > pid | query | usename | state<br>
>> >>> >>>>> >> >> >>> >> >>>>> ><br>
>> -------+--------------+--------------+-------<br>
>> >>> >>>>> >> >> >>> >> >>>>> > *29321 *| DISCARD ALL | enterprisedb |<br>
>> idle<br>
>> >>> >>>>> >> >> >>> >> >>>>> > *29619* | DISCARD ALL | enterprisedb |<br>
>> idle<br>
>> >>> >>>>> >> >> >>> >> >>>>><br>
>> >>> >>>>> >> >> >>> >> >>>>> Assuming you executed "show pool_pools" long<br>
>> >>> after<br>
>> >>> >>>>> >> 2020-08-01<br>
>> >>> >>>>> >> >> >>> >> >>>>> 06:21:26, that is very strange because<br>
>> >>> >>>>> connection_life_time<br>
>> >>> >>>>> >> >> >>> should<br>
>> >>> >>>>> >> >> >>> >> >>>>> have been already expired. I wonder if pgpool<br>
>> >>> tried to<br>
>> >>> >>>>> >> >> >>> disconnect the<br>
>> >>> >>>>> >> >> >>> >> >>>>> connection but failed. To check what actually<br>
>> >>> happend,<br>
>> >>> >>>>> can<br>
>> >>> >>>>> >> you<br>
>> >>> >>>>> >> >> >>> check<br>
>> >>> >>>>> >> >> >>> >> >>>>> ps status of pgpool process 18190 and 18193?<br>
>> >>> >>>>> >> >> >>> >> >>>>><br>
>> >>> >>>>> >> >> >>> >> >>>>> Also it would be nice you can examine the<br>
>> socket<br>
>> >>> status<br>
>> >>> >>>>> of<br>
>> >>> >>>>> >> >> >>> PostgreSQL<br>
>> >>> >>>>> >> >> >>> >> >>>>> backend 29321 and 29619, and pgpool socket<br>
>> >>> status of<br>
>> >>> >>>>> >> process<br>
>> >>> >>>>> >> >> >>> 18190<br>
>> >>> >>>>> >> >> >>> >> and<br>
>> >>> >>>>> >> >> >>> >> >>>>> 18193?<br>
>> >>> >>>>> >> >> >>> >> >>>>><br>
>> >>> >>>>> >> >> >>> >> >>>>> Best regards,<br>
>> >>> >>>>> >> >> >>> >> >>>>> --<br>
>> >>> >>>>> >> >> >>> >> >>>>> Tatsuo Ishii<br>
>> >>> >>>>> >> >> >>> >> >>>>> SRA OSS, Inc. Japan<br>
>> >>> >>>>> >> >> >>> >> >>>>> English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>> >>> >>>>> >> >> >>> >> >>>>> Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
>> >>> >>>>> >> >> >>> >> >>>>><br>
>> >>> >>>>> >> >> >>> >> >>>><br>
>> >>> >>>>> >> >> >>> >> > _______________________________________________<br>
>> >>> >>>>> >> >> >>> >> > pgpool-general mailing list<br>
>> >>> >>>>> >> >> >>> >> > <a href="mailto:pgpool-general@pgpool.net" target="_blank">pgpool-general@pgpool.net</a><br>
>> >>> >>>>> >> >> >>> >> ><br>
>> >>> <a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" rel="noreferrer" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br>
>> >>> >>>>> >> >> >>> >><br>
>> >>> >>>>> >> >> >>><br>
>> >>> >>>>> >> >> >><br>
>> >>> >>>>> >> >><br>
>> >>> >>>>> >><br>
>> >>> >>>>><br>
>> >>> >>> _______________________________________________<br>
>> >>> >>> pgpool-general mailing list<br>
>> >>> >>> <a href="mailto:pgpool-general@pgpool.net" target="_blank">pgpool-general@pgpool.net</a><br>
>> >>> >>> <a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" rel="noreferrer" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br>
>> >>> > _______________________________________________<br>
>> >>> > pgpool-general mailing list<br>
>> >>> > <a href="mailto:pgpool-general@pgpool.net" target="_blank">pgpool-general@pgpool.net</a><br>
>> >>> > <a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" rel="noreferrer" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br>
>> >>><br>
>> >><br>
>><br>
</blockquote></div>