Re: [HACKERS] A Better External Sort?

I've now gotten verification from multiple working DBA's that DB2, Oracle, and
SQL Server can achieve ~250MBps ASTR (with as much as ~500MBps ASTR in
setups akin to Oracle RAC) when attached to a decent (not outrageous, but
decent) HD subsystem...

I've not yet had any RW DBA verify Jeff Baker's supposition that ~1GBps ASTR is
attainable.  Cache based bursts that high, yes.  ASTR, no.

The DBA's in question run RW installations that include Solaris, M$, and Linux OS's
for companies that just about everyone on these lists are likely to recognize.

Also, the implication of these pg IO limits is that money spent on even moderately
priced 300MBps SATA II based RAID HW is wasted $'s.

In total, this situation is a recipe for driving potential pg users to other DBMS.

25MBps in and 15MBps out is =BAD=.

Have we instrumented the code in enough detail that we can tell _exactly_ where
the performance drainage is?

We have to fix this.
Ron


-----Original Message-----
From: Luke Lonergan <LLonergan@greenplum.com>
Sent: Oct 5, 2005 11:24 AM
To: Michael Stone <mstone+postgres@mathom.us>, Martijn van Oosterhout <kleptog@svana.org>
Cc: pgsql-hackers@postgresql.org, pgsql-performance@postgresql.org
Subject: Re: [HACKERS] [PERFORM] A Better External Sort?

Nope - it would be disk wait.

COPY is CPU bound on I/O subsystems faster that 50 MB/s on COPY (in) and about 15 MB/s (out).

- Luke

 -----Original Message-----
From:     Michael Stone [mailto:mstone+postgres@mathom.us]
Sent:    Wed Oct 05 09:58:41 2005
To:    Martijn van Oosterhout
Cc:    pgsql-hackers@postgresql.org; pgsql-performance@postgresql.org
Subject:    Re: [HACKERS] [PERFORM] A Better External Sort?

On Sat, Oct 01, 2005 at 06:19:41PM +0200, Martijn van Oosterhout wrote:
>COPY TO /dev/null WITH binary
>13MB/s    55% user 45% system  (ergo, CPU bound)
[snip]
>the most expensive. But it does point out that the whole process is
>probably CPU bound more than anything else.

Note that 45% of that cpu usage is system--which is where IO overhead
would end up being counted. Until you profile where you system time is
going it's premature to say it isn't an IO problem.

Mike Stone


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



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend