[pgpool-general: 7183] Re: Idle connection in database
Tatsuo Ishii
ishii at sraoss.co.jp
Sun Aug 9 19:34:38 JST 2020
Hi Nikhil,
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
>>>>
>>>
More information about the pgpool-general
mailing list