From: Ron <rjpeace@earthlink.net>
To: Luke Lonergan <llonergan@greenplum.com>
To: pgsql-performance@postgresql.org
Subject: Re: Hardware/OS recommendations for large databases
Date: Fri, 18 Nov 2005 15:29:11 -0500
Message-ID: <6.2.5.6.0.20051118151319.01d16288@earthlink.net> (raw)
In-Reply-To: <BFA32FA6.14027%llonergan@greenplum.com>
References: <0F3B99E2-4575-42B8-8AAC-3FE4B231348C@fastcrypt.com>
<BFA32FA6.14027%llonergan@greenplum.com>
Breaking the ~120MBps pg IO ceiling by any means
is an important result. Particularly when you
get a ~2x improvement. I'm curious how far we
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
>IVP) at a size double memory (2.12GB), Postgres
>8.0.3 with Bizgres enhancements takes 32 seconds
>to scan the table: that’s 66 MB/s. Not the
>efficiency I’d hope from the onboard SATA
>controller that I’d like, I would have expected
>to get 85% of the 100MB/s raw read performance.
Have you tried the large read ahead trick with
this system? It would be interesting to see how
much it would help. It might even be worth it to
do the experiment at all of [default, 2x default,
4x default, 8x default, etc] read ahead until
either a) you run out of resources to support the
desired read ahead, or b) performance levels
off. I can imagine the results being very enlightening.
>System B:
>This system is running an XFS filesystem, and
>has been tuned to use very large (16MB)
>readahead. It’s running the Centos 4.1 distro,
>which uses a Linux 2.6.9 kernel.
>
>Same test as above, but with 17GB of data takes
>69.7 seconds to scan (!) That’s 244.2MB/s,
>which is obviously double my earlier point of
>110-120MB/s. This system is running with a 16MB
>Linux readahead setting, let’s try it with the
>default (I think) setting of 256KB – AHA! Now we get 171.4 seconds or 99.3MB/s.
The above experiment would seem useful here as well.
>Summary:
>
><cough, cough> OK – you can get more I/O
>bandwidth out of the current I/O path for
>sequential scan if you tune the filesystem for
>large readahead. This is a cheap alternative to
>overhauling the executor to use asynch I/O.
>
>Still, there is a CPU limit here – this is not
>I/O bound, it is CPU limited as evidenced by the
>sensitivity to readahead settings. If the
>filesystem could do 1GB/s, you wouldn’t go any faster than 244MB/s.
>
>- Luke
I respect your honesty in reporting results that
were different then your expectations or
previously taken stance. Alan Stange's comment
re: the use of direct IO along with your comments
re: async IO and mem copies plus the results of
these experiments could very well point us
directly at how to most easily solve pg's CPU boundness during IO.
[HACKERS] are you watching this?
Ron
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rjpeace@earthlink.net, llonergan@greenplum.com
Subject: Re: Hardware/OS recommendations for large databases
In-Reply-To: <6.2.5.6.0.20051118151319.01d16288@earthlink.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox