Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id CFDB965058F for ; Tue, 9 Dec 2008 19:39:17 -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 97677-01-8 for ; Tue, 9 Dec 2008 19:39:09 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from EXHUB018-3.exch018.msoutlookonline.net (exhub018-3.exch018.msoutlookonline.net [64.78.17.18]) by mail.postgresql.org (Postfix) with ESMTP id EBE68650531 for ; Tue, 9 Dec 2008 19:34:52 -0400 (AST) Received: from EXVMBX018-1.exch018.msoutlookonline.net ([64.78.17.47]) by EXHUB018-3.exch018.msoutlookonline.net ([64.78.17.18]) with mapi; Tue, 9 Dec 2008 15:34:51 -0800 From: Scott Carey To: Robert Haas , Tom Lane CC: "jd@commandprompt.com" , Merlin Moncure , Scott Marlowe , Jean-David Beyer , "pgsql-performance@postgresql.org" Date: Tue, 9 Dec 2008 15:34:49 -0800 Subject: Re: Need help with 8.4 Performance Testing Thread-Topic: [PERFORM] Need help with 8.4 Performance Testing Thread-Index: AclaUbSDd8ct61FORW+KBsrww0b0+gABQVsI Message-ID: In-Reply-To: <603c8f070812091458w14021741s61996a27e94f1c82@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_C5644099F9Ascottrichrelevancecom_" 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/100 X-Sequence-Number: 32136 --_000_C5644099F9Ascottrichrelevancecom_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Prefetch CPU cost should be rather low in the grand scheme of things, and d= oes help performance even for very fast I/O. I would not expect a very lar= ge CPU use increase from that sort of patch in the grand scheme of things -= there is a lot that is more expensive to do on a per block basis. There are two ways to look at non-I/O bound performance: * Aggregate performance across many concurrent activities - here you want t= he least CPU used possible per action, and the least collisions on locks or= shared data structures. Using resources for as short of an interval as po= ssible also helps a lot here. * Single query performance, where you want to shorten the query time, perha= ps at the cost of more average CPU. Here, something like the fadvise stuff= helps - as would any thread parallelism. Perhaps less efficient in aggreg= ate, but more efficient for a single query. Overall CPU cost of accessing and reading data. If this comes from disk, t= he big gains will be along the whole chain: Driver to file system cache, f= ile system cache to process, process specific tasks (cache eviction, placem= ent, tracking), examining page tuples, locating tuples within pages, etc. = Anything that currently occurs on a per-block basis that could be done in = a larger batch or set of blocks may be a big gain. Another place that comm= only consumes CPU in larger software projects is memory allocation if more = advanced allocation techniques are not used. I have no idea what Postgres = uses here however. I do know that commercial databases have extensive work= in this area for performance, as well as reliability (harder to cause a le= ak, or easier to detect) and ease of use (don't have to even bother to free= in certain contexts). > On 12/9/08 2:58 PM, "Robert Haas" wrote: > I don't believe the thesis. The gap between disk speeds and memory > speeds may narrow over time, but I doubt it's likely to disappear > altogether any time soon, and certainly not for all users. Well, when select count(1) reads pages slower than my disk, its 16x + slowe= r 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 = 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 pages= themselves, from what I can measure. This is in fact, central to my point. Things will be CPU bound, not I/O bo= und. 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 ta= ke advantage of the fact that memory is faster (for reading data pages). The biggest change is not just that disks are getting closer to RAM, but th= at the random I/O penalty is diminishing significantly. Low latencies make= s seek-driven queries that used to consume mostly disk time consume CPU tim= e instead. High CPU costs for accessing pages makes a fast disk surprising= ly close to RAM speed. > Besides which, I believe the CPU overhead of that patch is pretty darn > small when the feature is not enabled. > ...Robert I doubt it is much CPU, on or off. It will help with SSD's when optimizing= a single query, it may not help much if a system has enough 'natural' para= llelism from other concurrent queries. However there is a clear CPU benefi= t for getting individual queries out of the way faster, and occupying preci= ous work_mem or other resources for a shorter time. Occupying resources fo= r a shorter period always translates to some CPU savings on a machine runni= ng at its limit with high concurrency. --_000_C5644099F9Ascottrichrelevancecom_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Re: [PERFORM] Need help with 8.4 Performance Testing Prefetch CPU cost should be rather low in the grand scheme of things,= and does help performance even for very fast I/O.  I would not expect= a very large CPU use increase from that sort of patch in the grand scheme = of things — there is a lot that is more expensive to do on a per bloc= k basis.

There are two ways to look at non-I/O bound performance:
* Aggregate performance across many concurrent activities — here you = want the least CPU used possible per action, and the least collisions on lo= cks or shared data structures.  Using resources for as short of an int= erval as possible also helps a lot here.
* Single query performance, where you want to shorten the query time, perha= ps at the cost of more average CPU.  Here, something like the fadvise = stuff helps — as would any thread parallelism.  Perhaps less eff= icient in aggregate, but more efficient for a single query.

Overall CPU cost of accessing and reading data.  If this comes from di= sk, the big gains will be along the whole chain:  Driver to file syste= m cache, file system cache to process, process specific tasks (cache evicti= on, placement, tracking), examining page tuples, locating tuples within pag= es, etc.   Anything that currently occurs on a per-block basis th= at could be done in a larger batch or set of blocks may be a big gain. &nbs= p;Another place that commonly consumes CPU in larger software projects is m= emory allocation if more advanced allocation techniques are not used.  = ;I have no idea what Postgres uses here however.  I do know that comme= rcial databases have extensive work in this area for performance, as well a= s reliability (harder to cause a leak, or easier to detect) and ease of use= (don’t have to even bother to free in certain contexts).  

> On 12/9/08 2:58 PM, "Robert Haas&= quot; <robertmhaas@gmail.com> w= rote:
> I don't believe the thesis.  The = gap between disk speeds and memory
> speeds may narrow over time, but I doubt it's likely to disappear
> altogether any time soon, and certainly not for all users.


> Besides which, I believe the CPU overhead of that patch is pretty darn=
> small when the feature is not enabled.

> ...Robert