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: Mon, 28 Apr 2008 14:16:02 -0400 (EDT)
Message-ID: <Pine.GSO.4.64.0804281404410.14083@westnet.com> (raw)
In-Reply-To: <20080428172431.GI6622@renesys.com>
References: <20080428172431.GI6622@renesys.com>
On Mon, 28 Apr 2008, John Rouillard wrote:
> 2008-04-21 11:36:43 UTC @(2761)i: LOG: checkpoints ... (27 seconds apart)
> so I changed:
> checkpoint_segments = 30
> checkpoint_warning = 150
That's good, but you might go higher than 30 for a bulk loading operation
like this, particularly on 8.1 where checkpoints are no fun. Using 100 is
not unreasonable.
> shared_buffers = 3000
> I don't see any indication in the docs that increasing shared memory
> would help speed up a copy operation.
The index blocks use buffer space, and what ends up happening if there's
not enough memory is they are written out more than they need to be (and
with your I/O hardware you need to avoid writes unless absolutely
necessary). Theoretically the OS is caching around that situation but
better to avoid it. You didn't say how much RAM you have, but you should
start by a factor of 10 increase to 30,000 and see if that helps; if so,
try making it large enough to use 1/4 of total server memory. 3000 is
only giving the server 24MB of RAM to work with, and it's unfair to expect
it to work well in that situation.
While not relevant to this exercise you'll need to set
effective_cache_size to a useful value one day as well.
--
* 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.0804281404410.14083@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