Message-ID: <4FD5FA6F.3080903@optrak.com>
Date: Mon, 11 Jun 2012 15:02:23 +0100
From: Mark Thornton <mthornton@optrak.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:12.0) Gecko/20120430 Thunderbird/12.0.1
MIME-Version: 1.0
To: sthomas@optionshouse.com, pgsql-performance@postgresql.org
Subject: Re: Performance of CLUSTER
References: <4FD458CA.3070902@optrak.com> <4FD5F5DC.3080507@optionshouse.com>
	<4FD5F6B5.2090505@optrak.com> <4FD5F837.5080006@optionshouse.com>
In-Reply-To: <4FD5F837.5080006@optionshouse.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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