<div dir="ltr">Hi Tatsuo,<div><br></div><div>I want to correct my statement from previous email:</div><div><br></div><div>From ps status <b>I could not see </b>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 <br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sun, Aug 2, 2020 at 12:15 PM Nikhil Shetty <<a href="mailto:nikhil.dba04@gmail.com">nikhil.dba04@gmail.com</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"><div dir="ltr">Hi Tatsuo,<div><br></div><div>
>>
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></div><div><br></div><div>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</div><div><br></div><div>Meanwhile, I did some testing of my own for these two parameters(
client_idle_limit and connection_life_time
) and their behaviour:</div><div><br></div><br><b>Test:</b><br><br>Database IP:128.199.222.92(master)<br>Pgpool IP: 128.199.224.132<br><br><b>Scenario1:</b><div><b><br></b></div><div>max_pool=1</div><div>num_init_children=100</div><div>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><b>1. Connected to Pgpool and ran a query with \watch 1:</b><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><b>2. Checking session in database, one session is active from pgpool</b><br> Sat 01 Aug 2020 07:26:14 AM UTC (every 1s)<br><br> pid | query | usename | client_addr | count | state<br>------+-----------------------------------------+--------------+-----------------+-------+--------<br> 3420 | select count(*) from pgbench_accounts ; | enterprisedb | 128.199.224.132 | 1 | active<br>(1 row)<br><br><b>3. Stopped the query after sometime using Ctrl-C but session is still open</b><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><b>4. Checking session in database, state is now idle</b><br><br> Sat 01 Aug 2020 07:28:14 AM UTC (every 1s)<br><br> pid | query | usename | client_addr | count | state<br>------+-----------------------------------------+--------------+-----------------+-------+-------<br> 3420 | select count(*) from pgbench_accounts ; | enterprisedb | 128.199.224.132 | 1 | idle<br><br><br><b>5. Checking session in database after 2 minutes, I can still see the database session idle</b><br><br> Sat 01 Aug 2020 07:30:02 AM UTC (every 1s)<br><br> pid | query | usename | client_addr | count | state<br>------+-----------------------------------------+--------------+-----------------+-------+-------<br> 3420 | select count(*) from pgbench_accounts ; | enterprisedb | 128.199.224.132 | 1 | idle<br> <br><b>6. Checking connection on pgpool server. connection is still open from pgpool to database</b><br><br>ps -ef|grep edb<br>enterpr+ 24170 24162 0 06:51 ? 00:00:00 pgpool: enterprisedb edb 128.199.222.92(34402) idle<br><br><b>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:</b><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><b>Scenario2:</b><br><br><div>max_pool=1</div><div>num_init_children=100</div><div>serialize_accept=on</div>child_max_connections=0<br>child_life_time=0<br>connection_life_time=0<br>client_idle_limit=60<br><br><b>1. Connected to Pgpool and ran a query with \watch 1:</b><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><b>2. Checking session in database, one session is active from pgpool</b><br><br> Sat 01 Aug 2020 07:39:55 AM UTC (every 1s)<br><br> pid | query | usename | client_addr | count | state<br>-------+-----------------------------------------+--------------+-----------------+-------+--------<br> 13427 | select count(*) from pgbench_accounts ; | enterprisedb | 128.199.224.132 | 1 | active<br><br><br><b>3. Stopped the query after sometime using Ctrl-C but session is still open</b><br>Sat 01 Aug 2020 07:41:39 AM UTC (every 1s)<br><br> count<br>---------<br> 6000000<br>^Cedb=#<br>edb=#<br><br><b>4. Checking session in database, state is now idle</b><br><br> Sat 01 Aug 2020 07:42:12 AM UTC (every 1s)<br><br> pid | query | usename | client_addr | count | state<br>-------+-----------------------------------------+--------------+-----------------+-------+-------<br> 13427 | select count(*) from pgbench_accounts ; | enterprisedb | 128.199.224.132 | 1 | idle<br>(1 row)<br><br><br><b>5. Checking session in database after 2 minutes, there is no session in the database.</b><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><b>6. Checking connection on pgpool server. No connection open in pgpool to database</b><br>ps -ef|grep edb<br><br><br><b>Observations:</b><br><br>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<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 9999 -h 128.199.224.132<br>enterpr+ 13198 13193 0 07:38 pts/0 00:00:00 /bin/psql.bin -p 9999 -h 128.199.224.132<br><br> <br>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 <br>on same psql session, it will first reset connection and then run the 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 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:<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,</div><div>Nikhil<br><br><br><br><div> <br></div></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">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></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">> 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>
</blockquote></div>
</blockquote></div>