Re: Monitoring number of backends

On 10/22/2013 12:59 PM, Stephen Frost wrote:
> Andy,
>
> * andy (andy@squeakycode.net) wrote:
>> My website is about to get a little more popular.  I'm trying to add
>> in some measurements to determine an upper limit of how many
>> concurrent database connections I'm currently using.
>
> PG is really *much* happier if you have only one backend per CPU in your
> system.  The way to get there is by using a connection pooler like
> pg_bouncer and configuring it based on how many CPUs you have.
>
> pg_bouncer can also provide stats for you.
>
>     Thanks,
>
>         Stephen
>

Ahh, bummer, man.  PgBouncer doesn't work so well when you have lots of databases.  I have about 90 databases, the
websitecould connect to any one of them on any request.  (They are all about as equally likely to be hit) 

In my pgbouncer.ini I set
[databases]
* =
[pgbouncer]
max_cleint_conn = 200
default_pool_size = 20

but that made each database its own pool, so 90*20 possible (but then maxed out at 200, I assume).  Postgres has only a
max_connectionsof 40, so I started getting "FATAL sorry too many cleints already" errors. 

I set the max_cleint_conn = 40
and default_pool_size = 3

so it should stop erroring out, but is this type of setup even going to help me?  If pgbouncer has 40 connections open
to40 different databases, and then a request comes in for a db it doesnt have, it'll have to drop one and build a new
connection,yes?  Won't that be slower than just connecting right to postgres in the first place? 

I need a Most-Recently-Used pool, not a per-database pool.  Is there a way to do this with pgbouncer?  (With a large
numberof databases) 

-Andy