[pgpool-general: 2455] Re: load balancing and JDBC connection pool

Tatsuo Ishii ishii at postgresql.org
Mon Jan 20 22:01:20 JST 2014

> Hi everyone. I'm new to this mailing list. Greetings to the pgpool-II
> community :-)
> There goes my question:
> I would like to use the load balancing capabilities of pgpool-II in a
> existing production setup but I have several concerns...
> This is the scenario (simplified):
> * 1 Web Server running Tomcat
> * 2 DB Servers running Postgres 9.3. Master-Slave replication using
> Hot-Standby.  Slave server runs in read-only mode and can be queried
> * 2 applications runs in the same Tomcat
> * Each application connects to Postgres through its own JDBC connection pool
> These 2 applications serve to different purposes but write into the same
> database.
> The JDBC connection pool allows me to have control of the connection
> management (flow, min/max idle connections, validation queries, expiration
> time of running queries, request queues, expiration time of awaiting
> requests, etc)
> Each application has different pool configuration values.
> Currently the postgres slave server are used to perform backups and as a
> safeguard in case of master crash but I would like to perform load
> balancing because the applications run long read queries and it would be
> beneficial.
> As far as I know, it can be only achieved using an intermediate layer, like
> pgpool-II. From the point of view of the application, it is transparent.
> Pgpool talks to the database, application talks to pgpool by simply
> changing the connection string.
> Load balancing is configured in pgpool but, like its name points, is also a
> connection pool so I end up with 2 pool layers. Is it correct?


> I could leave out Tomcat JDBC Connection pool and let the pooling tasks
> only to pgpool but I don't want to lose the capabilities that I currently
> have (different values for each application, visual monitoring of the pool
> status, dynamic modification of the pool through JMX, etc...).
> So, I guess that I need to setup a pool of connections in pgpool greater
> than the sum of the max connections of each JDBC pool and adjusts the
> timeout values in order to avoid undesired connection behaviours.
> I suspect that, despite the benefits of load balance read queries,
> "regular" performance will decrease because of the overhead caused by this
> extra step to reach the database.
> My question is: what is the best way to achieve load balancing with
> pgpool-II, bearing in mind that the applications has their own pooling
> strategy?
> Thank you in advance

I suggest to start off by disabling connection pooling of pgpool-II by

connection_cache = off

If this does not work well, please let us know.

Best regards,
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

More information about the pgpool-general mailing list