Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id D836A2E0067 for ; Tue, 8 Apr 2008 19:36:15 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 99077-03 for ; Tue, 8 Apr 2008 19:36:13 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from h07.llord.com (unknown [195.140.142.194]) by developer.postgresql.org (Postfix) with ESMTP id 197572E0054 for ; Tue, 8 Apr 2008 19:36:13 -0300 (ADT) Received: from par69-8-88-161-102-87.fbx.proxad.net ([88.161.102.87] helo=apollo13.peufeu.com) by h07.llord.com with esmtpa (Exim 4.68) (envelope-from ) id 1JjMQD-0005UA-Sr; Wed, 09 Apr 2008 00:36:10 +0200 Date: Wed, 09 Apr 2008 00:40:16 +0200 To: "Mark Stosberg" , pgsql-performance@postgresql.org Subject: Re: recommendations for web/db connection pooling or DBD::Gofer reviews From: PFC Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8 MIME-Version: 1.0 References: Content-Transfer-Encoding: 7bit Message-ID: In-Reply-To: User-Agent: Opera Mail/9.24 (Linux) X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - h07.llord.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - peufeu.com X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.973 tagged_above=0 required=5 tests=AWL=0.873, RDNS_NONE=0.1 X-Spam-Level: X-Archive-Number: 200804/96 X-Sequence-Number: 29589 > When traffic to our PostgreSQL-backed website spikes, the first resource > we see being exhausted is the DB slots on the master server (currently > set to about 400). > > I expect that as new Apache/mod_perl children are being put to us, they > are creating new database connections. > > I'm interested in recommendations to funnel more of that traffic through > fewer DB slots, if that's possible. (We could also consider increasing > the handles available, since the DB server has some CPU and memory to > spare). > > I'm particularly interested in review of DBD::Gofer, which seems like it > would help with this in our Perl application: > http://search.cpan.org/dist/DBI/lib/DBD/Gofer.pm > > I realize it has limitations, like "no transactions", but I think we > would still able to use it selectively in our application. Under heavy load, Apache has the usual failure mode of spawning so many threads/processes and database connections that it just exhausts all the memory on the webserver and also kills the database. As usual, I would use lighttpd as a frontend (also serving static files) to handle the large number of concurrent connections to clients, and then have it funnel this to a reasonable number of perl backends, something like 10-30. I don't know if fastcgi works with perl, but with PHP it certainly works very well. If you can't use fastcgi, use lighttpd as a HTTP proxy and apache with mod_perl behind. Recipe for good handling of heavy load is using an asynchronous server (which by design can handle any number of concurrent connections up to the OS' limit) in front of a small number of dynamic webpage generating threads/processes.