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 C15BBD1B1AE for ; Wed, 4 Aug 2004 10:30:17 -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 38664-02 for ; Wed, 4 Aug 2004 13:30:21 +0000 (GMT) Received: from pw6tiger.de (pw6tiger.de [217.160.140.116]) by svr1.postgresql.org (Postfix) with SMTP id 4699CD1B1A1 for ; Wed, 4 Aug 2004 10:30:08 -0300 (ADT) Received: (qmail 22959 invoked by uid 505); 4 Aug 2004 13:29:51 -0000 Received: from vygen@gmx.de by planwerk6 by uid 89 with qmail-scanner-1.14 (virus scan: Clear:. Processed in 0.238747 secs); 04 Aug 2004 13:29:51 -0000 Received: from isi-dial-153-153.isionline-dialin.de (HELO ?192.168.1.10?) (vygen@planwerk6.de@195.158.153.153) by pw6tiger.de with SMTP; 4 Aug 2004 13:29:51 -0000 From: Janning Vygen To: pgsql-performance@postgresql.org Subject: Re: The black art of postgresql.conf tweaking Date: Wed, 4 Aug 2004 15:26:30 +0200 User-Agent: KMail/1.6.2 Cc: Paul Serby References: <4110DA83.7020505@clockltd.com> In-Reply-To: <4110DA83.7020505@clockltd.com> MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Message-Id: <200408041526.30460.vygen@gmx.de> 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/50 X-Sequence-Number: 7686 Am Mittwoch, 4. August 2004 14:45 schrieb Paul Serby: > Apache on the Web server can take up to 300 connections and PHP is using > pg_pconnect > > Postgres is set with the following. > > max_connections = 300 > shared_buffers = 38400 > sort_mem = 12000 > > But Apache is still maxing out the non-super user connection limit. for most websites 300 connections is far too much (imagine even 10 request per second for 10 hours a day ends up to 10.8 Mio pages a month) but anyway: you should first focus on closing your http connection to the user as fast as possible. then you dont need so much concurrent connections which keep db connections open and uses memory. I did the following: - apache: keepalive off - apache patch: lingerd (google for it) - apache mod_gzip - pg_pconnect this keeps your http connection as short as possible, so the apache child is ready to serve the next client. Imagine 5 seconds of keepalive 1 second on lingering half-closed tcp connections and 4 more seconds for transport of uncompressed content. in this scenario your apache child uses memory an your pooled db connection for 10 seconds while doing nothing! in my experience apache in standard configuration can be the main bottleneck. and teh combination of keepalive off, lingerd and mod_gzip is GREAT and i didn't found much sites propagating a configuration like this. kind regards, janning p.s: sorry for being slightly off topic and talking about apache but when it comes to performance it is always important to look at the complete system.