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

[Andrey Zhidenkov]

On Sat, Nov 10, 2018 at 4:20 AM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>
> > 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).

Yes, it's normal. But I suggest there should be less messages than
number of "client to pgpool" connections if connection cache is used.

> 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.

I know about this feature and I used it to force pgpool not to use
connection cache and compare the results then. This behavior is
observed on database with name different from you have enumerated.
Could you please point me to the place in pgpool source code where it
doesn't create the connection cache if 'X' terminate message was not
send?

> 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.

No, all I use only one combination of user/database for testing.

> 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



-- 
-
With best regards, Andrey Zhidenkov