<div dir="ltr">Hi Tatsuo,<div><br></div><div>We are checking the patch internally before we go ahead and deploy in production.</div><div><br></div><div>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?</div><div><br></div><div>Thank you for your time and support.</div><div><br></div><div>Thanks and Regards,</div><div>Nikhil</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Aug 10, 2020 at 11:58 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>
I have been investigating if there's any case when<br>
connection_life_time is not working. Actually it *is*. If primary node<br>
is not node 0, connection_life_time does not work. Attached is the<br>
patch to fix that. Please try, if you like.<br>
<br>
> According to your previous message, corresponding pgpool process was<br>
> not there, but PostgreSQL backend process were still running. I<br>
> suspect the backend process was waiting for TCP/IP connection was<br>
> terminated. But to know what was actually happening, I was waiting for<br>
> your response.<br>
> <br>
>>> "DISCARD ALL" and state idle. I will have to test again to check the 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 remove backend 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 <<a href="mailto:nikhil.dba04@gmail.com" target="_blank">nikhil.dba04@gmail.com</a>> wrote:<br>
>> <br>
>>> Hi Tatsuo,<br>
>>><br>
>>> I want to correct my statement from previous email:<br>
>>><br>
>>> From ps status *I could not see *that the process (18190 and 18193) were<br>
>>> still present on pgpool but the backend process was still present with<br>
>>> "DISCARD ALL" and state idle. I will have to test again to check the socket<br>
>>> status of pid.I will get back on this<br>
>>><br>
>>> On Sun, Aug 2, 2020 at 12:15 PM Nikhil Shetty <<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 after 2020-08-01<br>
>>>> >> 06:21:26, that is very strange because connection_life_time should<br>
>>>> >> have been already expired. I wonder if pgpool tried to disconnect the<br>
>>>> >> connection but failed. To check what actually happend, can you check<br>
>>>> >> ps status of pgpool process 18190 and 18193?<br>
>>>><br>
>>>> >> Also it would be nice you can examine the socket status of PostgreSQL<br>
>>>> >> backend 29321 and 29619, and pgpool socket status of process 18190 and<br>
>>>> >> 18193?<br>
>>>><br>
>>>> From ps status I could see that the process (18190 and 18193) were still<br>
>>>> present on pgpool. I will have to test again to check the socket status of<br>
>>>> pid.I will get back on this<br>
>>>><br>
>>>> Meanwhile, I did some testing of my own for these two parameters(<br>
>>>> client_idle_limit and connection_life_time ) and their 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 \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 is active from pgpool*<br>
>>>> Sat 01 Aug 2020 07:26:14 AM UTC (every 1s)<br>
>>>><br>
>>>> pid | query | usename |<br>
>>>> client_addr | count | state<br>
>>>><br>
>>>> ------+-----------------------------------------+--------------+-----------------+-------+--------<br>
>>>> 3420 | select count(*) from pgbench_accounts ; | enterprisedb |<br>
>>>> 128.199.224.132 | 1 | active<br>
>>>> (1 row)<br>
>>>><br>
>>>> *3. Stopped the query after sometime using Ctrl-C but session is 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 idle*<br>
>>>><br>
>>>> Sat 01 Aug 2020 07:28:14 AM UTC (every 1s)<br>
>>>><br>
>>>> pid | query | usename |<br>
>>>> client_addr | count | state<br>
>>>><br>
>>>> ------+-----------------------------------------+--------------+-----------------+-------+-------<br>
>>>> 3420 | select count(*) from pgbench_accounts ; | enterprisedb |<br>
>>>> 128.199.224.132 | 1 | idle<br>
>>>><br>
>>>><br>
>>>> *5. Checking session in database after 2 minutes, I can still see the<br>
>>>> database session idle*<br>
>>>><br>
>>>> Sat 01 Aug 2020 07:30:02 AM UTC (every 1s)<br>
>>>><br>
>>>> pid | query | usename |<br>
>>>> client_addr | count | state<br>
>>>><br>
>>>> ------+-----------------------------------------+--------------+-----------------+-------+-------<br>
>>>> 3420 | select count(*) from pgbench_accounts ; | enterprisedb |<br>
>>>> 128.199.224.132 | 1 | idle<br>
>>>><br>
>>>> *6. Checking connection on pgpool server. connection is still open from<br>
>>>> pgpool to database*<br>
>>>><br>
>>>> ps -ef|grep edb<br>
>>>> enterpr+ 24170 24162 0 06:51 ? 00:00:00 pgpool: enterprisedb edb<br>
>>>> 128.199.222.92(34402) idle<br>
>>>><br>
>>>> *7. After being idle for more than 60 seconds, connections are still open<br>
>>>> from pgpool and on database.Connection is removed from pgpool server after<br>
>>>> I quit from the psql terminal but still present in the database as show<br>
>>>> below:*<br>
>>>> Sat 01 Aug 2020 07:34:26 AM UTC (every 1s)<br>
>>>><br>
>>>> pid | query | usename | client_addr | count | state<br>
>>>> ------+--------------+--------------+-----------------+-------+-------<br>
>>>> 3420 | DISCARD ALL | enterprisedb | 128.199.224.132 | 1 | 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 \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 is active from pgpool*<br>
>>>><br>
>>>> Sat 01 Aug 2020 07:39:55 AM UTC (every 1s)<br>
>>>><br>
>>>> pid | query | usename |<br>
>>>> client_addr | count | state<br>
>>>><br>
>>>> -------+-----------------------------------------+--------------+-----------------+-------+--------<br>
>>>> 13427 | select count(*) from pgbench_accounts ; | enterprisedb |<br>
>>>> 128.199.224.132 | 1 | active<br>
>>>><br>
>>>><br>
>>>> *3. Stopped the query after sometime using Ctrl-C but session is 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 idle*<br>
>>>><br>
>>>> Sat 01 Aug 2020 07:42:12 AM UTC (every 1s)<br>
>>>><br>
>>>> pid | query | usename |<br>
>>>> client_addr | count | state<br>
>>>><br>
>>>> -------+-----------------------------------------+--------------+-----------------+-------+-------<br>
>>>> 13427 | select count(*) from pgbench_accounts ; | enterprisedb |<br>
>>>> 128.199.224.132 | 1 | idle<br>
>>>> (1 row)<br>
>>>><br>
>>>><br>
>>>> *5. Checking session in database after 2 minutes, there is no session 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 | state<br>
>>>> -----+-------+---------+-------------+-------+-------<br>
>>>> (0 rows)<br>
>>>><br>
>>>><br>
>>>> *6. Checking connection on pgpool server. No connection open in pgpool to<br>
>>>> database*<br>
>>>> ps -ef|grep edb<br>
>>>><br>
>>>><br>
>>>> *Observations:*<br>
>>>><br>
>>>> With client_idle_limit of 60 seconds, all connections are closed after<br>
>>>> being idle for more than 1 minute. I can see psql session in database 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 /bin/bash /bin/psql -p<br>
>>>> 9999 -h 128.199.224.132<br>
>>>> enterpr+ 13198 13193 0 07:38 pts/0 00:00:00 /bin/psql.bin -p 9999 -h<br>
>>>> 128.199.224.132<br>
>>>><br>
>>>><br>
>>>> From scenario 2( client_idle_limit = 60), we can say that when<br>
>>>> client_idle_limit is triggered client is disconnected, no database<br>
>>>> connection is in use but the session still remains on server.If i start<br>
>>>> running query<br>
>>>> on same psql session, it will first reset connection and then run the<br>
>>>> query, this will again create a new connection to the database.<br>
>>>><br>
>>>> From scenario 1(connection_life_time = 60), there is no real benefit I<br>
>>>> see. I assumed the connections(DISCARD ALL) present in database will be<br>
>>>> reused but each time I connected to the database, it created a new<br>
>>>> connection as seen below:<br>
>>>><br>
>>>> pid | query | usename | client_addr | count | state<br>
>>>> -------+--------------+--------------+-----------------+-------+-------<br>
>>>> 12895 | | enterprisedb | 128.199.224.132 | 1 | idle<br>
>>>> 3420 | DISCARD ALL | enterprisedb | 128.199.224.132 | 1 | idle<br>
>>>> 12531 | DISCARD ALL | enterprisedb | 128.199.224.132 | 1 | idle<br>
>>>> 12636 | DISCARD ALL | enterprisedb | 128.199.224.132 | 1 | idle<br>
>>>> 12698 | DISCARD ALL | enterprisedb | 128.199.224.132 | 1 | idle<br>
>>>> 12751 | DISCARD ALL | enterprisedb | 128.199.224.132 | 1 | idle<br>
>>>> 12773 | DISCARD ALL | enterprisedb | 128.199.224.132 | 1 | idle<br>
>>>> 12862 | DISCARD ALL | enterprisedb | 128.199.224.132 | 1 | idle<br>
>>>> 12878 | DISCARD ALL | enterprisedb | 128.199.224.132 | 1 | 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 <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>> wrote:<br>
>>>><br>
>>>>> > Hi,<br>
>>>>> ><br>
>>>>> > Yes, we can see the pid<br>
>>>>> ><br>
>>>>> > show pool_pools;<br>
>>>>> > pool_pid | start_time | pool_id | backend_id | database |<br>
>>>>> > username | create_time | majorversion | minorversion |<br>
>>>>> > pool_counter | pool_backendpid | pool_connected<br>
>>>>> ><br>
>>>>> > 18190 | 2020-08-01 06:14:41 | 0 | 1 | edb |<br>
>>>>> > enterprisedb | 2020-08-01 06:19:52 | 3 | 0 | 1<br>
>>>>> > | *29321* | 0<br>
>>>>> > 18193 | 2020-08-01 06:14:41 | 0 | 1 | edb |<br>
>>>>> > enterprisedb | 2020-08-01 06:21:26 | 3 | 0 | 1<br>
>>>>> > | *29619 *| 0<br>
>>>>> ><br>
>>>>> ><br>
>>>>> > select pid,query,usename,state from pg_stat_activity where<br>
>>>>> > client_addr='128.199.224.132' group by usename,query,pid;<br>
>>>>> > pid | query | usename | state<br>
>>>>> > -------+--------------+--------------+-------<br>
>>>>> > *29321 *| DISCARD ALL | enterprisedb | idle<br>
>>>>> > *29619* | DISCARD ALL | enterprisedb | idle<br>
>>>>><br>
>>>>> Assuming you executed "show pool_pools" long after 2020-08-01<br>
>>>>> 06:21:26, that is very strange because connection_life_time should<br>
>>>>> have been already expired. I wonder if pgpool tried to disconnect the<br>
>>>>> connection but failed. To check what actually happend, can you check<br>
>>>>> ps status of pgpool process 18190 and 18193?<br>
>>>>><br>
>>>>> Also it would be nice you can examine the socket status of PostgreSQL<br>
>>>>> backend 29321 and 29619, and pgpool socket status of process 18190 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>
> <a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" rel="noreferrer" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br>
</blockquote></div>