Message-ID: <4FD60447.1090802@optionshouse.com>
Date: Mon, 11 Jun 2012 09:44:23 -0500
From: Shaun Thomas <sthomas@optionshouse.com>
Reply-To: <sthomas@optionshouse.com>
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; Linux i686;
	rv:12.0) Gecko/20120430 Thunderbird/12.0.1
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <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>
	<4FD5FA6F.3080903@optrak.com> <4FD5FD5C.5040203@optionshouse.com>
	<4FD5FFEE.6000306@optrak.com>
In-Reply-To: <4FD5FFEE.6000306@optrak.com>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
content-transfer-encoding: 7bit

On 06/11/2012 09:25 AM, Mark Thornton wrote:

> Certainly not --- the server only has 5GB of memory. Nevertheless I
> don't expect quadratic behaviour for CLUSTER (n log n would be my
> expected time).

And there it is. :)

Since that's the case, *DO NOT* create the symlink from pgsql_tmp to 
/dev/shm like I suggested before. You don't have enough memory for that, 
and it will likely cause problems. I need to stop assuming everyone has 
huge servers. I know low-end laptops have 4GB of RAM these days, but 
servers have longer shelf-lives, and VMs can play larger roles.

So here's the thing, and I should have honestly realized it the second I 
noted the >100x jump in execution time. All of your previous tables fit 
in memory. Nice, speedy, >100x faster than disk, memory. It's not that 
the table is only 10x larger than other tables in your examples, it's 
that the entire thing doesn't fit in memory.

Since it can't just read the table and assume it's in memory, reads have 
a chance to fetch from disk. Since it's also maintaining several 
temporary files for the new index and replacement table structures, it's 
fighting for random reads and writes during the whole process. That's in 
addition to any transaction log traffic and checkpoints since the 
process will span several.

Actually, your case is a good illustration of how memory and 
high-performance IO devices can reduce maintenance costs. If you played 
around with steadily increasing table sizes, I bet you could even find 
the exact row count and table size where the table no longer fits in 
PostgreSQL or OS cache, and suddenly takes 100x longer to process. That 
kind of steady table growth is often seen in databases, and admins 
sometimes see this without understanding why it happens.

-- 
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email