SEO

Jeff, Ron,

First off, Jeff, please take it easy. We're discussing 8.2 features at
this point and there's no reason to get stressed out at Ron. You can
get plenty stressed out when 8.2 is near feature freeze. ;-)


Regarding use cases for better sorts:

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.

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.

Following an index creation, we see that 95% of the time required is the
external sort, which averages 2mb/s. 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.


--Josh Berkus



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Thu, Sep 29, 2005 at 10:06:52AM -0700, Luke Lonergan wrote:
> Josh,
>
> On 9/29/05 9:54 AM, "Josh Berkus" <josh@agliodbs.com> wrote:
>
> > Following an index creation, we see that 95% of the time required
> > is the external sort, which averages 2mb/s. 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.
>
> Yuuuup! That about sums it up - regardless of taking 1 or 2 passes
> through the heap being sorted, 1.5 - 2 MB/s is the wrong number.
> This is not necessarily an algorithmic problem, but is a
> optimization problem with Postgres that must be fixed before it can
> be competitive.
>
> We read/write to/from disk at 240MB/s and so 2 passes would run at a
> net rate of 120MB/s through the sort set if it were that efficient.
>
> Anyone interested in tackling the real performance issue? (flame
> bait, but for a worthy cause :-)

I'm not sure that it's flamebait, but what do I know? Apart from the
nasty number (1.5-2 MB/s), what other observations do you have to
hand? Any ideas about what things are not performing here? Parts of
the code that could bear extra scrutiny? Ideas on how to fix same in
a cross-platform way?

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Josh Berkus <josh@agliodbs.com> writes:
> 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.
> ...
> Following an index creation, we see that 95% of the time required is the
> external sort, which averages 2mb/s. 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.

These numbers don't seem to add up. You have not provided any details
about the index key datatypes or sizes, but I'll take a guess that the
raw data for each index is somewhere around 10GB. The theory says that
the runs created during the first pass should on average be about twice
work_mem, so at 128mb work_mem there should be around 40 runs to be
merged, which would take probably three passes with six-way merging.
Raising work_mem to a gig should result in about five runs, needing only
one pass, which is really going to be as good as it gets. If you could
not see any difference then I see little hope for the idea that reducing
the number of merge passes will help.

Umm ... you were raising maintenance_work_mem, I trust, not work_mem?

We really need to get some hard data about what's going on here. The
sort code doesn't report any internal statistics at the moment, but it
would not be hard to whack together a patch that reports useful info
in the form of NOTICE messages or some such.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Tom,

> Raising work_mem to a gig should result in about five runs, needing only
> one pass, which is really going to be as good as it gets. If you could
> not see any difference then I see little hope for the idea that reducing
> the number of merge passes will help.

Right. It *should have*, but didn't seem to. Example of a simple sort
test of 100 million random-number records

1M 3294 seconds
16M 1107 seconds
256M 1209 seconds
512M 1174 seconds
512M with 'not null' for column that is indexed 1168 seconds

> Umm ... you were raising maintenance_work_mem, I trust, not work_mem?

Yes.

>
> We really need to get some hard data about what's going on here. The
> sort code doesn't report any internal statistics at the moment, but it
> would not be hard to whack together a patch that reports useful info
> in the form of NOTICE messages or some such.

Yeah, I'll do this as soon as the patch is finished. Always useful to
gear up the old TPC-H.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster