Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 04144477478 for ; Fri, 17 Jan 2003 12:56:06 -0500 (EST) Received: from candle.pha.pa.us (momjian.navpoint.com [207.106.42.251]) by postgresql.org (Postfix) with ESMTP id 91EA9477425 for ; Fri, 17 Jan 2003 12:52:38 -0500 (EST) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.10.1) id h0HHqLR27486; Fri, 17 Jan 2003 12:52:21 -0500 (EST) From: Bruce Momjian Message-Id: <200301171752.h0HHqLR27486@candle.pha.pa.us> Subject: Re: 7.3.1 New install, large queries are slow In-Reply-To: <13109.1042824791@sss.pgh.pa.us> To: Tom Lane Date: Fri, 17 Jan 2003 12:52:21 -0500 (EST) Cc: Josh Berkus , Roman Fail , sszabo@megazone23.bigpanda.com, pgsql-performance@postgresql.org X-Mailer: ELM [version 2.4ME+ PL99 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/188 X-Sequence-Number: 835 Tom Lane wrote: > "Josh Berkus" writes: > >>> shared_buffers = 131072 > >> > >> Yipes! Try about a tenth that much. Or less. > > > Why? He has 4GB RAM on the machine. > > I think a gig of shared buffers is overkill no matter what. > > 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. > > There's another issue, which is somewhat platform-dependent; I'm not > sure if it applies to whatever OS Roman is using. But if you have a > machine where SysV shared memory is not locked into RAM, then a huge > shared buffer arena creates the probability that some of it will be > touched seldom enough that the kernel will decide to swap it out. When > that happens, you *really* pay through the nose --- a page that you > might have been able to get from kernel cache without incurring I/O will > now certainly cost you I/O to touch. It's even worse if the buffer > contained a dirty page at the time it was swapped out --- now that page > is going to require being read back in and written out again, a net cost > of three I/Os where there should have been one. Bottom line is that > shared_buffers should be kept small enough that the space all looks like > a hot spot to the kernel's memory allocation manager. Just as a data point, I believe other database systems recommend very large shared memory areas if a lot of data is being accessed. I seem to remember Informix doing that. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073