[pgpool-general: 4333] Re: Connection Pool

Tatsuo Ishii ishii at postgresql.org
Mon Jan 18 11:30:48 JST 2016


>  Hi Tatsuo,
> Attached is the pgpool.conf file that I used.  Please review and let me know how I can prevent the connection pool issue below.
> Kind Regards,Derek.
> 
> 
>     On Friday, 15 January 2016, 13:09, Derek <derek_kouch at yahoo.com.au> wrote:
>  
> 
>  I'll try to send early next week.
> 
> Kind regards,
> Derek.
> 
>> On 15 Jan 2016, at 11:15 AM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>> 
>> Connection pooling behavior depends on pgpool.conf.  Can we share it?
>> 
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp/
>> 
>>> Hi,
>>> 
>>> I'm trying to understand how the connection pool works. Whether I'm getting a connection leak.
>>> 
>>> Pgpool v3.4
>>> Postgres v9.3.10
>>> Rhel v7.1
>>> 
>>> I have 2 applications, the first application A monitors postgres via pgpool. It connects and disconnects continuously every few minutes. The second is an application B that starts up in the morning and stays connected for the whole day, and shutdowns down at night. Both applications use different user accounts to log into Postgres.
>>> 
>>> What I've noticed is that in pgpool admin it shows that there are processes that share the same connection even though the username/database/client ip connection combo is different. Though one shows front end connected (app B) and the other is disconnected (app A). Is this ok?
>>> 
>>> On the pgpool box, it only shows the connected application B in the list of processes.

Yes, it makes sense.

It is possible that a pgpool process holds connections to backend with
different username/database pair, up to 4, because you set max_pool to
4.

However no more than 1 client can connect to the pgpool process at the
same time. So you only see B connects to pgpool.

>>> On the Postgres box, it shows both the disconnected app A and the connected app B. I think it is due to what is shown in pgpoolAdmin. The disconnect app A holds an open database connection, even though the app A is no longer running. This connection doesn't disconnect until app B does.

This is because you set child_life_time to 300. I think what happens
here is, after A and B are disconnected, pgpool goes into idle state
and sucides after 300 seconds.

>>> How can I stop this from happening?

If you want to stop the connections from pgpool to backend after A (or
B) disconnects to pgpool, you could set connection_life_time to non 0.
If you set it to 30, for example, the connection to backend will be
disconnected after it is idle for 30 seconds.

>>> Kind regards,
>>> Derek.
>>> _______________________________________________
>>> 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