X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9B5A1DB77A for ; Fri, 18 Nov 2005 12:13:18 -0400 (AST) 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 54914-01 for ; Fri, 18 Nov 2005 16:13:16 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtp104.rog.mail.re2.yahoo.com (smtp104.rog.mail.re2.yahoo.com [206.190.36.82]) by svr1.postgresql.org (Postfix) with SMTP id 9C4A4DBAAE for ; Fri, 18 Nov 2005 12:13:14 -0400 (AST) Received: (qmail 29651 invoked from network); 18 Nov 2005 16:13:14 -0000 Received: from unknown (HELO phlogiston.dydns.org) (a.sullivan@rogers.com@209.222.54.227 with login) by smtp104.rog.mail.re2.yahoo.com with SMTP; 18 Nov 2005 16:13:14 -0000 Received: by phlogiston.dydns.org (Postfix, from userid 1000) id B888740B5; Fri, 18 Nov 2005 11:13:12 -0500 (EST) Date: Fri, 18 Nov 2005 11:13:12 -0500 From: Andrew Sullivan To: Guillaume Smet Cc: Andrew Sullivan , pgsql-performance@postgresql.org Subject: Re: weird performances problem Message-ID: <20051118161312.GC28967@phlogiston.dyndns.org> References: <437CC21D.8070402@openwide.fr> <20051117221314.GC26696@phlogiston.dyndns.org> <437D13AA.4010309@openwide.fr> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <437D13AA.4010309@openwide.fr> User-Agent: Mutt/1.5.9i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[AWL=0.000] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200511/376 X-Sequence-Number: 15633 On Fri, Nov 18, 2005 at 12:35:06AM +0100, Guillaume Smet wrote: > sort_mem = 32768 I would be very suspicious of that much memory for sort. Please see the docs for what that does. That is the amount that _each sort_ can allocate before spilling to disk. If some set of your users are causing complicated queries with, say, four sorts apiece, then each user is potentially allocating 4x that much memory. That's going to wreak havoc on your disk buffers (which are tricky to monitor on most systems, and impossible on some). This'd be the first knob I'd twiddle, for sure. A -- Andrew Sullivan | ajs@crankycanuck.ca It is above all style through which power defers to reason. --J. Robert Oppenheimer