pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Greg Smith <gsmith@gregsmith.com>
To: pgsql-performance@postgresql.org
Subject: Re: TPC-H Scaling Factors X PostgreSQL Cluster Command
Date: Mon, 23 Apr 2007 23:39:42 -0400 (EDT)
Message-ID: <Pine.GSO.4.64.0704232331380.7149@westnet.com> (raw)
In-Reply-To: <d34b24380704210754q53aa86c8l3a63211d81febbf7@mail.gmail.com>
References: <d34b24380704210754q53aa86c8l3a63211d81febbf7@mail.gmail.com>

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.

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.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD



view thread (6+ messages)  latest in thread

Message-ID: <Pine.GSO.4.64.0704232331380.7149@westnet.com>
Permalink:  ../Pine.GSO.4.64.0704232331380.7149@westnet.com/
Also on:    postgresql.org/message-id/Pine.GSO.4.64.0704232331380.7149@westnet.com

 · 

reply
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
  Subject: Re: TPC-H Scaling Factors X PostgreSQL Cluster Command
  In-Reply-To: <Pine.GSO.4.64.0704232331380.7149@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