[pgpool-general: 7177] Re: Idle connection in database
Nikhil Shetty
nikhil.dba04 at gmail.com
Sun Aug 2 15:45:54 JST 2020
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/20200802/dc47eae5/attachment.html>
More information about the pgpool-general
mailing list