From: | Fabio Pardi <f(dot)pardi(at)portavita(dot)eu> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: pgbouncer |
Date: | 2019-01-18 11:47:48 |
Message-ID: | a635e456-fab6-e5f6-f480-dc5e45c0d3e5@portavita.eu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)com>> ha scritto:
>
> On Thu, Jan 17, 2019 at 8:18 AM Nicola Contu <nicola(dot)contu(at)gmail(dot)com
> <mailto:nicola(dot)contu(at)gmail(dot)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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Michal | 2019-01-18 11:50:38 | Re: Upgrading from 9.2.15 to 9.4.20 |
Previous Message | Achilleas Mantzios | 2019-01-18 10:47:32 | Re: Upgrading from 9.2.15 to 9.4.20 |