From: PFC <lists@peufeu.com>
To: Jim C. Nasby <decibel@decibel.org>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Feature suggestion : FAST CLUSTER
Date: Sun, 27 May 2007 19:34:30 +0200
Message-ID: <op.tszxrsircigqcu@apollo13> (raw)
In-Reply-To: <20070527155338.GM92628@nasby.net>
References: <op.tspwemefcigqcu@apollo13>
<20070527155338.GM92628@nasby.net>
On Sun, 27 May 2007 17:53:38 +0200, Jim C. Nasby <decibel@decibel.org>
wrote:
> On Tue, May 22, 2007 at 09:29:00AM +0200, PFC wrote:
>> This does not run a complete sort on the table. It would be about as
>> fast as your seq scan disk throughput. Obviously, the end result is
>> not as
>> good as a real CLUSTER since the table will be made up of several
>> ordered
>> chunks and a range lookup. Therefore, a range lookup on the clustered
>> columns would need at most N seeks, versus 1 for a really clustered
>> table.
>> But it only scans the table once and writes it once, even counting index
>> rebuild.
>
> Do you have any data that indicates such an arrangement would be
> substantially better than less-clustered data?
While the little benchmark that will answer your question is running,
I'll add a few comments :
I have been creating a new benchmark for PostgreSQL and MySQL, that I
will call the Forum Benchmark. It mimics the activity of a forum.
So far, I have got interesting results about Postgres and InnoDB and will
publish an extensive report with lots of nasty stuff in it, in, say, 2
weeks, since I'm doing this in spare time.
Anyway, forums like clustered tables, specifically clusteriing posts on
(topic_id, post_id), in order to be able to display a page with one disk
seek, instead of one seek per post.
PostgreSQL humiliates InnoDB on CPU-bound workloads (about 2x faster
since I run it on dual core ; InnoDB uses only one core). However, InnoDB
can automatically cluster tables without maintenance. This means InnoDB
will, even though it sucks and is awfully bloated, run a lot faster than
postgres if things become IO-bound, ie. if the dataset is larger than RAM.
Postgres needs to cluster the posts table in order to keep going. CLUSTER
is very slow. I tried inserting into a new posts table, ordering by
(post_id, topic_id), then renaming the new table in place of the old. It
is faster, but still slow when handling lots of data.
I am trying other approaches, some quite hack-ish, and will report my
findings.
Regards
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: lists@peufeu.com, decibel@decibel.org
Subject: Re: Feature suggestion : FAST CLUSTER
In-Reply-To: <op.tszxrsircigqcu@apollo13>
* 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