Re: Hardware/OS recommendations for large databases

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"  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:
>
> 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