From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Greg Smith <gsmith@gregsmith.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: TPC-H Scaling Factors X PostgreSQL Cluster Command
Date: Tue, 24 Apr 2007 09:52:38 +0100
Message-ID: <462DC556.5080601@enterprisedb.com> (raw)
In-Reply-To: <Pine.GSO.4.64.0704232331380.7149@westnet.com>
References: <d34b24380704210754q53aa86c8l3a63211d81febbf7@mail.gmail.com>
<Pine.GSO.4.64.0704232331380.7149@westnet.com>
Greg Smith wrote:
> On Sat, 21 Apr 2007, Nelson Kotowski wrote:
>
>> I identified that the cluster command over the lineitem table (cluster
>> idx_lineitem on lineitem) is the responsible. I got to this conclusion
>> because when i run it in the 1GB and 2GB database i am able to
>> complete this script in 10 and 30 minutes each. But when i run this
>> command over the 5GB database, it simply seems as it won't end.
>
> Have you looked in the database log files for messages? Unless you
> changed some other parameters from the defaults that you didn't mention,
> I'd expect you've got a constant series of "checkpoint occuring too
> frequently" errors in there, which would be a huge slowdown on your
> index rebuild. Slowdowns from checkpoints would get worse with an
> increase of shared_buffers, as you report.
Index builds don't write WAL, unless archive_command has been set. A
higher shared_buffers setting can hurt index build performance, but for
a different reason: the memory spent on shared_buffers can't be used for
sorting and caching the sort tapes.
> The default setting for checkpoint_segments of 3 is extremely low for
> even a 1GB database. Try increasing that to 30, restart the server, and
> rebuild the index to see how much the 1GB case speeds up. If it's
> significantly faster (it should be), try the 5GB one again.
A good advice, but it's unlikely to make a difference at load time.
BTW: With CVS HEAD, if you create the table in the same transaction (or
TRUNCATE) as you load the data, the COPY will skip writing WAL which can
give a nice speedup.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
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: heikki@enterprisedb.com, gsmith@gregsmith.com
Subject: Re: TPC-H Scaling Factors X PostgreSQL Cluster Command
In-Reply-To: <462DC556.5080601@enterprisedb.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