From: Mark Thornton <mthornton@optrak.com>
To: sthomas@optionshouse.com
To: pgsql-performance@postgresql.org
Subject: Re: Performance of CLUSTER
Date: Mon, 11 Jun 2012 15:02:23 +0100
Message-ID: <4FD5FA6F.3080903@optrak.com> (raw)
In-Reply-To: <4FD5F837.5080006@optionshouse.com>
References: <4FD458CA.3070902@optrak.com>
<4FD5F5DC.3080507@optionshouse.com>
<4FD5F6B5.2090505@optrak.com>
<4FD5F837.5080006@optionshouse.com>
On 11/06/12 14:52, Shaun Thomas wrote:
> On 06/11/2012 08:46 AM, Mark Thornton wrote:
>
>> 500m --- though isn't clear if cluster uses maintenance memory or the
>> regular work memory. I could readily use a higher value for
>> maintenance_work_mem.
>
> For an operation like that, having a full GB wouldn't hurt. Though if
> you haven't already, you might think about pointing
I didn't think the process was using even the 500m it ought to have had
available, whereas creating an index did appear to use that much. Note
though that I didn't stay up all night watching it!
> your pgsql_tmp to /dev/shm for a while, even for just this operation.
>
> Then again, is your CPU at 100% during the entire operation?
No the CPU utilization is quite low. Most of the time is waiting for IO.
> If it's not fetching anything from disk or writing out much, reducing
> IO won't help. :) One deficiency we've had with CLUSTER is that it's a
> synchronous operation. It does each thing one after the other. So
> it'll organize the table contents, then it'll reindex each index
> (including the primary key) one after the other. If you have a lot of
> those, that can take a while, especially if any composite or complex
> indexes exist.
In this case there are only two indexes, the gist one and a primary key
(on a bigint value).
>
> You might actually be better off running parallel REINDEX commands on
> the table (I wrote a script for this because we have a 250M row table
> that each index takes 1-2.5 hours to build). You might also consider
> pg_reorg, which seems to handle some parts of a table rebuild a little
> better.
>
> That should give you an escalation pattern, though. :)
Thanks for your help,
Mark Thornton
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: mthornton@optrak.com, sthomas@optionshouse.com
Subject: Re: Performance of CLUSTER
In-Reply-To: <4FD5FA6F.3080903@optrak.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