From: Greg Smith <gsmith@gregsmith.com>
To: John Rouillard <rouilj@renesys.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Very poor performance loading 100M of sql data using copy
Date: Tue, 29 Apr 2008 11:58:00 -0400 (EDT)
Message-ID: <Pine.GSO.4.64.0804291149450.8414@westnet.com> (raw)
In-Reply-To: <20080429151622.GP6622@renesys.com>
References: <20080428172431.GI6622@renesys.com>
<Pine.GSO.4.64.0804281404410.14083@westnet.com>
<20080429151622.GP6622@renesys.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gsmith@gregsmith.com, rouilj@renesys.com
Subject: Re: Very poor performance loading 100M of sql data using copy
In-Reply-To: <Pine.GSO.4.64.0804291149450.8414@westnet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox