Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id B1D8F6333C4 for ; Fri, 30 Jul 2010 15:07:12 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 96405-05-3 for ; Fri, 30 Jul 2010 18:07:04 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id B7DC16333BA for ; Fri, 30 Jul 2010 15:07:03 -0300 (ADT) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Fri, 30 Jul 2010 13:07:02 -0500 Message-Id: <4C52CE6F0200002500033F21@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Fri, 30 Jul 2010 13:06:55 -0500 From: "Kevin Grittner" To: "Craig James" Cc: Subject: Re: Connection pooling for a mixture of lightweight and heavyweight jobs? References: <4C50ABA5.5010408@emolecules.com> <4C52B0240200002500033F10@gw.wicourts.gov> <4C530758.7050804@emolecules.com> <4C52C7790200002500033F1C@gw.wicourts.gov> <4C5310C4.1040906@emolecules.com> In-Reply-To: <4C5310C4.1040906@emolecules.com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.311 tagged_above=-10 required=5 tests=BAYES_40=-0.001, RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201007/225 X-Sequence-Number: 35244 Craig James wrote: > I'm not clear on how connection pooling would help this problem. > I would have 100 lightweight backends, whether they were pooled or > not, always sitting around. Not if you use a good pooler with the right configuration. You should have 10 to 20 database connections which the lightweight query requests share. You want something which will queue requests above that maximum, and immediately release one more request when a previous request completes. We have this built in to our application framework, so I'm not familiar with how to configure pgbouncer or pgpool, but from what I've heard I would expect both of these to support such behavior. (Someone with more experience with one or both of these products, please jump in here....) At this point some people protest that they can't let requests queue up like that because they need fast response time. Trust me; if you set up a good connection pool to the right size, this *will* give you much better throughput *and* response time than letting the thundering herd loose to compete for resources. -Kevin