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:

On 11/18/05 5:00 AM, "Dave Cramer" <[EMAIL PROTECTED]> 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.


<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?


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?


Reply via email to