Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 9754C63315F for ; Fri, 9 Jul 2010 16:28:32 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 57349-02 for ; Fri, 9 Jul 2010 19:28:24 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from bill.kwcorp.com (mailx.kwcorp.com [65.118.99.196]) by mail.postgresql.org (Postfix) with ESMTP id EE0016330B7 for ; Fri, 9 Jul 2010 16:28:23 -0300 (ADT) X-TM-IMSS-Message-ID: <7d801c54000bbbc1@kwcorp.com> Received: from gw.homedecorators.com ([172.23.217.142]) by kwcorp.com ([192.168.85.25]) with ESMTP (TREND IMSS SMTP Service 7.0) id 7d801c54000bbbc1 ; Fri, 9 Jul 2010 14:28:18 -0500 Received: from HDC_MTA-MTA by gw.homedecorators.com with Novell_GroupWise; Fri, 09 Jul 2010 14:28:21 -0500 Message-Id: <4C3731E0.2E1C.0042.0@homedecorators.com> X-Mailer: Novell GroupWise Internet Agent 7.0.2 HP Date: Fri, 09 Jul 2010 14:27:44 -0500 From: "Jorge Montero" To: "Kevin Grittner" Cc: Subject: Re: Need help in performance tuning. References: <4C369346.6010501@postnewspapers.com.au> <8760.1278650566@sss.pgh.pa.us> <1278680186.1914.36.camel@bnicholson-desktop> <4C36F57102000025000332F6@gw.wicourts.gov> <4C36FEAD02000025000332FC@gw.wicourts.gov> <4C371B82020000250003330D@gw.wicourts.gov> In-Reply-To: <4C371B82020000250003330D@gw.wicourts.gov> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__Part4B663D20.0__=" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201007/158 X-Sequence-Number: 39547 This is a MIME message. If you are reading this text, you may want to consider changing to a mail reader or gateway that understands how to properly handle MIME multipart messages. --=__Part4B663D20.0__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable If anything was built in the database to handle such connections, I'd = recommend a big, bold warning, recommending the use of client-side pooling = if available. For something like, say, a web-server, pooling connections = to the database provides a massive performance advantage regardless of how = good the database is at handling way more active queries than the hardware = can handle: The assignment of a connection to a thread tends to be at = least an order of magnitude cheaper than establishing a new connection for = each new thread, and destroying it when it dies. This is especially true = if the client architecture relies in relatively short lived threads. =20 While there are a few cases where pooling is counter productive, this only = happens in relatively few scenarios. This is why every java application = server out there wil strongly recommend using its own facilities to = connect to a database: The performance is almost always better, and it = provides less headaches to the DBAs. =20 Now, if remote clients are accessing your database directly, setting up a = pool inbetween might not be as straightforward or give you the same gains = across the board, and that might be the only case where letting the db do = its own pooling makes sense. >>> "Kevin Grittner" 7/9/2010 12:52 PM >>> Matthew Wakeling wrote: > On Fri, 9 Jul 2010, Kevin Grittner wrote: >>> Interesting idea. As far as I can see, you are suggesting >>> solving the too many connections problem by allowing lots of >>> connections, but only allowing a certain number to do anything >>> at a time? >> >> Right. >=20 > I think in some situations, this arrangement would be an > advantage. However, I do not think it will suit the majority of > situations, and could reduce the performance when the user doesn't > need the functionality, either because they have a pool already, > or they don't have many connections. Oh, totally agreed, except that I think we can have essentially nil impact if they don't exceed a configured limit. In my experience, pooling is more effective the closer you put it to the client. I suppose the strongest argument that could be made against building in some sort of pooling is that it doesn't encourage people to look for client-side solutions. However, we seem to get a lot of posts from people who don't do this, are not able to easily manage it, and who would benefit from even a simple solution like this. -Kevin --=20 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance --=__Part4B663D20.0__= Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Description: HTML
If anything was built in the database to handle such connections, I'd = recommend a big, bold warning, recommending the use of client-side pooling = if available. For something like, say, a web-server, pooling connections = to the database provides a massive performance advantage regardless of how = good the database is at handling way more active queries than the hardware = can handle: The assignment of a connection to a thread tends to be at = least an order of magnitude cheaper than establishing a new connection for = each new thread, and destroying it when it dies. This is especially true = if the client architecture relies in relatively short lived threads.
 
While there are a few cases where pooling is counter productive, this = only happens in relatively few scenarios. This is why every java applicatio= n server out there wil strongly recommend using its own facilities to = connect to a database: The performance is almost always better, and it = provides less headaches to the DBAs.
 
Now, if remote clients are accessing your database directly, setting = up a pool inbetween might not be as straightforward or give you the same = gains across the board, and that might be the only case where letting the = db do its own pooling makes sense.

>>> "Kevin Grittner" = <Kevin.Grittner@wicourts.gov> 7/9/2010 12:52 PM >>>
Matth= ew Wakeling <matthew@flymine.org> wrote:
> On Fri, 9 Jul 2010, = Kevin Grittner wrote:
>>> Interesting idea. As far as I can = see, you are suggesting
>>> solving the too many connections = problem by allowing lots of
>>> connections, but only allowing = a certain number to do anything
>>> at a time?
>>
&= gt;> Right.
>
> I think in some situations, this arrangemen= t would be an
> advantage.  However, I do not think it will = suit the majority of
> situations, and could reduce the performance = when the user doesn't
> need the functionality, either because they = have a pool already,
> or they don't have many connections.

Oh= , totally agreed, except that I think we can have essentially nil
impact= if they don't exceed a configured limit.  In my experience,
poolin= g is more effective the closer you put it to the client.  I
suppose= the strongest argument that could be made against building
in some = sort of pooling is that it doesn't encourage people to look
for = client-side solutions.  However, we seem to get a lot of posts
from= people who don't do this, are not able to easily manage it, and
who = would benefit from even a simple solution like this.

-Kevin

-= -
Sent via pgsql-performance mailing list (pgsql-performance@postgresql= .org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-pe= rformance
--=__Part4B663D20.0__=--