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.johns...@gmail.com <mailto:david.g.johns...@gmail.com>> ha scritto: > > On Thu, Jan 17, 2019 at 8:18 AM Nicola Contu <nicola.co...@gmail.com > <mailto:nicola.co...@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. >
