pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron Peacetree <rjpeace@earthlink.net>
To: Josh Berkus <josh@agliodbs.com>
To: pgsql-hackers@postgresql.org
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] A Better External Sort?
Date: Fri, 30 Sep 2005 01:24:30 -0400 (EDT)
Message-ID: <28561251.1128057870608.JavaMail.root@elwamui-polski.atl.sa.earthlink.net> (raw)

>From: Josh Berkus <josh@agliodbs.com>
>Sent: Sep 29, 2005 12:54 PM
>Subject: Re: [HACKERS] [PERFORM] A Better External Sort?
>
>The biggest single area where I see PostgreSQL external
>sort sucking is on index creation on large tables.   For
>example, for free version of TPCH, it takes only 1.5 hours to
>load a 60GB Lineitem table on OSDL's hardware, but over 3
>hours to create each index on that table.  This means that
>over all our load into TPCH takes 4 times as long to create 
>the indexes as it did to bulk load the data.
>
Hmmm.
60GB/5400secs= 11MBps.  That's ssllooww.  So the first
problem is evidently our physical layout and/or HD IO layer
sucks.

Creating the table and then creating the indexes on the table
is going to require more physical IO than if we created the
table and the indexes concurrently in chunks and then
combined the indexes on the chunks into the overall indexes
for the whole table, so there's a potential speed-up.

The method I've been talking about is basically a recipe for
creating indexes as fast as possible with as few IO operations,
HD or RAM, as possible and nearly no random ones, so it
could help as well.

OTOH, HD IO rate is the fundamental performance metric.
As long as our HD IO rate is pessimal, so will the performance
of everything else be.   Why can't we load a table at closer to
the peak IO rate of the HDs?   


>Anyone restoring a large database from pg_dump is in the
>same situation.  Even worse, if you have to create a new
>index on a large table on a production database in use,
>because the I/O from the index creation swamps everything.
>
Fix for this in the works ;-)


>Following an index creation, we see that 95% of the time
>required is the external sort, which averages 2mb/s.
>
Assuming decent HD HW, this is HORRIBLE.

What's kind of instrumenting and profiling has been done of
the code involved?


>This is with seperate drives for the WAL, the pg_tmp, the table
>and the index.  I've confirmed that increasing work_mem 
>beyond a small minimum (around 128mb) had no benefit on
>the overall index creation speed.
>
No surprise.  The process is severely limited by the abyssmally
slow HD IO.

Ron



view thread (112+ messages)  latest in thread

Message-ID: <28561251.1128057870608.JavaMail.root@elwamui-polski.atl.sa.earthlink.net>
Permalink:  ../28561251.1128057870608.JavaMail.root@elwamui-polski.atl.sa.earthlink.net/
Also on:    postgresql.org/message-id/28561251.1128057870608.JavaMail.root@elwamui-polski.atl.sa.earthlink.net

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: rjpeace@earthlink.net, josh@agliodbs.com, pgsql-hackers@postgresql.org
  Subject: Re: [PERFORM] A Better External Sort?
  In-Reply-To: <28561251.1128057870608.JavaMail.root@elwamui-polski.atl.sa.earthlink.net>
  References: <28561251.1128057870608.JavaMail.root@elwamui-polski.atl.sa.earthlink.net>

* 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