Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 5A0754763AE for ; Fri, 17 Jan 2003 13:03:25 -0500 (EST) Received: from mail.libertyrms.com (unknown [209.167.124.227]) by postgresql.org (Postfix) with ESMTP id B65164773A6 for ; Fri, 17 Jan 2003 12:59:39 -0500 (EST) Received: from andrew by mail.libertyrms.com with local (Exim 3.22 #3 (Debian)) id 18ZamJ-0000pY-00 for ; Fri, 17 Jan 2003 12:59:39 -0500 Date: Fri, 17 Jan 2003 12:59:39 -0500 From: Andrew Sullivan To: pgsql-performance@postgresql.org Subject: Re: 7.3.1 New install, large queries are slow Message-ID: <20030117125939.J23422@mail.libertyrms.com> Mail-Followup-To: Andrew Sullivan , pgsql-performance@postgresql.org References: <13109.1042824791@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i In-Reply-To: <13109.1042824791@sss.pgh.pa.us>; from tgl@sss.pgh.pa.us on Fri, Jan 17, 2003 at 12:33:11PM -0500 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/189 X-Sequence-Number: 836 On Fri, Jan 17, 2003 at 12:33:11PM -0500, Tom Lane wrote: > One reason not to crank up shared_buffers "just because you can" is that > there are operations (such as CHECKPOINT) that have to scan through all > the buffers, linearly. I don't *think* any of these are in > performance-critical paths, but nonetheless you're wasting CPU. I trust > the kernel to manage a huge number of buffers efficiently more than I > trust Postgres. For what it's worth, we have exactly that experience on our Sun E4500s. I had machines with 12 gig I was testing on, and I increased the buffers to 2 Gig, because truss was showing us some sluggishness in the system was tripping on the system call to get a page. It was satisifed right away by the kernel's cache, but the system call was still the most expensive part of the operation. After we'd increased the shared buffers, however, performance _degraded_ considerably. It now spent all its time instead managing the huge shared buffer, and the cost of that was much worse than the cost of the system call. So it is extremely dependent on the efficiency of PostgreSQL's use of shared memory as compared to the efficiency of the system call. A -- ---- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada M2P 2A8 +1 416 646 3304 x110