Re: pgbouncer - Mailing list pgsql-general

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