<html>
<head>
<meta content="text/html; charset=windows-1252"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">Hi Daniel,<br>
<br>
Sorry for the late reply, <br>
<br>
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. <br>
<br>
I've found the following settings to be stable<br>
<br>
An the DBCP side:<br>
maxActive = 100<br>
maxIdle = 20<br>
<br>
On pgpool:<br>
num_init_children = maxActive * number of app servers + a little
for psql clients, monitoring etc. <br>
max_pool = num of app server clusters (as this drives a unique db
/ user)<br>
<br>
On Postgres:<br>
max_connections = (num_init_children * max_pools) +
superuser_reserved_connections<br>
<br>
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.<br>
<br>
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. <br>
<br>
All the best, <br>
<br>
Will<br>
<br>
<br>
On 24/01/13 01:18, Daniel Caldeweyher wrote:<br>
</div>
<blockquote
cite="mid:CAN3G7dY7aP_AFn+UyqQWDe5Y0KFh549eZUu==RKUycENxFrfog@mail.gmail.com"
type="cite">
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
<div dir="ltr">Hi Will,
<div><br>
</div>
<div style="">we have a similar setup (<a moz-do-not-send="true"
href="http://www.sraoss.jp/pipermail/pgpool-general/2013-January/001352.html">http://www.sraoss.jp/pipermail/pgpool-general/2013-January/001352.html</a>)
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?</div>
<div style=""><br>
</div>
<div style="">In your testing have tried connecting to pgpool
directly rather than through DBCP, e.g. using <span
style="color:rgb(0,0,0)">PGSimpleDataSource?</span></div>
<div style=""><br>
</div>
<div style="">
Thanks,</div>
<div style=""><br>
</div>
<div style="">Daniel</div>
</div>
<div class="gmail_extra"><br clear="all">
<div>
<div dir="ltr">
<p><b><span style="font-size:7.5pt;color:#0080ff"
lang="EN-US">daniel caldeweyher</span></b><b><span
style="font-size:7.5pt;color:gray" lang="EN-US">
DiplMM, BInfTech (Hon 1)</span></b><span
style="font-size:7.5pt;color:#1f497d" lang="EN-US"> </span><b><span
style="font-size:7.5pt;color:gray" lang="EN-US">|</span></b><span
style="font-size:7.5pt;color:#1f497d" lang="EN-US"> </span><b><span
style="font-size:7.5pt;color:gray" lang="EN-US">senior
software developer | property data solutions</span></b><span
style="font-size:7.5pt;color:#1f497d" lang="EN-US"><br>
</span><span style="font-size:7.5pt;color:gray"
lang="EN-US">telephone: 1300 665 177 | direct line: (07)
3666 2112 | facsimile: 1300 332 335<a
moz-do-not-send="true"
href="mailto:daniel.caldeweyher@pricefinder.com.au"
target="_blank"><span style="color:blue"><br>
</span></a></span><a moz-do-not-send="true"
href="mailto:daniel.caldeweyher@pricefinder.com.au"
target="_blank"><b><span
style="font-size:7.5pt;color:gray" lang="EN-US"></span></b><span
style="font-size:7.5pt;color:#1f497d" lang="EN-US"></span><span
style="font-size:7.5pt;color:gray" lang="EN-US"></span></a><span
style="font-size:7.5pt;color:gray" lang="EN-US"><a
moz-do-not-send="true"
href="mailto:daniel.caldeweyher@pricefinder.com.au"
target="_blank"><span style="color:blue">daniel.caldeweyher@pricefinder.com.au</span></a></span><span
style="font-size:10.0pt;color:#1f497d" lang="EN-US"> </span><span
style="font-size:7.5pt;color:gray" lang="EN-US">| <a
moz-do-not-send="true"
href="http://www.propertydatasolutions.com.au/"
target="_blank"><span style="color:blue">www.propertydatasolutions.com.au</span></a></span><span
style="font-size:10.0pt;color:#1f497d" lang="EN-US"></span><span
style="font-size:7.5pt;color:gray"><br>
92 petrie terrace, brisbane q 4000</span></p>
</div>
</div>
<br>
<br>
<div class="gmail_quote">On Thu, Jan 24, 2013 at 4:14 AM, Will
Ferguson <span dir="ltr"><<a moz-do-not-send="true"
href="mailto:WFerguson@northplains.com" target="_blank">WFerguson@northplains.com</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
<div link="blue" vlink="purple" lang="EN-US">
<div
style="direction:ltr;font-size:10pt;font-family:Tahoma"><span
lang="en-US"><font color="black" face="Tahoma"><span
style="font-size:10pt" dir="ltr">Hi Nick,
<br>
<br>
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.
<br>
<br>
Will</span></font></span>
<div style="font-size:16px;font-family:Times New Roman">
<hr>
<div style="direction:ltr"><font color="#000000"
face="Tahoma"><b>From:</b> <a
moz-do-not-send="true"
href="mailto:pgpool-general-bounces@pgpool.net"
target="_blank">pgpool-general-bounces@pgpool.net</a>
[<a moz-do-not-send="true"
href="mailto:pgpool-general-bounces@pgpool.net"
target="_blank">pgpool-general-bounces@pgpool.net</a>]
on behalf of Sell, Matt [<a moz-do-not-send="true"
href="mailto:matt.sell@fluke.com"
target="_blank">matt.sell@fluke.com</a>]<br>
<b>Sent:</b> 23 January 2013 17:42<br>
<b>To:</b> <a moz-do-not-send="true"
href="mailto:pgpool-general@pgpool.net"
target="_blank">pgpool-general@pgpool.net</a><br>
<b>Subject:</b> [pgpool-general: 1339] Re: Java
usage of pgpool 2<br>
</font><br>
</div>
<div>
<div class="h5">
<div>
<div>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Nick,</span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">We
are within a couple of weeks of switching
our production Glassfish 3.1.2.2-based
environment to PGPool. We’ve been testing
against PGPool 3.2.1 for about a month now
with no observed problems.</span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">We
*<b>did</b>* 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.</span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">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.</span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span></p>
<p class="MsoNormal"
style="text-autospace:none"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">
- Matt</span></p>
<p class="MsoNormal"
style="text-autospace:none"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span></p>
<p class="MsoNormal"><b><span
style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span
style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">
<a moz-do-not-send="true"
href="mailto:pgpool-general-bounces@pgpool.net"
target="_blank">pgpool-general-bounces@pgpool.net</a>
[mailto:<a moz-do-not-send="true"
href="mailto:pgpool-general-bounces@pgpool.net"
target="_blank">pgpool-general-bounces@pgpool.net</a>]
<b>On Behalf Of </b>Nick Hristov<br>
<b>Sent:</b> Wednesday, December 05, 2012
10:31 AM<br>
<b>To:</b> <a moz-do-not-send="true"
href="mailto:pgpool-general@pgpool.net"
target="_blank">pgpool-general@pgpool.net</a><br>
<b>Subject:</b> [pgpool-general: 1237]
Java usage of pgpool 2</span></p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">Hello all.</p>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal">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).</p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal">We are a java shop, and
will be connecting to pg pool via the
JDBC4 driver (its really jdbc3 under the
covers). </p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal">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.</p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal">Upon further
investigation by one of my colleagues we
discovered the issue was this: <a
moz-do-not-send="true"
href="http://www.pgpool.net/mantisbt/view.php?id=21"
target="_blank">http://www.pgpool.net/mantisbt/view.php?id=21</a> .</p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal">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.</p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal">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?</p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal">Thank you very much,</p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal">Nick</p>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<br>
_______________________________________________<br>
pgpool-general mailing list<br>
<a moz-do-not-send="true"
href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a><br>
<a moz-do-not-send="true"
href="http://www.pgpool.net/mailman/listinfo/pgpool-general"
target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br>
<br>
</blockquote>
</div>
<br>
</div>
</blockquote>
<br>
<br>
<div class="moz-signature">-- <br>
<div>
<table cellpadding="10">
<tbody>
<tr>
<td valign="top"><img
src="cid:part17.02060704.03020701@northplains.com"
border="0"></td>
<td style="font-family:Helvetica
Neue;color:#22376f;font-size:16px;line-height:17px"
align="left" valign="top"><strong>Will Ferguson</strong><br>
<span style="font-family:Helvetica
Neue;color:#22376f;font-size:12px">Technical
Operations Manager<br>
Zetland House, 5-25 Scrutton Street, London, EC2A 4HJ,
UK<br>
p: +44 (0)207 749 1800 | m: +44 (0)7825 536 416 | f:
+44 (0)207 749 1801<br>
e: <a href="mailto:willferguson@northplains.com"
target="_blank">willferguson@northplains.com</a> |
w: <a href="http://www.vyre.com" target="_blank">www.vyre.com</a><br>
<a href="https://twitter.com/vyreonbrand"
target="_blank"><img
src="cid:part20.00030606.01070401@northplains.com"></a><a
href="http://www.linkedin.com/company/vyre"
target="_blank"><img
src="cid:part22.01080103.06070601@northplains.com"></a><a
href="https://www.facebook.com/VYRELtd"
target="_blank"><img
src="cid:part24.02030501.04050403@northplains.com"></a><a
href="http://www.vyre.com/news_press/blogs/"
target="_blank"><img
src="cid:part26.07030103.06060902@northplains.com"></a></span></td>
</tr>
</tbody>
</table>
</div>
</div>
</body>
</html>