Обсуждение: pgbouncer

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.

> Same thing for max_db_connections. I set this to 1 and I am able to connect from 2 shells.

Same as pool_size but basically a fail-safe since pools are
per-user/per-database while this limit is per-database only.

> This is kind of confusing and I'm not really cleared reading the documentation.

For each setting you need to understand whether it limits
pgbouncer->PostgreSQL or client->pgbouncer

Configurations in [databases] limit the former; [pgbouncer] options
either provide defaults for the [databases] or limit clients.

> [databases]
> cmdwela = host=127.0.0.1 port=5432 dbname=cmdwela auth_user=pgbouncer pool_size=120
> cicciopasticcio = host=127.0.0.1 port=5432 dbname=cicciopasticcio auth_user=pgbouncer pool_size=2
>
> [pgbouncer]
> listen_port = 6543
> listen_addr = *
> auth_type = md5
> auth_file = /etc/pgbouncer/users.txt
> auth_query = select uname,phash from user_lookup($1)
> logfile = /var/log/pgbouncer.log
> pidfile = /home/postgres/pgbouncer.pid
> admin_users = admin
> user=postgres

> max_db_connections=1
So one open connection max per database/user pool but it is shared -
i.e., actively executing queries running in parallel are limited to
this number.

> max_client_conn=5
> I just want to limit connections from the app etc.

That limit is 5

David J.

David,

Are you sure? The behaviour I experienced is different from what you described.

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'
 

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


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.

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

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

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


regards,

fabio pardi

> 
>> Same thing for max_db_connections. I set this to 1 and I am able to connect from 2 shells.
> 
> Same as pool_size but basically a fail-safe since pools are
> per-user/per-database while this limit is per-database only.
> 
>> This is kind of confusing and I'm not really cleared reading the documentation.
> 
> For each setting you need to understand whether it limits
> pgbouncer->PostgreSQL or client->pgbouncer
> 
> Configurations in [databases] limit the former; [pgbouncer] options
> either provide defaults for the [databases] or limit clients.
> 
>> [databases]
>> cmdwela = host=127.0.0.1 port=5432 dbname=cmdwela auth_user=pgbouncer pool_size=120
>> cicciopasticcio = host=127.0.0.1 port=5432 dbname=cicciopasticcio auth_user=pgbouncer pool_size=2
>>
>> [pgbouncer]
>> listen_port = 6543
>> listen_addr = *
>> auth_type = md5
>> auth_file = /etc/pgbouncer/users.txt
>> auth_query = select uname,phash from user_lookup($1)
>> logfile = /var/log/pgbouncer.log
>> pidfile = /home/postgres/pgbouncer.pid
>> admin_users = admin
>> user=postgres
> 
>> max_db_connections=1
> So one open connection max per database/user pool but it is shared -
> i.e., actively executing queries running in parallel are limited to
> this number.
> 
>> max_client_conn=5
>> I just want to limit connections from the app etc.
> 
> That limit is 5
> 
> David J.
> 

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.

Nicola,

My previous mail was maybe misleading because when i mentioned
'connections' I actually meant active connections to the db, as in:
doing a transaction.
In that case, yes, the connections are shared. But as soon as they
initiate a transaction, then they are not shared any longer and a new
pgbouncer connection is spawn to the database. Alternatively, the client
cannot be served and has to wait.

I hope my example reported here below will clarify the situation:


---
pgbouncer setting:

test_db   = host=... dbname=... user=... pool_size=2

[pgbouncer]
pool_mode = session


max_client_conn = 10
default_pool_size = 5
min_pool_size = 4
reserve_pool_size = 3
---

With above configuration, this is what 'show pools' on pgbouncer shows
when clients are not using pgbouncer:

database   | test_db
user       | xx
cl_active  | 0
cl_waiting | 0
sv_active  | 0
sv_idle    | 0
sv_used    | 2
sv_tested  | 0
sv_login   | 0
maxwait    | 0
maxwait_us | 0
pool_mode  | session

2 servers used, as specified by pool_size

means: 2 TCP connections open to the db

 netstat --tcp -n |grep 5432 | | grep EST | wc -l
2


*******************************************

if you remove pool_size from the database string, then config becomes:

test_db   = host=... dbname=... user=...

---
[pgbouncer]
pool_mode = session


max_client_conn = 10
default_pool_size = 5
min_pool_size = 4
reserve_pool_size = 3
---

therefore:

database   | test_db
user       | xx
cl_active  | 0
cl_waiting | 0
sv_active  | 0
sv_idle    | 0
sv_used    | 4
sv_tested  | 0
sv_login   | 0
maxwait    | 0
maxwait_us | 0
pool_mode  | session



netstat --tcp -n |grep EST | grep 5432 | wc -l
4


what we learned: pool_size overwrites min_pool_size

***********************************************


Now, I m restoring pool_size to 2 as we had in the beginning, and
checking how many connections we can open to pgbouncer..


We now have 2 connections always available.

i therefore open open 2 connections to the database. I only connect, and
leave them idle. This is confirmed by the

-[ RECORD 2 ]---------
database   | test_db
user       | xx
cl_active  | 2
cl_waiting | 0
sv_active  | 0
sv_idle    | 0
sv_used    | 2
sv_tested  | 0
sv_login   | 0
maxwait    | 0
maxwait_us | 0
pool_mode  | session


so, now, 2 server connections are open. If I open one more, then you are
sharing the connections, as David mentioned.

-[ RECORD 2 ]---------
database   | test_db
user       | xx
cl_active  | 3
cl_waiting | 0
sv_active  | 0
sv_idle    | 0
sv_used    | 2
sv_tested  | 0
sv_login   | 0
maxwait    | 0
maxwait_us | 0
pool_mode  | session

We can see 3 clients active, and 2 connections to the db.

and netstat will count 2 TCP connections only.


If instead you ask your clients to initiate a database transaction, for
instance typing 'BEGIN ;' then you will actually see what i meant in my
previous mail:

database   | test_db
user       | xx
cl_active  | 3
cl_waiting | 0
sv_active  | 3
sv_idle    | 0
sv_used    | 0
sv_tested  | 0
sv_login   | 0
maxwait    | 0
maxwait_us | 0
pool_mode  | session

and netstat will show 3 open connections to your db


how many connections can you possibly open to the database?

pool_size + reserve_pool_size = 5 in my example.

how many connections can you make to pgbouncer? a total (globally,
including to pgbouncer db) of max_client_conn

that means, 'cl_active' can go up to 10 in my example, but as soon as
the 6th client initiates a transaction, it cannot be served and has to
wait for a connection to be freed.


regards,

fabio pardi





On 1/17/19 5:15 PM, Nicola Contu wrote:
>> 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.
> 
> Mmh, my pool_mode is per session. The 4 sessions were active, not doing
> any query, but connected to the shell.
> So that's what my doubt 
> 
> Il giorno gio 17 gen 2019 alle ore 16:32 David G. Johnston
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> ha scritto:
> 
>     On Thu, Jan 17, 2019 at 8:18 AM Nicola Contu <nicola.contu@gmail.com
>     <mailto: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.
> 
>     > Same thing for max_db_connections. I set this to 1 and I am able
>     to connect from 2 shells.
> 
>     Same as pool_size but basically a fail-safe since pools are
>     per-user/per-database while this limit is per-database only.
> 
>     > This is kind of confusing and I'm not really cleared reading the
>     documentation.
> 
>     For each setting you need to understand whether it limits
>     pgbouncer->PostgreSQL or client->pgbouncer
> 
>     Configurations in [databases] limit the former; [pgbouncer] options
>     either provide defaults for the [databases] or limit clients.
> 
>     > [databases]
>     > cmdwela = host=127.0.0.1 port=5432 dbname=cmdwela
>     auth_user=pgbouncer pool_size=120
>     > cicciopasticcio = host=127.0.0.1 port=5432 dbname=cicciopasticcio
>     auth_user=pgbouncer pool_size=2
>     >
>     > [pgbouncer]
>     > listen_port = 6543
>     > listen_addr = *
>     > auth_type = md5
>     > auth_file = /etc/pgbouncer/users.txt
>     > auth_query = select uname,phash from user_lookup($1)
>     > logfile = /var/log/pgbouncer.log
>     > pidfile = /home/postgres/pgbouncer.pid
>     > admin_users = admin
>     > user=postgres
> 
>     > max_db_connections=1
>     So one open connection max per database/user pool but it is shared -
>     i.e., actively executing queries running in parallel are limited to
>     this number.
> 
>     > max_client_conn=5
>     > I just want to limit connections from the app etc.
> 
>     That limit is 5
> 
>     David J.
>