pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pgbouncer
21+ messages / 15 participants
[nested] [flat]

* pgbouncer
@ 2010-06-03 16:40 Mario Ignacio Rodríguez Cortés <ignacio.cortes@inegi.org.mx>
  2010-06-03 20:03 ` Re: pgbouncer Merlin Moncure <mmoncure@gmail.com>
  2010-06-05 19:40 ` Re: pgbouncer Dimitri Fontaine <dfontaine@hi-media.com>
  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

* Re: pgbouncer
  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   ` Re: pgbouncer Vick Khera <vivek@khera.org>
  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 16:40 pgbouncer Mario Ignacio Rodríguez Cortés <ignacio.cortes@inegi.org.mx>
  2010-06-03 20:03 ` Re: pgbouncer Merlin Moncure <mmoncure@gmail.com>
@ 2010-06-03 20:33   ` Vick Khera <vivek@khera.org>
  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-03 16:40 pgbouncer Mario Ignacio Rodríguez Cortés <ignacio.cortes@inegi.org.mx>
@ 2010-06-05 19:40 ` Dimitri Fontaine <dfontaine@hi-media.com>
  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
@ 2010-06-03 17:05 Mario Rodriguez <mario.irs@gmail.com>
  2010-06-03 18:32 ` Re: pgbouncer Steve Crawford <scrawford@pinpointresearch.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 17:05 pgbouncer Mario Rodriguez <mario.irs@gmail.com>
@ 2010-06-03 18:32 ` Steve Crawford <scrawford@pinpointresearch.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

* Pgbouncer
@ 2015-11-30 21:46 Torsten Förtsch <torsten.foertsch@gmx.net>
  2015-11-30 21:50 ` Re: Pgbouncer Steve Crawford <scrawford@pinpointresearch.com>
  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:46 Pgbouncer Torsten Förtsch <torsten.foertsch@gmx.net>
@ 2015-11-30 21:50 ` Steve Crawford <scrawford@pinpointresearch.com>
  2015-11-30 21:56   ` Re: Pgbouncer 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: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   ` Torsten Förtsch <torsten.foertsch@gmx.net>
  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>
  2019-01-17 15:32 ` Re: pgbouncer David G. Johnston <david.g.johnston@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: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   ` Re: pgbouncer Fabio Pardi <f.pardi@portavita.eu>
  2019-01-17 16:15   ` Re: pgbouncer 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 15:18 pgbouncer Nicola Contu <nicola.contu@gmail.com>
  2019-01-17 15:32 ` Re: pgbouncer David G. Johnston <david.g.johnston@gmail.com>
@ 2019-01-17 16:06   ` Fabio Pardi <f.pardi@portavita.eu>
  2019-01-17 16:23     ` Re: pgbouncer 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 15:18 pgbouncer Nicola Contu <nicola.contu@gmail.com>
  2019-01-17 15:32 ` Re: pgbouncer David G. Johnston <david.g.johnston@gmail.com>
  2019-01-17 16:06   ` Re: pgbouncer Fabio Pardi <f.pardi@portavita.eu>
@ 2019-01-17 16:23     ` David G. Johnston <david.g.johnston@gmail.com>
  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-17 15:18 pgbouncer Nicola Contu <nicola.contu@gmail.com>
  2019-01-17 15:32 ` Re: pgbouncer David G. Johnston <david.g.johnston@gmail.com>
@ 2019-01-17 16:15   ` Nicola Contu <nicola.contu@gmail.com>
  2019-01-18 11:47     ` Re: pgbouncer Fabio Pardi <f.pardi@portavita.eu>
  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 15:18 pgbouncer Nicola Contu <nicola.contu@gmail.com>
  2019-01-17 15:32 ` Re: pgbouncer David G. Johnston <david.g.johnston@gmail.com>
  2019-01-17 16:15   ` Re: pgbouncer Nicola Contu <nicola.contu@gmail.com>
@ 2019-01-18 11:47     ` Fabio Pardi <f.pardi@portavita.eu>
  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>
  2023-06-28 12:23 ` Re: pgbouncer Sebastiaan Mannem <sebas@mannem.nl>
  2023-06-28 12:27 ` Re: pgbouncer Laurenz Albe <laurenz.albe@cybertec.at>
  2023-06-28 12:43 ` Re: pgbouncer Giovanni Biscontini <biscontini.g@es2000.it>
  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 11:19 pgbouncer Rita <rmorgan466@gmail.com>
@ 2023-06-28 12:23 ` Sebastiaan Mannem <sebas@mannem.nl>
  2023-06-28 14:16   ` Re: pgbouncer 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 11:19 pgbouncer Rita <rmorgan466@gmail.com>
  2023-06-28 12:23 ` Re: pgbouncer Sebastiaan Mannem <sebas@mannem.nl>
@ 2023-06-28 14:16   ` Rita <rmorgan466@gmail.com>
  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 11:19 pgbouncer Rita <rmorgan466@gmail.com>
@ 2023-06-28 12:27 ` Laurenz Albe <laurenz.albe@cybertec.at>
  2023-06-28 14:32   ` Re: pgbouncer Ben Chobot <bench@silentmedia.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 11:19 pgbouncer Rita <rmorgan466@gmail.com>
  2023-06-28 12:27 ` Re: pgbouncer Laurenz Albe <laurenz.albe@cybertec.at>
@ 2023-06-28 14:32   ` Ben Chobot <bench@silentmedia.com>
  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

* Re: pgbouncer
  2023-06-28 11:19 pgbouncer Rita <rmorgan466@gmail.com>
@ 2023-06-28 12:43 ` Giovanni Biscontini <biscontini.g@es2000.it>
  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


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