User-Agent: Microsoft-Entourage/11.2.1.051004
Date: Sat, 19 Nov 2005 08:13:09 -0800
Subject: Re: Hardware/OS recommendations for large databases (
From: "Luke Lonergan" <llonergan@greenplum.com>
To: stange@rentec.com,
	"Greg Stark" <gsstark@mit.edu>
cc: "Dave Cramer" <pg@fastcrypt.com>, "Joshua Marsh" <icub3d@gmail.com>,
	pgsql-performance@postgresql.org
Message-ID: <BFA48F15.14182%llonergan@greenplum.com>
Thread-Topic: [PERFORM] Hardware/OS recommendations for large databases
 (
Thread-Index: AcXseIujtrf4zfrUS8uGzWRjlVpIrQAq5YQU
In-Reply-To: <437E2DF2.50906@rentec.com>
MIME-Version: 1.0
Content-Type: text/plain;
 charset=us-ascii
Content-Transfer-Encoding: 7bit

Alan,

On 11/18/05 11:39 AM, "Alan Stange" <stange@rentec.com> wrote:

> Yes and no.   The one cpu is clearly idle.   The second cpu is 40% busy
> and 60% idle (aka iowait in the above numbers).

The "aka iowait" is the problem here - iowait is not idle (otherwise it
would be in the "idle" column).

Iowait is time spent waiting on blocking io calls.  As another poster
pointed out, you have a two CPU system, and during your scan, as predicted,
one CPU went 100% busy on the seq scan.  During iowait periods, the CPU can
be context switched to other users, but as I pointed out earlier, that's not
useful for getting response on decision support queries.

Thanks for your data, it exemplifies many of the points brought up:
- Lots of disks and expensive I/O hardware does not help improve performance
on large table queries because I/O bandwidth does not scale beyond
110-120MB/s on the fastest CPUs
- OLTP performance optimizations are different than decision support

Regards,

- Luke