Re: Connection Pooling

On Sat, Apr 03, 2010 at 09:32:25PM -0400, Merlin Moncure wrote:
- On Fri, Mar 26, 2010 at 5:17 PM, David Kerr <dmk(at)mr-paradox(dot)net> wrote:
- > Howdy all,
- >
- > I have some apps that are connecting to my DB via direct JDBC and I'd like to pool their connections.
- >
- > I've been looking at poolers for a while, and pgbouncer and pgpool-ii seem to be some of the most popular, so
- > i've started with those.
- >
- >
- > I'm setting up pgbouncer, and i've hit a bit of a snag. Hopefully someone can tell me if pgbouncer or pgpool are
- > capable of this (and if so, how to do it) or alternatly a pooler that can...
- >
- > What I'd like to be able to do is this (not using pooler syntax, this is just a high level of what i want to achive)
- >
- > Say i set max pool size = 10 connections. and max # of pools = 5.
- >
- > That means that i should have 5 connections to my database covering 50 connections total.
- >
- > I can't really seem to make that work with pgbouncer without naming the pools separetly. (pool1 = dbname = a, pool2 = dbname =a)
- > which means my app is tied to a pool (or has to specifically code to rotate pools...) which is not really desireable.
-
- 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)

For 4 out of the 5 applications, we're using Geronimo which has it's own pooler in the manner that
I've described above.

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.

Thanks

Dave