[pgpool-general: 6272] Re: pgpool does not reuse connections

[Tatsuo Ishii]

> pgpool log is full of messages like these:
> 
> pgpool_1    | 2018-11-09 14:53:26: pid 4618: LOG:  new connection received
> pgpool_1    | 2018-11-09 14:53:26: pid 4618: DETAIL:  connecting
> host=172.22.0.1 port=60030

The log does not help too much (it's normal if you set log_connections
to on).

Another possibility is, if the database is one of template0, template1,
postgress or regression, connection cache is not created. Does your
application use those database?  Also if client does not disconnect
proper way (without sending terminate message 'X') then connection
cache is not created.

Finally, if there are too many combinations of user/database (in your
case more than 4 = max_pool), Pgpool-II needs to disconnect the oldest
connection to create a new connection cache.

Are one of them is your case?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> I wrote test app on golang and succeeded to reproduce this on my local
> machine. Go sql driver uses pq library, written on go from scratch and
> it doesn't use postgres libpq C library. Would that cause the problem?

No, as far as pq library strictly follows PostgreSQL's
frontend/backend protocol.

> (https://github.com/lib/pq/blob/master/conn.go#L820).
> On Fri, Nov 9, 2018 at 8:54 PM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>>
>> > We have a problem at one of our environments with pgpool. It does not
>> > use connection pool for some reason. Application written on golang (we
>> > use gorm which uses package https://golang.org/pkg/database/sql/)
>> > opens connections to pgpool and executes queries. Application executes
>> > every transaction in a separate connection. In postgresql logs I see
>> > that pgpool constantly connects and disconnects from postgres like as
>> > if connection pool is not used. I tried to reproduce this situation
>> > with pgbench (option --connect) but everything is OK with it. pgpool
>> > works with pgbench as expected - it reuses all connections and doesn't
>> > create a new one for each transaction.
>> >
>> > num_init_children = 100, max_pool =4. max_connections in postgres = 1000.
>> >
>> > We tried pgpool versions 3.3.4 and 3.7.5.
>> >
>> > Any ideas?
>>
>> No idea so far. Can you share pgpool log? 3.7.5 is preferable as 3.3
>> is already EOL.
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
> 
> 
> 
> -- 
> -
> With best regards, Andrey Zhidenkov