[pgpool-general: 7184] Re: Idle connection in database
Tatsuo Ishii
ishii at sraoss.co.jp
Mon Aug 10 15:28:09 JST 2020
Hi Nikhil,
I have been investigating if there's any case when
connection_life_time is not working. Actually it *is*. If primary node
is not node 0, connection_life_time does not work. Attached is the
patch to fix that. Please try, if you like.
> 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
>>>>>
>>>>
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general
-------------- next part --------------
A non-text attachment was scrubbed...
Name: pool_connection_pool.diff
Type: text/x-patch
Size: 686 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20200810/5f9b7eca/attachment.bin>
More information about the pgpool-general
mailing list