<div dir="ltr"><span style="font-family:arial,sans-serif;font-size:13.333333969116211px">Hi All,</span><div style="font-family:arial,sans-serif;font-size:13.333333969116211px"><br></div><div style="font-family:arial,sans-serif;font-size:13.333333969116211px">
I have subscribed to this list because my google-fu is exhausted trying to find the best configuration for our setup:</div><div style="font-family:arial,sans-serif;font-size:13.333333969116211px"><br></div><div style="font-family:arial,sans-serif;font-size:13.333333969116211px">
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.</div>
<div style="font-family:arial,sans-serif;font-size:13.333333969116211px"><br></div><div style="font-family:arial,sans-serif;font-size:13.333333969116211px">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.</div>
<div style="font-family:arial,sans-serif;font-size:13.333333969116211px"><br></div><div style="font-family:arial,sans-serif;font-size:13.333333969116211px">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).<br>
</div><div style="font-family:arial,sans-serif;font-size:13.333333969116211px"><br></div><div style="font-family:arial,sans-serif;font-size:13.333333969116211px">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.</div>
<div style="font-family:arial,sans-serif;font-size:13.333333969116211px"><br></div><div style="font-family:arial,sans-serif;font-size:13.333333969116211px">Googling for JDBC vs external pooling has not provided any answers. Pretty much all resources I was able to find (e.g. <a href="http://pgexperts.com/document.html?id=58" target="_blank">http://pgexperts.com/document.html?id=58</a>) state that if you use Java, you should use JDBC pooling, all else use pgbouncer/pgpool.</div>
<div style="font-family:arial,sans-serif;font-size:13.333333969116211px"><br></div><div style="font-family:arial,sans-serif;font-size:13.333333969116211px">However, none of the resources were commenting on multi JVM setups and load balanced databases.</div>
<div style="font-family:arial,sans-serif;font-size:13.333333969116211px"><br></div><div style="font-family:arial,sans-serif;font-size:13.333333969116211px">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.</div>
<div style="font-family:arial,sans-serif;font-size:13.333333969116211px"><br></div><div style="font-family:arial,sans-serif;font-size:13.333333969116211px">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:</div>
<div style="font-family:arial,sans-serif;font-size:13.333333969116211px"><br></div><div style="font-family:arial,sans-serif;font-size:13.333333969116211px"><div><Resource name="jdbc/prod" auth="Container" </div>
<div> <span style="white-space:pre-wrap"> </span>type="org.postgresql.ds.PGSimpleDataSource"</div><div><span style="white-space:pre-wrap"> </span>factory="org.postgresql.ds.common.PGObjectFactory"<br>
</div><div><span style="white-space:pre-wrap"> </span>user="UUUU" password="PPPP" </div><div><span style="white-space:pre-wrap"> </span>serverName="prod-pool"</div><div><span style="white-space:pre-wrap"> </span>portNumber="9999"</div>
<div><span style="white-space:pre-wrap"> </span>databaseName="prod"</div><div><span style="white-space:pre-wrap"> </span>prepareThreshold="1"/></div><div><br></div><div>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.</div>
<div><br></div><div>My question, is this the correct way, i.e. PGSimpleDataSource + pgpool, for configuring multi JVM applications?</div><div><br></div><div>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).</div>
<div><br></div><div>Otherwise, how do others setup their load balanced java apps and load balanced postgres servers? Glassfish cluster + Glassfish managed connection pool?</div><div><br></div><div>Thank you very much in advance,</div>
<div><br></div><div>Daniel</div></div>
</div>