[pgpool-general: 7189] Re: Idle connection in database
Tatsuo Ishii
ishii at sraoss.co.jp
Tue Aug 11 21:22:56 JST 2020
Hi Nikhil,
> Hi Tatsuo,
>
> We are checking the patch internally before we go ahead and deploy in
> production.
Ok.
> 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?
In general connection_life_time is better because:
1. when client_idle_limit expires, pgpool needs to fork a new process,
but connection_life_time does not.
2. when client_idle_limit expires, it looses mutiple connection pools
(this only applicatable fro max_pool > 1 case) because the
connection pools the process hold go away.
However if you have very short and frequent sessions, more alarm
system calls with connection_life_time are required and maybe
client_idle_limit wins in this case.
> Thank you for your time and support.
>
> Thanks and Regards,
> Nikhil
>
> On Mon, Aug 10, 2020 at 11:58 AM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>
>> 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
>>
More information about the pgpool-general
mailing list