To: stange@rentec.com
Cc: Luke Lonergan <llonergan@greenplum.com>, Dave Cramer <pg@fastcrypt.com>,
	Greg Stark <gsstark@mit.edu>,  Joshua Marsh <icub3d@gmail.com>,
	pgsql-performance@postgresql.org
Subject: Re: Hardware/OS recommendations for large databases (
References: <BFA3553F.1406D%llonergan@greenplum.com>
	<437E1DAE.1070301@rentec.com>
In-Reply-To: <437E1DAE.1070301@rentec.com>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 18 Nov 2005 14:07:34 -0500
Message-ID: <87oe4hpyfd.fsf@stark.xeocode.com>
Lines: 58
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Alan Stange <stange@rentec.com> writes:

> Luke Lonergan wrote:
> > Alan,
> >
> > On 11/18/05 9:31 AM, "Alan Stange" <stange@rentec.com> wrote:
> >
> >
> >> Here's the output from one iteration of iostat -k 60 while the box is
> >> doing a select count(1) on a 238GB table.
> >>
> >> avg-cpu:  %user   %nice    %sys %iowait   %idle
> >>            0.99    0.00   17.97   32.40   48.64
> >>
> >> Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
> >> sdd             345.95    130732.53         0.00    7843952          0
> >>
> >> We're reading 130MB/s for a full minute.  About 20% of a single cpu was
> >> being used.   The remainder being idle.
> >>
> >
> > Cool - thanks for the results.  Is that % of one CPU, or of 2?  Was the
> > system otherwise idle?
> >
> Actually, this was dual cpu 

I hate to agree with him but that looks like a dual machine with one CPU
pegged. Yes most of the time is being spent in the kernel, but you're still
basically cpu limited.

That said, 130MB/s is nothing to sneeze at, that's maxing out two high end
drives and quite respectable for a 3-disk stripe set, even reasonable for a
4-disk stripe set. If you're using 5 or more disks in RAID-0 or RAID 1+0 and
only getting 130MB/s then it does seem likely the cpu is actually holding you
back here.

Still it doesn't show Postgres being nearly so CPU wasteful as the original
poster claimed.

> It's all in the kernel either way; using a different scheduler or file
> system would change that result. Even better would be using direct IO to not
> flush everything else from memory and avoid some memory copies from kernel
> to user space. Note that almost none of the time is user time. Changing
> postgresql won't change the cpu useage.

Well changing to direct i/o would still be changing Postgres so that's
unclear. And there are plenty of more mundane ways that Postgres is
responsible for how efficiently or not the kernel is used. Just using fewer
syscalls to do the same amount of reading would reduce cpu consumption.


> One IMHO obvious improvement would be to have vacuum and analyze only do direct
> IO.  Now they appear to be very effective memory flushing tools.  Table scans
> on tables larger than say 4x memory should probably also use direct IO for
> reads.

-- 
greg