Thread: Connection Pooling

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.

is what i'm looking to do possible in pgbouncer or pgpool? or anything?

Thanks

Dave

On Fri, Mar 26, 2010 at 11:17 PM, David Kerr <dmk@mr-paradox.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.
>
> is what i'm looking to do possible in pgbouncer or pgpool? or anything?
>
> Thanks
>
> Dave
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


You may also have a look at Commons DBCP from Apache software
foundation, "http://commons.apache.org/dbcp/". I have used it for a
few projects and have had no problems.


Allan.

Allan Kamau wrote:
> You may also have a look at Commons DBCP from Apache software
> foundation, "http://commons.apache.org/dbcp/". I have used it for a
> few projects and have had no problems.
>

for that matter, JDBC has its own connection pooling in java.

On 3/27/2010 12:46 AM, John R Pierce wrote:
> Allan Kamau wrote:
>> You may also have a look at Commons DBCP from Apache software
>> foundation, "http://commons.apache.org/dbcp/". I have used it for a
>> few projects and have had no problems.
>
> for that matter, JDBC has its own connection pooling in java.
>
>
>

It looks like both of those solutions require a coding change. I'm
hoping for a middleware solution similar to pgpool/pgbouncer.

Thanks!

Dave

On 2010-03-29, David Kerr wrote:

> On 3/27/2010 12:46 AM, John R Pierce wrote:
>> Allan Kamau wrote:
>>> You may also have a look at Commons DBCP from Apache software
>>> foundation, "http://commons.apache.org/dbcp/". I have used it for a
>>> few projects and have had no problems.
>>
>> for that matter, JDBC has its own connection pooling in java.
>>
>>
>>
>
> It looks like both of those solutions require a coding change. I'm
> hoping for a middleware solution similar to pgpool/pgbouncer.
>

I'm using proxool for JDBC-connection pooling. It behaves as a usual
JDBC-Driver or DataSource should do. The configuration can be loaded
statically when starting the application.

--
Robert...

Allan Kamau wrote:
>>> You may also have a look at Commons DBCP from Apache software
>>> foundation, "http://commons.apache.org/dbcp/". I have used it for a
>>> few projects and have had no problems.

John R Pierce wrote:
>> for that matter, JDBC has its own connection pooling in java.

David Kerr wrote:
> It looks like both of those solutions require a coding change. I'm
> hoping for a middleware solution similar to pgpool/pgbouncer.

How's that?

If JDBC has its own connection pooling and you're already using JDBC, voilà!
no change needed, right?

If you're using a DataSource for JDBC, then it's a configuration change, not a
code change, to switch to different databases, including to a different brand
like Oracle instead of PG, never mind to switch between a pooled or unpooled
connection.

Most app servers, including Tomcat, provide DBCP inbuilt with no further
effort on the programmer's or even the deployer's part.

So to what coding changes do you refer?

--
Lew

On Fri, Mar 26, 2010 at 5:17 PM, David Kerr <dmk@mr-paradox.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

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@mr-paradox.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

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

On Mon, Apr 5, 2010 at 2:36 PM, David Kerr <dmk@mr-paradox.net> wrote:
> 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.

You should probably also look into memcached to take a lot of the read
load off of your databases.

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@mr-paradox.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

On Mon, Apr 05, 2010 at 09:46:45PM -0600, Scott Marlowe wrote:
- On Mon, Apr 5, 2010 at 2:36 PM, David Kerr <dmk@mr-paradox.net> wrote:
- > 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.
-
- You should probably also look into memcached to take a lot of the read
- load off of your databases.
-

Definitely, we've got a memcache hooked into Hibernate (or so i'm told) for the biggest app.

thanks!

Dave

On Tue, Apr 6, 2010 at 1:09 PM, David Kerr <dmk@mr-paradox.net> wrote:
> On Mon, Apr 05, 2010 at 10:44:53PM -0400, Merlin Moncure wrote:
> - 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?

yes. pool_size connections per role per db. unless you hardcode role
in the bouncer->backend connection string then it's just per db, and
the libpq->bouncer role is only used to auth to bouncer than ignored
iirc.

bouncer uses libevent, so with epoll on linux can give you excellent
performance.  that also makes it single threaded, which is extremely
nice.   it's pretty hard to max out a bouncer cpu core before the
database bottlenecks you, but on your hardware things might be
different.

merlin