Re: pgbouncer - Mailing list pgsql-general

On Thu, Jan 17, 2019 at 9:06 AM Fabio Pardi <f.pardi@portavita.eu> wrote:
> Are you sure? The behaviour I experienced is different from what you described.

85%...
>
> On 17/01/2019 16:32, David G. Johnston wrote:
> > On Thu, Jan 17, 2019 at 8:18 AM Nicola Contu <nicola.contu@gmail.com> wrote:
> >>
> >> Hello,
> >> I am a bit confused about the settings in pgbouncer
> >>
> >> What's exactly the pool_size?
> >
> > Roughly, the number of open connections pgbouncer will keep to PostgreSQL.
> >
> >> If I set 3, and I tried to connect from 4 shells, I am still able to connect.
> >
> > That would be the point - pgbouncer is sharing the 3 connections it
> > keeps with PostgreSQL between the 4 client connections made to it.
>
>
> pgbouncer will spawn connections until it reach the maximum allowed. Connections go 1:1 between pgbouncer and the
client,and are not 'shared'
 

The pgbouncer->PostgreSQL connections in the pool are shared among the
different client connections the database/user pairing use.

>
> pool_size is the size of the pool that pgbouncer keeps open for you. If not set, the default_pool_size is used.

The "you" pronoun doesn't make sense - I am a person but pgbouncer
only cares about the client connections I try to make and I can make
multiple.

>
> If you check the open connections to your host, you can verify it
>
> netstat --tcp -n | grep ...
>
> will show ESTABLISHED connections = pool_size
>
>
> client connections will be accepted by pgbouncer until pool_size is reached.

As you say below pgbouncer accepts the connection and places it into a
wait queue for one of the pool pgbouncer->PostgreSQL sessions to
become free

> after that, pgbouncer can spawn more connection only if reserve_pool_size allows it, and after a wait of
reserve_pool_timeout.
 

Ignoring the accordion behavior for now

>
> when your roof is reached (means you are opening pool_size + reserve_pool_size) then client connections will enter a
queue.

ceiling...

>
> Entering a queue, does not mean be serviced by the database. It means wait your turn. If you connect manually, you
willnotice an open connection, (socket is opened) but not database shell.
 

There is no such thing as a database shell; clients might have shells.
All that matters, though, is whether the connection from the client is
open and it is able to send queries over it.  That those queries don't
get executed right away because they have been queued waiting for an
available pgbouncer->PostgreSQL session is what I was describing:
sharing the 2 connections/sessions among 5 connected clients.

David J.