Re: Connection Pooling

On Mon, Apr 5, 2010 at 4:36 PM, David Kerr <dmk@mr-paradox.net> wrote:
> On Sat, Apr 03, 2010 at 09:32:25PM -0400, Merlin Moncure wrote:
> -
> - I have a lot of respect for pgbouncer (haven't used pgpool).  One
> - possible way to do what you're thinking is to rotate the pool on user.
> -  In bouncer each database role gets its own pool (if you understand
> - how transaction mode works you can see why it has to work this way).
> - Not sure if this is helpful.  Why are you trying to separate the pools
> - like that?
> -
> - merlin
>
> Based on a lot of the comments i've gotten here, I'm starting to think that I've got the wrong idea about
> connection pools and pooling in general. So, let me lay out some of my assumptions and my problem and
> maybe we can go from there...
>
> My app will have over 10k concurrent users. I have huge servers 32 cores (64bit), 64GB ram. RedHat linux.
>
> Those 10k users will all be logging in as one of 5 application users.
>
> From following this list, and talking to our PG consultants, I know that for that many connecitons I need
> to have a connection pooler. Because with postgres you shouldn't set max_connections much higher than 2000
> (which is pushing it)

This is correct.  If you go with pgbouncer, you would want to use
transaction pooling mode obviously.

> For the 5th application, an ETL job that could have as many as 1000 concurrent processes/connections,
> i don't have a java container. it's just a raw jar file that gets run via java <bla>.
>
> That's what I'm aiming to pool, and i'd like to do it without modifying the code if possible.

pgbouncer is totally transparent.  I manage quite a few databases and
I use it (w/session mode) so I can psql to a single host (localhost),
and bounce between different databases.  Like I said earlier, you have
discreet pools based on role -- otherwise there would be no really
good way to control the role your queries would operate under.  This
will keep your etl from drilling your box, and if you keep your
pool_size under the number of cores you have, you will have some
available if things get really dicey, which is nice.

caveats:
*) no openssl, but stunnel works well (you may have to grab a recent stunnel)
*) transaction mode blocks use of certain database features, like
notifies.  again, doesn't sound too bad for you

doesn't sound like you need openssl though.  If you have the ability
to set up a test environment, I'd set it up and give it a shot.

merlin