X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id DF40C5E4699 for ; Sun, 8 Aug 2004 11:29:42 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 63246-10 for ; Sun, 8 Aug 2004 14:29:39 +0000 (GMT) Received: from bayswater1.ymogen.net (host-154-240-27-217.pobox.net.uk [217.27.240.154]) by svr1.postgresql.org (Postfix) with ESMTP id DBCFF5E46C2 for ; Sun, 8 Aug 2004 11:29:34 -0300 (ADT) Received: from solent (82-68-95-1.dsl.in-addr.zen.co.uk [82.68.95.1]) by bayswater1.ymogen.net (Postfix) with ESMTP id 4A6ABA55C5; Sun, 8 Aug 2004 15:29:39 +0100 (BST) Reply-To: From: "Matt Clark" To: "'Jan Wieck'" , "'Jeff'" Cc: "'Martin Foster'" , "'PostgreSQL Performance'" Subject: Re: Performance Bottleneck Date: Sun, 8 Aug 2004 15:29:39 +0100 Organization: Ymogen Ltd Message-ID: <007f01c47d54$233b4bf0$8300a8c0@solent> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 In-Reply-To: <41163001.8040500@Yahoo.com> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Importance: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/103 X-Sequence-Number: 7739 > And this is exactly where the pgpool advantage lies. > Especially with the > TPC-W, the Apache is serving a mix of PHP (or whatever CGI > technique is > used) and static content like images. Since the 200+ Apache > kids serve > any of that content by random and the emulated browsers very much > encourage it to ramp up MaxClients children by using up to 4 > concurrent > image connections, one does end up with MaxClients DB > connections that > are all relatively low frequently used. In contrast to that the real > pgpool causes lesser, more active DB connections, which is better for > performance. There are two well-worn and very mature techniques for dealing with the issue of web apps using one DB connection per apache process, both of which work extremely well and attack the issue at its source. 1) Use a front-end caching proxy like Squid as an accelerator. Static content will be served by the accelerator 99% of the time. Additionally, large pages can be served immediately to the accelerator by Apache, which can then go on to serve another request without waiting for the end user's dial-up connection to pull the data down. Massive speedup, fewer apache processes needed. 2) Serve static content off an entirely separate apache server than the dynamic content, but by using separate domains (e.g. 'static.foo.com'). Personally I favour number 1. Our last biggish peak saw 6000 open HTTP and HTTPS connections and only 200 apache children, all of them nice and busy, not hanging around on street corners looking bored. During quiet times Apache drops back to its configured minimum of 40 kids. Option 2 has the advantage that you can use a leaner build for the 'dynamic' apache server, but with RAM so plentiful these days that's a less useful property. Basically this puts the 'pooling' back in the stateless HTTP area where it truly belongs and can be proven not to have any peculiar side effects (especially when it comes to transaction safety). Even better, so long as you use URL parameters for searches and the like, you can have the accelerator cache those pages for a certain time too so long as slightly stale results are OK. I'm sure pgpool and the like have their place, but being band-aids for poorly configured websites probably isn't the best use for them. M