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

[Julio Gonzalo]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20140120/457eab0a/attachment.html>