[pgpool-general: 7182] Re: Idle connection in database
Nikhil Shetty
nikhil.dba04 at gmail.com
Sun Aug 9 19:11:14 JST 2020
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
>>>
>>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20200809/3959f17e/attachment-0001.html>
More information about the pgpool-general
mailing list