Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 715F92E0040 for ; Tue, 29 Apr 2008 12:58:23 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 61951-07 for ; Tue, 29 Apr 2008 12:58:01 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from westnet.com (westnet.com [216.187.52.2]) by developer.postgresql.org (Postfix) with ESMTP id BF66A2E002D for ; Tue, 29 Apr 2008 12:58:01 -0300 (ADT) Received: from westnet.com (localhost [127.0.0.1]) by westnet.com (8.14.0/8.14.0) with ESMTP id m3TFw1p7018218; Tue, 29 Apr 2008 11:58:01 -0400 (EDT) Received: from localhost (gsmith@localhost) by westnet.com (8.14.0/8.13.2/Submit) with ESMTP id m3TFw00s018204; Tue, 29 Apr 2008 11:58:00 -0400 (EDT) X-Authentication-Warning: westnet.com: gsmith owned process doing -bs Date: Tue, 29 Apr 2008 11:58:00 -0400 (EDT) From: Greg Smith X-X-Sender: gsmith@westnet.com To: John Rouillard cc: pgsql-performance@postgresql.org Subject: Re: Very poor performance loading 100M of sql data using copy In-Reply-To: <20080429151622.GP6622@renesys.com> Message-ID: References: <20080428172431.GI6622@renesys.com> <20080429151622.GP6622@renesys.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/447 X-Sequence-Number: 29940 On Tue, 29 Apr 2008, John Rouillard wrote: > So swap the memory usage from the OS cache to the postgresql process. > Using 1/4 as a guideline it sounds like 600,000 (approx 4GB) is a > better setting. So I'll try 300000 to start (1/8 of memory) and see > what it does to the other processes on the box. That is potentially a good setting. Just be warned that when you do hit a checkpoint with a high setting here, you can end up with a lot of data in memory that needs to be written out, and under 8.2 that can cause an ugly spike in disk writes. The reason I usually threw out 30,000 as a suggested starting figure is that most caching disk controllers can buffer at least 256MB of writes to keep that situation from getting too bad. Try it out and see what happens, just be warned that's the possible downside of setting shared_buffers too high and therefore you might want to ease into that more gradually (particularly if this system is shared with other apps). x -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD