Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 3F40364FC57 for ; Wed, 10 Dec 2008 01:48:11 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 53997-10 for ; Wed, 10 Dec 2008 01:48:05 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from EXHUB018-2.exch018.msoutlookonline.net (exhub018-2.exch018.msoutlookonline.net [64.78.17.17]) by mail.postgresql.org (Postfix) with ESMTP id 4649A64FC4C for ; Wed, 10 Dec 2008 01:48:05 -0400 (AST) Received: from EXVMBX018-1.exch018.msoutlookonline.net ([64.78.17.47]) by EXHUB018-2.exch018.msoutlookonline.net ([64.78.17.17]) with mapi; Tue, 9 Dec 2008 21:48:04 -0800 From: Scott Carey To: Robert Haas CC: Tom Lane , "jd@commandprompt.com" , Merlin Moncure , Scott Marlowe , Jean-David Beyer , "pgsql-performance@postgresql.org" Date: Tue, 9 Dec 2008 21:48:02 -0800 Subject: Re: Need help with 8.4 Performance Testing Thread-Topic: [PERFORM] Need help with 8.4 Performance Testing Thread-Index: AcladE8049bNcRhWQbqRLsUhXGQ+qwAFo4BW Message-ID: In-Reply-To: <603c8f070812091906j2f164215v91c907f8bff8e440@mail.gmail.com> Accept-Language: en-US Content-Language: en X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: en-US Content-Type: multipart/alternative; boundary="_000_C5649812FC6scottrichrelevancecom_" MIME-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200812/108 X-Sequence-Number: 32144 --_000_C5649812FC6scottrichrelevancecom_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Just to clarify, I'm not talking about random I/O bound loads today, on har= d drives, targetted by the fadvise stuff - these aren't CPU bound, and they= will be helped by it. For sequential scans, this situation is different, since the OS has suffici= ent read-ahead prefetching algorithms of its own for sequential reads, and = the CPU work and I/O work ends up happening in parallel due to that. For what it is worth, you can roughly double to triple the iops of an Intel= X-25M on pure random reads if you queue up multiple concurrent reads rathe= r than serialize them. But it is not due to spindles, it is due to the lat= ency of the SATA interface and the ability of the controller chip to issue = reads to flash devices on different banks concurrently to some extent. On 12/9/08 7:06 PM, "Robert Haas" wrote: > Well, when select count(1) reads pages slower than my disk, its 16x + slo= wer > than my RAM. Until one can demonstrate that the system can even read pag= es > in RAM faster than what disks will do next year, it doesn't matter much t= hat > RAM is faster. It does matter that RAM is faster for sorts, hashes, and > other operations, but at the current time it does not for the raw pages > themselves, from what I can measure. > > This is in fact, central to my point. Things will be CPU bound, not I/O > bound. It is mentioned that we still have to access things over the bus, > and memory is faster, etc. But Postgres is too CPU bound on page access = to > take advantage of the fact that memory is faster (for reading data pages)= . As I understand it, a big part of the reason for the posix_fadvise patch is that the current system doesn't do a good job leveraging many spindles in the service of a single query. So the problem is not that the CPU overhead is too large in some general sense but that the disk and CPU operations get serialized, leading to an overall loss of performance. On the other hand, there are certainly cases (such as a database which is entirely in RAM, or all the commonly used parts are in RAM) where there really isn't very much I/O, and in those cases of course the CPU cost will dominate. ...Robert --_000_C5649812FC6scottrichrelevancecom_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Re: [PERFORM] Need help with 8.4 Performance Testing Just to clarify, I’m not talking about random I/O bound loads t= oday, on hard drives, targetted by the fadvise stuff — these arenR= 17;t CPU bound, and they will be helped by it.

For sequential scans, this situation is different, since the OS has suffici= ent read-ahead prefetching algorithms of its own for sequential reads, and = the CPU work and I/O work ends up happening in parallel due to that.

For what it is worth, you can roughly double to triple the iops of an Intel= X-25M on pure random reads if you queue up multiple concurrent reads rathe= r than serialize them.  But it is not due to spindles, it is due to th= e latency of the SATA interface and the ability of the controller chip to i= ssue reads to flash devices on different banks concurrently to some extent.=  


On 12/9/08 7:06 PM, "Robert Haas" <robertmhaas@gmail.com> wrote:

> Well, when select count(1) reads pages= slower than my disk, its 16x + slower
> than my RAM.  Until one can demonstrate that the system can even = read pages
> in RAM faster than what disks will do next year, it doesn't matter muc= h that
> RAM is faster.   It does matter that RAM is faster for sorts= , hashes, and
> other operations, but at the current time it does not for the raw page= s
> themselves, from what I can measure.
>
> This is in fact, central to my point.  Things will be CPU bound, = not I/O
> bound.  It is mentioned that we still have to access things over = the bus,
> and memory is faster, etc.  But Postgres is too CPU bound on page= access to
> take advantage of the fact that memory is faster (for reading data pag= es).

As I understand it, a big part of the reason for the posix_fadvise
patch is that the current system doesn't do a good job leveraging many
spindles in the service of a single query.  So the problem is not that=
the CPU overhead is too large in some general sense but that the disk
and CPU operations get serialized, leading to an overall loss of
performance.  On the other hand, there are certainly cases (such as a<= BR> database which is entirely in RAM, or all the commonly used parts are
in RAM) where there really isn't very much I/O, and in those cases of
course the CPU cost will dominate.

...Robert

--_000_C5649812FC6scottrichrelevancecom_--