Re: Connection Pooling

On Mon, Apr 05, 2010 at 10:44:53PM -0400, Merlin Moncure wrote:
- On Mon, Apr 5, 2010 at 4:36 PM, David Kerr <dmk(at)mr-paradox(dot)net> wrote:
- > On Sat, Apr 03, 2010 at 09:32:25PM -0400, Merlin Moncure wrote:
- > 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.

oh, interesting, I had been looking at session mode, I'll have to check out transaction mode.

- > 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.

right, so that's kind of my issue, the ETL process will log in as 1 user, and connect to 1 database
wouldn't that mean that if i set max_pool_size = 30 then i'd have at max 30 connections allowed to
the DB from that user?

- 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.

right i wouldn't need either of those.

Thanks!

Dave