[pgpool-general: 1332] pg-pool + multi JVM + hibernate

[Daniel Caldeweyher]

Hi All,

I have subscribed to this list because my google-fu is exhausted trying to
find the best configuration for our setup:

We currently have multiple JVMs (across multiple servers) connected to our
main postgres 9 db (max connection = 1000, data on multi-path 1GE iSCSI
SAN). We tried to upgrade to a faster server with local disks, but were
suddenly swamped with a very high load. We resolved that this had to be due
to the removal of the network bottleneck and too high max connections
setting.

Due to the multi JVM setup and the eventual goal of load balanced DBs, we
decided to get our applications to use pgbouncer initially as this is said
to be easier to get up and running.

To reduce the number of connection we trialled half our JVMs with pgbouncer
with max 500 clients and max 150 server connections. However since we use
Hibernate which utilises Prepared statements, the caching of which we
didn't want to loose (pgbouncer doens't parse/cache statements) we wanted
to keep our existing JDBC connection pool (BoneCP).

And this is where we have run in to trouble. Using multiple layers of
connection pooling doesn't seem to work quite as well, I suspect due to do
idle JDBC pooled connection using up idle pooled backend connections. To
counter this we tried to set pgbouncer's pool size = max clients = 150 and
ensured that the total maximum JDBC connection would not exceed this. This
gave us better results, but once again during peak usage, connections would
hang.

Googling for JDBC vs external pooling has not provided any answers. Pretty
much all resources I was able to find (e.g.
http://pgexperts.com/document.html?id=58) state that if you use Java, you
should use JDBC pooling, all else use pgbouncer/pgpool.

However, none of the resources were commenting on multi JVM setups and load
balanced databases.

We have now decided to scrap pgbouncer and focus all our efforts on pgpool
as this is what we will need down the track anyway, once our Master/Slave
SR is setup.

As far as I am aware pgpool caches Prepared Statements, which means we
should be able to get rid of our JDBC pooling. For this I have configured
our JNDI/JDBC resource as follows:

<Resource name="jdbc/prod" auth="Container"
     type="org.postgresql.ds.PGSimpleDataSource"
factory="org.postgresql.ds.common.PGObjectFactory"
 user="UUUU" password="PPPP"
serverName="prod-pool"
portNumber="9999"
 databaseName="prod"
prepareThreshold="1"/>

I wasn't able to find any instructions on PGSimpleDataSource but looking
through the postgres jdbc4 jar and reading the PGObjectFactory source code
I was able to get this working.

My question, is this the correct way, i.e. PGSimpleDataSource + pgpool, for
configuring multi JVM applications?

My only concern with this configuration is, that I don't believe that we
are the only ones with this sort of setup, which what I believe would be a
fairly standard JEE setup. But yet, I was unable to find any kind of
examples or articles about multi JVM + pgpool ( + hibernate).

Otherwise, how do others setup their load balanced java apps and load
balanced postgres servers? Glassfish cluster + Glassfish managed connection
pool?

Thank you very much in advance,

Daniel
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20130121/f225fa04/attachment.html>