User-Agent: Microsoft-Entourage/11.2.0.050811
Date: Wed, 05 Oct 2005 16:55:51 -0700
Subject: Re: [HACKERS] A Better External Sort?
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "Michael Stone" <mstone+postgres@mathom.us>
Cc: pgsql-hackers@postgresql.org, pgsql-performance@postgresql.org
Message-ID: <BF69B617.10B72%llonergan@greenplum.com>
Thread-Topic: [HACKERS] [PERFORM] A Better External Sort?
Thread-Index: AcXKCFCOjxWPyjX7EdqbagANk63kWA==
In-Reply-To: <20051005153349.GZ2241@mathom.us>
MIME-Version: 1.0
Content-Type: text/plain;
 charset=us-ascii
Content-Transfer-Encoding: 7bit

Michael,

On 10/5/05 8:33 AM, "Michael Stone" <mstone+postgres@mathom.us> wrote:

> real    0m8.889s 
> user    0m0.877s 
> sys     0m8.010s 
> 
> it's not in disk wait state (in fact the whole read was cached) but it's
> only getting 1MB/s.

You've proven my point completely.  This process is bottlenecked in the CPU.
The only way to improve it would be to optimize the system (libc) functions
like "fread" where it is spending most of it's time.

In COPY, we found lots of libc functions like strlen() being called
ridiculous numbers of times, in one case it was called on every
timestamp/date attribute to get the length of TZ, which is constant.  That
one function call was in the system category, and was responsible for
several percent of the time.

By the way, system routines like fgetc/getc/strlen/atoi etc, don't appear in
gprof profiles of dynamic linked objects, nor by default in oprofile
results.

If the bottleneck is in I/O, you will see the time spent in disk wait, not
in system.

- Luke