DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=dk20050327;
 d=earthlink.net;
	b=UMKgMxx0sApZ1H1VfjR1KLvvXrtPJQiRfG8prl+EsjGL1B0W3iWBtT2Qt2E1O8Wb;
	h=Received:Message-Id:X-Mailer:Date:To:From:Subject:In-Reply-To:References:Mime-Version:Content-Type:Content-Transfer-Encoding:X-ELNK-Trace:X-Originating-IP;
Message-Id: <6.2.5.6.0.20051118151319.01d16288@earthlink.net>
Date: Fri, 18 Nov 2005 15:29:11 -0500
To: "Luke Lonergan" <llonergan@greenplum.com>,
 pgsql-performance@postgresql.org
From: Ron <rjpeace@earthlink.net>
Subject: Re: Hardware/OS recommendations for large databases 
In-Reply-To: <BFA32FA6.14027%llonergan@greenplum.com>
References: <0F3B99E2-4575-42B8-8AAC-3FE4B231348C@fastcrypt.com>
	<BFA32FA6.14027%llonergan@greenplum.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"; format=flowed
Content-Transfer-Encoding: quoted-printable

Breaking the ~120MBps pg IO ceiling by any means=20
is an important result.  Particularly when you=20
get a ~2x improvement.  I'm curious how far we=20
can get using simple approaches like this.

At 10:13 AM 11/18/2005, Luke Lonergan wrote:
>Dave,
>
>On 11/18/05 5:00 AM, "Dave Cramer" <pg@fastcrypt.com> wrote:
> >
> > Now there's an interesting line drawn in the sand. I presume you have
> > numbers to back this up ?
> >
> > This should draw some interesting posts.
>
>Part 2: The answer
>
>System A:
>This system is running RedHat 3 Update 4, with a Fedora 2.6.10 Linux=
 kernel.
>
>On a single table with 15 columns (the Bizgres=20
>IVP) at a size double memory (2.12GB), Postgres=20
>8.0.3 with Bizgres enhancements takes 32 seconds=20
>to scan the table: that=92s 66 MB/s.  Not the=20
>efficiency I=92d hope from the onboard SATA=20
>controller that I=92d like, I would have expected=20
>to get 85% of the 100MB/s raw read performance.
Have you tried the large read ahead trick with=20
this system?  It would be interesting to see how=20
much it would help.  It might even be worth it to=20
do the experiment at all of [default, 2x default,=20
4x default, 8x default, etc] read ahead until=20
either a) you run out of resources to support the=20
desired read ahead, or b) performance levels=20
off.  I can imagine the results being very enlightening.


>System B:
>This system is running an XFS filesystem, and=20
>has been tuned to use very large (16MB)=20
>readahead.  It=92s running the Centos 4.1 distro,=20
>which uses a Linux 2.6.9 kernel.
>
>Same test as above, but with 17GB of data takes=20
>69.7 seconds to scan (!)  That=92s 244.2MB/s,=20
>which is obviously double my earlier point of=20
>110-120MB/s.  This system is running with a 16MB=20
>Linux readahead setting, let=92s try it with the=20
>default (I think) setting of 256KB =96 AHA! Now we get 171.4 seconds or=
 99.3MB/s.
The above experiment would seem useful here as well.


>Summary:
>
><cough, cough> OK =96 you can get more I/O=20
>bandwidth out of the current I/O path for=20
>sequential scan if you tune the filesystem for=20
>large readahead.  This is a cheap alternative to=20
>overhauling the executor to use asynch I/O.
>
>Still, there is a CPU limit here =96 this is not=20
>I/O bound, it is CPU limited as evidenced by the=20
>sensitivity to readahead settings.   If the=20
>filesystem could do 1GB/s, you wouldn=92t go any faster than 244MB/s.
>
>- Luke

I respect your honesty in reporting results that=20
were different then your expectations or=20
previously taken stance.  Alan Stange's comment=20
re: the use of direct IO along with your comments=20
re: async IO and mem copies plus the results of=20
these experiments could very well point us=20
directly at how to most easily solve pg's CPU boundness during IO.

[HACKERS] are you watching this?

Ron