Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 452F9476726 for ; Fri, 17 Jan 2003 13:03:58 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 16E93477395 for ; Fri, 17 Jan 2003 13:01:19 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h0HI195u013373; Fri, 17 Jan 2003 13:01:09 -0500 (EST) To: Bruce Momjian Cc: Josh Berkus , Roman Fail , sszabo@megazone23.bigpanda.com, pgsql-performance@postgresql.org Subject: Re: 7.3.1 New install, large queries are slow In-reply-to: <200301171752.h0HHqLR27486@candle.pha.pa.us> References: <200301171752.h0HHqLR27486@candle.pha.pa.us> Comments: In-reply-to Bruce Momjian message dated "Fri, 17 Jan 2003 12:52:21 -0500" Date: Fri, 17 Jan 2003 13:01:09 -0500 Message-ID: <13372.1042826469@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/190 X-Sequence-Number: 837 Bruce Momjian writes: > 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. Yeah, but isn't that theory a hangover from pre-Unix operating systems? In all modern Unixen, you can expect the kernel to make use of any spare RAM for disk buffer cache --- and that behavior makes it pointless for Postgres to try to do large amounts of its own buffering. Having a page in our own buffer instead of kernel buffer saves a context swap to access the page, but it doesn't save I/O, so the benefit is a lot less than you might think. I think there's seriously diminishing returns in pushing shared_buffers beyond a few thousand, and once you get to the point where it distorts the kernel's ability to manage memory for processes, you're really shooting yourself in the foot. regards, tom lane