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 964AFD1B17B for ; Wed, 4 Aug 2004 11:51:44 -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 61590-02 for ; Wed, 4 Aug 2004 14:51:49 +0000 (GMT) Received: from stubee.d2hosting.net (d2hosting.net [66.70.41.160]) by svr1.postgresql.org (Postfix) with ESMTP id B94C1D1B1C8 for ; Wed, 4 Aug 2004 11:51:37 -0300 (ADT) Received: from [127.0.0.1] (stubee.d2hosting.net [66.70.41.160]) by stubee.d2hosting.net (8.11.6/linuxconf) with ESMTP id i74EpJi24735; Wed, 4 Aug 2004 09:51:19 -0500 Message-ID: <4110F7E0.4050204@idigx.com> Date: Wed, 04 Aug 2004 09:51:12 -0500 From: Thomas Swan User-Agent: Mozilla Thunderbird 0.7.2 (Windows/20040707) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Janning Vygen Cc: pgsql-performance@postgresql.org, Paul Serby Subject: Re: The black art of postgresql.conf tweaking References: <4110DA83.7020505@clockltd.com> <200408041526.30460.vygen@gmx.de> In-Reply-To: <200408041526.30460.vygen@gmx.de> X-Enigmail-Version: 0.85.0.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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/53 X-Sequence-Number: 7689 Janning Vygen wrote: >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 >> The number of connections in apache and in postgresql are the keys. From what you've described, apache can have up to 300 child processes. If this application uses a different identity or db for different connects then you may have more than one connection open per process easily exhausting your available connections. Also, your application may open multiple connections to postgresql per process. See if setting max_connections in postgres to a larger works, but you may want to reduce your sort_mem proportionately to keep from overbooking your system. >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 > > KeepAlive for 2 or 3 seconds is quite sufficient. This keeps the current number of connections down for those browsers that support it, and keeps the server from leaving too many open. We found KeepAlive off caused too many http connections to be opened and closed for our applications and hardware to keep up. The benefit is facilitating a rapid succession of requests: page loads, graphics, embedded objects, frames, etc. >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. > > Good advice.