From: Jignesh K. Shah <J.K.Shah@Sun.COM>
To: Ron Peacetree <rjpeace@earthlink.net>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-hackers@postgresql.org
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] A Better External Sort?
Date: Fri, 30 Sep 2005 16:38:00 -0400
Message-ID: <433DA228.4060103@sun.com> (raw)
In-Reply-To: <31833713.1128111650174.JavaMail.root@elwamui-polski.atl.sa.earthlink.net>
References: <31833713.1128111650174.JavaMail.root@elwamui-polski.atl.sa.earthlink.net>
I have seen similar performance as Josh and my reasoning is as follows:
* WAL is the biggest bottleneck with its default size of 16MB. Many
people hate to recompile the code to change its default, and
increasing checkpoint segments help but still there is lot of overhead
in the rotation of WAL files (Even putting WAL on tmpfs shows that it is
still slow). Having an option for bigger size is helpful to a small
extent percentagewise (and frees up CPU a bit in doing file rotation)
* Growing files: Even though this is OS dependent but it does spend lot
of time doing small 8K block increases to grow files. If we can signal
bigger chunks to grow or "pre-grow" to expected size of data files
that will help a lot in such cases.
* COPY command had restriction but that has been fixed to a large
extent.(Great job)
But ofcourse I have lost touch with programming and can't begin to
understand PostgreSQL code to change it myself.
Regards,
Jignesh
Ron Peacetree wrote:
>That 11MBps was your =bulk load= speed. If just loading a table
>is this slow, then there are issues with basic physical IO, not just
>IO during sort operations.
>
>As I said, the obvious candidates are inefficient physical layout
>and/or flawed IO code.
>
>Until the basic IO issues are addressed, we could replace the
>present sorting code with infinitely fast sorting code and we'd
>still be scrod performance wise.
>
>So why does basic IO suck so badly?
>
>Ron
>
>
>-----Original Message-----
>From: Josh Berkus <josh@agliodbs.com>
>Sent: Sep 30, 2005 1:23 PM
>To: Ron Peacetree <rjpeace@earthlink.net>
>Cc: pgsql-hackers@postgresql.org, pgsql-performance@postgresql.org
>Subject: Re: [HACKERS] [PERFORM] A Better External Sort?
>
>Ron,
>
>
>
>>Hmmm.
>>60GB/5400secs= 11MBps. That's ssllooww. So the first
>>problem is evidently our physical layout and/or HD IO layer
>>sucks.
>>
>>
>
>Actually, it's much worse than that, because the sort is only dealing
>with one column. As I said, monitoring the iostat our top speed was
>2.2mb/s.
>
>--Josh
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
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: J.K.Shah@Sun.COM, rjpeace@earthlink.net, josh@agliodbs.com, pgsql-hackers@postgresql.org
Subject: Re: [PERFORM] A Better External Sort?
In-Reply-To: <433DA228.4060103@sun.com>
* 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