[pgpool-general: 1360] Re: Java usage of pgpool 2

Will Ferguson wferguson at northplains.com
Fri Feb 1 21:55:08 JST 2013

Hi Daniel,

Sorry for the late reply,

We'll be running multiple clusters of Java app servers, where all app 
servers in a cluster will connect to the same database with the same 
user. All app servers are using DBCP.

I've found the following settings to be stable

An the DBCP side:
maxActive = 100
maxIdle = 20

On pgpool:
num_init_children = maxActive *  number of app servers + a little for 
psql clients, monitoring etc.
max_pool = num of app server clusters (as this drives a unique db / user)

On Postgres:
max_connections = (num_init_children * max_pools) + 

I'm currently not sure if there is really any benefit in having the 2 
layer pooling. DBCP does a good job at managing connections and I don't 
really see much benefit in having pgpool manage extra idle connections 
to the database - I'd be interested in hearing any opinions on this.

The intention was always to use pgpool for load balancing and HA rather 
than any pooling features. We've been using DBCP for years so I'm more 
inclined to drop the pgpool pooling features - setting connection_cache 
= off - than replace DBCP and so I haven't tested a PGSimpleDataSource. 
I may get around to it but have other areas I'm more focusing on atm.

All the best,


On 24/01/13 01:18, Daniel Caldeweyher wrote:
> Hi Will,
> we have a similar setup 
> (http://www.sraoss.jp/pipermail/pgpool-general/2013-January/001352.html) 
> and I was just hoping you could tell us your DBCP / pgpool 
> configuration and how you deal with the 2-layer connection pooling. 
> Does your pgpool max client match the max total of pooled DBCP 
> connections, i.e. your pgpool acts more like load balancer rather than 
> actual connection pool to reduce backend connections?
> In your testing have tried connecting to pgpool directly rather than 
> through DBCP, e.g. using PGSimpleDataSource?
> Thanks,
> Daniel
> *daniel caldeweyher**DiplMM, BInfTech (Hon 1)**|**senior software 
> developer | property data solutions*
> telephone: 1300 665 177 | direct line: (07) 3666 2112 | facsimile: 
> 1300 332 335
> <mailto:daniel.caldeweyher at pricefinder.com.au>**<mailto:daniel.caldeweyher at pricefinder.com.au>daniel.caldeweyher at pricefinder.com.au 
> <mailto:daniel.caldeweyher at pricefinder.com.au>| 
> www.propertydatasolutions.com.au 
> <http://www.propertydatasolutions.com.au/>
> 92 petrie terrace, brisbane  q  4000
> On Thu, Jan 24, 2013 at 4:14 AM, Will Ferguson 
> <WFerguson at northplains.com <mailto:WFerguson at northplains.com>> wrote:
>     Hi Nick,
>     We're also just finishing testing a Java / PGPool setup. Our app
>     servers use JDBC / Hibernate & Apache DBCP, and will be working
>     with a 2 server PG cluster. Generally the only issues we've had
>     have been around our understanding and learning - especially
>     around connection management, but no bugs to speak of (we're using
>     PGPool 3.2.1 and PG 9.2). As it stands, I can't see anything
>     stopping us going into production so am pretty confident all will
>     be OK.
>     Will
>     ------------------------------------------------------------------------
>     *From:* pgpool-general-bounces at pgpool.net
>     <mailto:pgpool-general-bounces at pgpool.net>
>     [pgpool-general-bounces at pgpool.net
>     <mailto:pgpool-general-bounces at pgpool.net>] on behalf of Sell,
>     Matt [matt.sell at fluke.com <mailto:matt.sell at fluke.com>]
>     *Sent:* 23 January 2013 17:42
>     *To:* pgpool-general at pgpool.net <mailto:pgpool-general at pgpool.net>
>     *Subject:* [pgpool-general: 1339] Re: Java usage of pgpool 2
>     Nick,
>     We are within a couple of weeks of switching our production
>     Glassfish environment to PGPool. We’ve been testing
>     against PGPool 3.2.1 for about a month now with no observed problems.
>     We **did** have problems where PGPool 3.2.0 was crashing when
>     using the “ping” command from Glassfish to test the connection,
>     and again when retrieving large objects (byte arrays) from PG via
>     PGPool. Those problems have been fixed (at least as far as our use
>     case goes) as of 3.2.1. The in-memory query cache feature was a
>     welcome addition and gives us a little performance boost.
>     With the exception of the large object composed using a byte
>     array, our usage of PG via PGPool is pretty simplistic, though.
>     We’re comfortable moving forward with PGPool 3.2.1 and PG 9.2.
>     - Matt
>     *From:*pgpool-general-bounces at pgpool.net
>     <mailto:pgpool-general-bounces at pgpool.net>
>     [mailto:pgpool-general-bounces at pgpool.net
>     <mailto:pgpool-general-bounces at pgpool.net>] *On Behalf Of *Nick
>     Hristov
>     *Sent:* Wednesday, December 05, 2012 10:31 AM
>     *To:* pgpool-general at pgpool.net <mailto:pgpool-general at pgpool.net>
>     *Subject:* [pgpool-general: 1237] Java usage of pgpool 2
>     Hello all.
>     My company is currently thinking about deploying pgpool as a high
>     availability solution of our current postgresql installation (we
>     want to be able to have an automated method of switching to a
>     hot-standby pg instance at any point).
>     We are a java shop, and will be connecting to pg pool via the
>     JDBC4 driver (its really jdbc3 under the covers).
>     The problem is that we discovered a complete show stopper bug,
>     where pg pool crashes immediately upon connection with the JDBC
>     driver. Connections/queries via psql work quite fine.
>     Upon further investigation by one of my colleagues we discovered
>     the issue was this: http://www.pgpool.net/mantisbt/view.php?id=21 .
>     Even though we applied the patch, I am quite uneasy, because this
>     is a show stopper bug, imho. I am worried that this could prove to
>     be just the beginning of a series of issues.
>     My question is, does anybody have a sense how many
>     companies/organizations have chosen to deploy a pgpool/java in
>     their production environment? What is the overall impression of
>     the stability of the product? Any gotchas, or clear do-not-do-this
>     items that you can point out?
>     Thank you very much,
>     Nick
>     _______________________________________________
>     pgpool-general mailing list
>     pgpool-general at pgpool.net <mailto:pgpool-general at pgpool.net>
>     http://www.pgpool.net/mailman/listinfo/pgpool-general

	*Will Ferguson*
Technical Operations Manager
Zetland House, 5-25 Scrutton Street, London, EC2A 4HJ, UK
p: +44 (0)207 749 1800 | m: +44 (0)7825 536 416 | f: +44 (0)207 749 1801
e: willferguson at northplains.com <mailto:willferguson at northplains.com> | 
w: www.vyre.com <http://www.vyre.com>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20130201/3fc3b981/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: email-signatureVYRE.png
Type: image/png
Size: 5672 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20130201/3fc3b981/attachment-0005.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: socialmedia-twitter.png
Type: image/png
Size: 1391 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20130201/3fc3b981/attachment-0006.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: socialmedia-linkedin.png
Type: image/png
Size: 1412 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20130201/3fc3b981/attachment-0007.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: socialmedia-fb.png
Type: image/png
Size: 1374 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20130201/3fc3b981/attachment-0008.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: socialmedia-blog.png
Type: image/png
Size: 1446 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20130201/3fc3b981/attachment-0009.png>

More information about the pgpool-general mailing list