pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedpgbouncer
21+ messages / 15 participants
[nested] [flat]
* pgbouncer
@ 2010-06-03 16:40 Mario Ignacio Rodríguez Cortés <ignacio.cortes@inegi.org.mx>
0 siblings, 2 replies; 21+ messages in thread
From: Mario Ignacio Rodríguez Cortés @ 2010-06-03 16:40 UTC (permalink / raw)
To: pgsql-general
hello all:
Well, i have a question if you know about this, i have a webserver in a
server and i have a database server, the question is: where should I
install the pgbouncer? in webserver, in database server or is the same?
whats your experience.
Thanks.
ISC: Mario Ignacio Rodríguez Cortés.
^ permalink raw reply [nested|flat] 21+ messages in thread
* pgbouncer
@ 2010-06-03 17:05 Mario Rodriguez <mario.irs@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Mario Rodriguez @ 2010-06-03 17:05 UTC (permalink / raw)
To: pgsql-general
hello all:
Well, i have a question if you know about this, i have a webserver in a
server and i have a database server, the question is: where should I
install the pgbouncer? in webserver, in database server or is the same?
what's your experience.
Thanks.
ISC: Mario Ignacio Rodríguez Cortés.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: pgbouncer
@ 2010-06-03 18:32 Steve Crawford <scrawford@pinpointresearch.com>
parent: Mario Rodriguez <mario.irs@gmail.com>
0 siblings, 0 replies; 21+ messages in thread
From: Steve Crawford @ 2010-06-03 18:32 UTC (permalink / raw)
To: Mario Rodriguez <mario.irs@gmail.com>; +Cc: pgsql-general
On 06/03/2010 10:05 AM, Mario Rodriguez wrote:
>
> Well, i have a question if you know about this, i have a webserver in a
> server and i have a database server, the question is: where should I
> install the pgbouncer? in webserver, in database server or is the same?
> what's your experience.
I haven't hammered pgbouncer super-hard but in my experience it is
pretty lightweight so if you are so close to the edge on remaining
resources on either your webserver or database-server that pgbouncer's
resource use is an issue, then you probably have other issues to fix,
first. I have run it on the webserver, on the db-server and on a third
unrelated server without difficulty.
Cheers,
Steve
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: pgbouncer
@ 2010-06-03 20:03 Merlin Moncure <mmoncure@gmail.com>
parent: Mario Ignacio Rodríguez Cortés <ignacio.cortes@inegi.org.mx>
1 sibling, 1 reply; 21+ messages in thread
From: Merlin Moncure @ 2010-06-03 20:03 UTC (permalink / raw)
To: Mario Ignacio Rodríguez Cortés <ignacio.cortes@inegi.org.mx>; +Cc: pgsql-general
On Thu, Jun 3, 2010 at 12:40 PM, Mario Ignacio Rodríguez Cortés
<ignacio.cortes@inegi.org.mx> wrote:
> hello all:
>
> Well, i have a question if you know about this, i have a webserver in a
> server and i have a database server, the question is: where should I
> install the pgbouncer? in webserver, in database server or is the same?
> whats your experience.
my gut (guess) says here that if the db and web server are on
different boxes, you will want to put pgbouncer on the web server box.
my reasoning is that you have lots of libpq 'auths' going on to
pgbouncer and far fewer real auths to the database, so you want the
leg with the greater # of auths to get the local benefit. reason #2
is the less going on in a dedicated db box the better.
I'm interested in this too...does anyone else have experience in this matter?
merlin
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: pgbouncer
@ 2010-06-03 20:33 Vick Khera <vivek@khera.org>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 0 replies; 21+ messages in thread
From: Vick Khera @ 2010-06-03 20:33 UTC (permalink / raw)
To: pgsql-general
On Thu, Jun 3, 2010 at 4:03 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> I'm interested in this too...does anyone else have experience in this matter?
>
I've pondered this many times. My questions are what failure mode are
you looking to avoid, especially if you're using pgbouncer for load
balance in addition to connection pool. I think the best spot in most
cases is the web server itself, but If the web server is dead, then
the pooler goes with it, whereas if the pooler is elsewhere you have
yet another point of failure when load balancing -- that is if db1
goes down and takes pgpool with it, db2 is unused and won't save your
app.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: pgbouncer
@ 2010-06-05 19:40 Dimitri Fontaine <dfontaine@hi-media.com>
parent: Mario Ignacio Rodríguez Cortés <ignacio.cortes@inegi.org.mx>
1 sibling, 0 replies; 21+ messages in thread
From: Dimitri Fontaine @ 2010-06-05 19:40 UTC (permalink / raw)
To: Mario Ignacio Rodríguez Cortés <ignacio.cortes@inegi.org.mx>; +Cc: pgsql-general
Mario Ignacio Rodríguez Cortés <ignacio.cortes@inegi.org.mx> writes:
> Well, i have a question if you know about this, i have a webserver in a
> server and i have a database server, the question is: where should I
> install the pgbouncer? in webserver, in database server or is the same?
> whats your experience.
I always install an instance of pgbouncer per webserver, and sometimes I
also have another pgbouncer on the PostgreSQL box too.
I don't think I ever share a single pgbouncer for several web servers
directly though.
Regards,
--
dim
^ permalink raw reply [nested|flat] 21+ messages in thread
* Pgbouncer
@ 2015-11-30 21:46 Torsten Förtsch <torsten.foertsch@gmx.net>
0 siblings, 1 reply; 21+ messages in thread
From: Torsten Förtsch @ 2015-11-30 21:46 UTC (permalink / raw)
To: pgsql-general
Hi,
I am not sure if this is the right place to ask this question. If not,
please point me to it.
I am trying out the new pgbouncer (latest git). "SHOW SERVERS" is
telling me 2 connections in "active" state. Both show
connect_time=2015-11-30 18:58:30. Request_time is 19:01:35 and 20:56:36.
Both use pool_mode=transaction.
Server_lifetime=600. And now is 21:38:55.
Shouldn't those connections have been closed long ago?
I also checked on the backend. The backend processes are there, are idle
and query_start and state_change times match the request_times above.
Also, since pool_mode=transaction, why are these connections "active"?
Thanks,
Torsten
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Pgbouncer
@ 2015-11-30 21:50 Steve Crawford <scrawford@pinpointresearch.com>
parent: Torsten Förtsch <torsten.foertsch@gmx.net>
0 siblings, 1 reply; 21+ messages in thread
From: Steve Crawford @ 2015-11-30 21:50 UTC (permalink / raw)
To: Torsten Förtsch <torsten.foertsch@gmx.net>; +Cc: pgsql-general
Do you have any clients connected that are idle in transaction?
Cheers,
Steve
On Mon, Nov 30, 2015 at 1:46 PM, Torsten Förtsch <torsten.foertsch@gmx.net>
wrote:
> Hi,
>
> I am not sure if this is the right place to ask this question. If not,
> please point me to it.
>
> I am trying out the new pgbouncer (latest git). "SHOW SERVERS" is
> telling me 2 connections in "active" state. Both show
> connect_time=2015-11-30 18:58:30. Request_time is 19:01:35 and 20:56:36.
> Both use pool_mode=transaction.
>
> Server_lifetime=600. And now is 21:38:55.
>
> Shouldn't those connections have been closed long ago?
>
> I also checked on the backend. The backend processes are there, are idle
> and query_start and state_change times match the request_times above.
>
> Also, since pool_mode=transaction, why are these connections "active"?
>
> Thanks,
> Torsten
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Pgbouncer
@ 2015-11-30 21:56 Torsten Förtsch <torsten.foertsch@gmx.net>
parent: Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 0 replies; 21+ messages in thread
From: Torsten Förtsch @ 2015-11-30 21:56 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: pgsql-general
On 30/11/15 22:50, Steve Crawford wrote:
> Do you have any clients connected that are idle in transaction?
No, the backend database is a streaming replica. Also, as I said, the
backend process is in "idle" state not "idle in transaction".
backend_start | 2015-11-30 18:58:30.639962+00
xact_start | <NULL>
query_start | 2015-11-30 20:56:36.479003+00
state_change | 2015-11-30 20:56:36.480199+00
waiting | f
state | idle
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 21+ messages in thread
* pgbouncer
@ 2019-01-17 15:18 Nicola Contu <nicola.contu@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Nicola Contu @ 2019-01-17 15:18 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Hello,
I am a bit confused about the settings in pgbouncer
What's exactly the pool_size?
If I set 3, and I tried to connect from 4 shells, I am still able to
connect.
Same thing for max_db_connections. I set this to 1 and I am able to connect
from 2 shells.
This is kind of confusing and I'm not really cleared reading the
documentation.
This is my config.
[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
log_connections=0
log_disconnections=0
max_client_conn=5
I just want to limit connections from the app etc.
Thanks
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: pgbouncer
@ 2019-01-17 15:32 David G. Johnston <david.g.johnston@gmail.com>
parent: Nicola Contu <nicola.contu@gmail.com>
0 siblings, 2 replies; 21+ messages in thread
From: David G. Johnston @ 2019-01-17 15:32 UTC (permalink / raw)
To: Nicola Contu <nicola.contu@gmail.com>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: pgbouncer
@ 2019-01-17 16:06 Fabio Pardi <f.pardi@portavita.eu>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 21+ messages in thread
From: Fabio Pardi @ 2019-01-17 16:06 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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 notice an 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.
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: pgbouncer
@ 2019-01-17 16:15 Nicola Contu <nicola.contu@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 21+ messages in thread
From: Nicola Contu @ 2019-01-17 16:15 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
> 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> ha scritto:
> 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.
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: pgbouncer
@ 2019-01-17 16:23 David G. Johnston <david.g.johnston@gmail.com>
parent: Fabio Pardi <f.pardi@portavita.eu>
0 siblings, 0 replies; 21+ messages in thread
From: David G. Johnston @ 2019-01-17 16:23 UTC (permalink / raw)
To: Fabio Pardi <f.pardi@portavita.eu>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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 will notice 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.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: pgbouncer
@ 2019-01-18 11:47 Fabio Pardi <f.pardi@portavita.eu>
parent: Nicola Contu <nicola.contu@gmail.com>
0 siblings, 0 replies; 21+ messages in thread
From: Fabio Pardi @ 2019-01-18 11:47 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* pgbouncer
@ 2023-06-28 11:19 Rita <rmorgan466@gmail.com>
0 siblings, 3 replies; 21+ messages in thread
From: Rita @ 2023-06-28 11:19 UTC (permalink / raw)
To: pgsql-general
seems like I may need to deploy pgbouncer for my webapp. should i deploy it
on the db server or on the webserver?
--
--- Get your facts first, then you can distort them as you please.--
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: pgbouncer
@ 2023-06-28 12:23 Sebastiaan Mannem <sebas@mannem.nl>
parent: Rita <rmorgan466@gmail.com>
2 siblings, 1 reply; 21+ messages in thread
From: Sebastiaan Mannem @ 2023-06-28 12:23 UTC (permalink / raw)
To: Rita <rmorgan466@gmail.com>; +Cc: pgsql-general
It greatly depends on what you want to achieve. Both have pro’s and con’s.
What do you need pgbouncer for?
Verstuurd vanaf mijn iPhone
> Op 28 jun. 2023 om 13:19 heeft Rita <rmorgan466@gmail.com> het volgende geschreven:
>
>
>
> seems like I may need to deploy pgbouncer for my webapp. should i deploy it on the db server or on the webserver?
> --
> --- Get your facts first, then you can distort them as you please.--
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: pgbouncer
@ 2023-06-28 12:27 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Rita <rmorgan466@gmail.com>
2 siblings, 1 reply; 21+ messages in thread
From: Laurenz Albe @ 2023-06-28 12:27 UTC (permalink / raw)
To: Rita <rmorgan466@gmail.com>; pgsql-general
On Wed, 2023-06-28 at 07:19 -0400, Rita wrote:
> seems like I may need to deploy pgbouncer for my webapp. should i deploy it on the db server or on the webserver?
On the database server. You don't want network latency between pgbouncer and PostgreSQL,
so that the connections can be active as much as possible.
That will improve throughput.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: pgbouncer
@ 2023-06-28 12:43 Giovanni Biscontini <biscontini.g@es2000.it>
parent: Rita <rmorgan466@gmail.com>
2 siblings, 0 replies; 21+ messages in thread
From: Giovanni Biscontini @ 2023-06-28 12:43 UTC (permalink / raw)
To: Rita <rmorgan466@gmail.com>; +Cc: pgsql-general
On the DB server: the bouncer is made to optimize many and fast connections
to the DB and fasten the throughput, so You've to have the shortest way to
go to the data.
One important thing: remember that different user raise different
connections so be careful to choose which application you'll direct to
pgbouncer.
A couple more: be careful to choose the right pool_mode and tune the
right server_connect_timeout value.
all the best, Giovanni
Il giorno mer 28 giu 2023 alle ore 13:19 Rita <rmorgan466@gmail.com> ha
scritto:
>
> seems like I may need to deploy pgbouncer for my webapp. should i deploy
> it on the db server or on the webserver?
> --
> --- Get your facts first, then you can distort them as you please.--
>
--
*Best Regards*
*Dott. Giovanni Biscontini*
Software Developer & Database Architect
Web: https://www.es2000.it <http://www.es2000.it/;
_________________________________________________________________
Privacy e riservatezza: il presente messaggio, così come i relativi
allegati, contengono dati ed informazioni da considerarsi strettamente
riservate ed è indirizzato esclusivamente al destinatario sopra indicato,
il quale è l'unico autorizzato a trattarlo in osservanza delle norme del
Regolamento UE 2016/679 (RGPD). Chiunque ricevesse questo messaggio per
errore di evitare di copiarlo, divulgarlo, distribuirlo a terzi e di dare
notizia al mittente dell’errato invio, distruggendo poi l'eventuale copia
cartacea e la copia in formato elettronico.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: pgbouncer
@ 2023-06-28 14:16 Rita <rmorgan466@gmail.com>
parent: Sebastiaan Mannem <sebas@mannem.nl>
0 siblings, 0 replies; 21+ messages in thread
From: Rita @ 2023-06-28 14:16 UTC (permalink / raw)
To: Sebastiaan Mannem <sebas@mannem.nl>; +Cc: pgsql-general
I have a dedicated server. I have 3 additional physical application servers
which hit the database. I periodically get the application server not able
to reach the database server (is server on port 5432 listening? message).
Looking at some metrics, seems like I am pulling a lot of tuples (300k or
so) during that time.
On Wed, Jun 28, 2023 at 8:23 AM Sebastiaan Mannem <sebas@mannem.nl> wrote:
> It greatly depends on what you want to achieve. Both have pro’s and con’s.
> What do you need pgbouncer for?
>
> Verstuurd vanaf mijn iPhone
>
> > Op 28 jun. 2023 om 13:19 heeft Rita <rmorgan466@gmail.com> het volgende
> geschreven:
> >
> >
> >
> > seems like I may need to deploy pgbouncer for my webapp. should i deploy
> it on the db server or on the webserver?
> > --
> > --- Get your facts first, then you can distort them as you please.--
>
--
--- Get your facts first, then you can distort them as you please.--
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: pgbouncer
@ 2023-06-28 14:32 Ben Chobot <bench@silentmedia.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 21+ messages in thread
From: Ben Chobot @ 2023-06-28 14:32 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Rita <rmorgan466@gmail.com>; pgsql-general
Laurenz Albe wrote on 6/28/23 5:27 AM:
> On Wed, 2023-06-28 at 07:19 -0400, Rita wrote:
>> seems like I may need to deploy pgbouncer for my webapp. should i deploy it on the db server or on the webserver?
> On the database server. You don't want network latency between pgbouncer and PostgreSQL,
> so that the connections can be active as much as possible.
> That will improve throughput.
This is true, but as somebody who also deploys a fleet of stand-alone
pgBouncers in front of his dbs, a good reason to have a pgBouncer-based
traffic spigot before traffic hits your db server is that you can pause
traffic from the client to the db during maintenance, up to and
including replacing your entire db server, and all clients see is a
period of the db acting slow.
For applications that struggle with connection management, this can be
very valuable.
^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~2023-06-28 14:32 UTC | newest]
Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-06-03 16:40 pgbouncer Mario Ignacio Rodríguez Cortés <ignacio.cortes@inegi.org.mx>
2010-06-03 20:03 ` Merlin Moncure <mmoncure@gmail.com>
2010-06-03 20:33 ` Vick Khera <vivek@khera.org>
2010-06-05 19:40 ` Dimitri Fontaine <dfontaine@hi-media.com>
2010-06-03 17:05 pgbouncer Mario Rodriguez <mario.irs@gmail.com>
2010-06-03 18:32 ` Steve Crawford <scrawford@pinpointresearch.com>
2015-11-30 21:46 Pgbouncer Torsten Förtsch <torsten.foertsch@gmx.net>
2015-11-30 21:50 ` Re: Pgbouncer Steve Crawford <scrawford@pinpointresearch.com>
2015-11-30 21:56 ` Re: Pgbouncer Torsten Förtsch <torsten.foertsch@gmx.net>
2019-01-17 15:18 pgbouncer Nicola Contu <nicola.contu@gmail.com>
2019-01-17 15:32 ` David G. Johnston <david.g.johnston@gmail.com>
2019-01-17 16:06 ` Fabio Pardi <f.pardi@portavita.eu>
2019-01-17 16:23 ` David G. Johnston <david.g.johnston@gmail.com>
2019-01-17 16:15 ` Nicola Contu <nicola.contu@gmail.com>
2019-01-18 11:47 ` Fabio Pardi <f.pardi@portavita.eu>
2023-06-28 11:19 pgbouncer Rita <rmorgan466@gmail.com>
2023-06-28 12:23 ` Sebastiaan Mannem <sebas@mannem.nl>
2023-06-28 14:16 ` Rita <rmorgan466@gmail.com>
2023-06-28 12:27 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-06-28 14:32 ` Ben Chobot <bench@silentmedia.com>
2023-06-28 12:43 ` Giovanni Biscontini <biscontini.g@es2000.it>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox