User-Agent: Microsoft-Entourage/11.2.1.051004
Date: Mon, 21 Nov 2005 00:12:55 -0800
Subject: Re: Hardware/OS recommendations for large databases (
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "Alan Stange" <stange@rentec.com>
cc: "Greg Stark" <gsstark@mit.edu>, "Dave Cramer" <pg@fastcrypt.com>,
	"Joshua Marsh" <icub3d@gmail.com>, pgsql-performance@postgresql.org
Message-ID: <BFA6C187.1425C%llonergan@greenplum.com>
Thread-Topic: [PERFORM] Hardware/OS recommendations for large databases
 (
Thread-Index: AcXtjSIct5lGn9A0TIO+AtPmVX3wYQA5j3wv
In-Reply-To: <437FFEF0.9090409@rentec.com>
MIME-Version: 1.0
Content-Type: text/plain;
 charset=us-ascii
Content-Transfer-Encoding: 7bit

Alan,


On 11/19/05 8:43 PM, "Alan Stange" <stange@rentec.com> wrote:

> Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
> sdd             343.73    175035.73       277.55    5251072       8326
> 
> while doing a select count(1) on the same large table as before.
> Subsequent iostat output all showed that this data rate was being
> maintained.  The system is otherwise mostly idle during this measurement.

Yes - interesting.  Note the other result using XFS that I posted earlier
where I got 240+MB/s.  XFS has more aggressive readahead, which is why I
used it.
 
> Can you explain again why you think there's an IO ceiling of 120MB/s
> because I really don't understand?

OK - slower this time:

We've seen between 110MB/s and 120MB/s on a wide variety of fast CPU
machines with fast I/O subsystems that can sustain 250MB/s+ using dd, but
which all are capped at 120MB/s when doing sequential scans with different
versions of Postgres.

Understand my point: It doesn't matter that there is idle or iowait on the
CPU, the postgres executor is not able to drive the I/O rate for two
reasons: there is a lot of CPU used for the scan (the 40% you reported) and
a lack of asynchrony (the iowait time).  That means that by speeding up the
CPU you only reduce the first part, but you don't fix the second and v.v.

With more aggressive readahead, the second problem (the I/O asynchrony) is
handled better by the Linux kernel and filesystem.  That's what we're seeing
with XFS.

- Luke