Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 4141B64FC85
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Fri,  2 Jan 2009 16:02:23 -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 08122-08
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Fri,  2 Jan 2009 16:02:19 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id 354DD64FC83
	for <pgsql-hackers@postgresql.org>;
	Fri,  2 Jan 2009 16:02:19 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n02K1jdD007538;
	Fri, 2 Jan 2009 15:01:45 -0500 (EST)
To: Greg Smith <gsmith@gregsmith.com>
cc: Robert Haas <robertmhaas@gmail.com>,
	Gregory Stark <stark@enterprisedb.com>,
	ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>,
	Postgres <pgsql-hackers@postgresql.org>
Subject: Re: posix_fadvise v22 
In-reply-to: <Pine.GSO.4.64.0901021416310.15834@westnet.com> 
References: <FDDBA24E-FF4D-4654-BA75-692B3BA71B97@enterprisedb.com>
	<8347.1229012970@sss.pgh.pa.us>
	<4136ffa0812110911x4914a951o6ca502879e3dede7@mail.gmail.com>
	<87k5a6tmy7.fsf@oxford.xeocode.com>
	<603c8f070901011230t1afa6644racb715b975078054@mail.gmail.com>
	<10658.1230843315@sss.pgh.pa.us>
	<603c8f070901011843p121e40afu35c4a7bdc51c051c@mail.gmail.com>
	<Pine.GSO.4.64.0901020335150.16680@westnet.com>
	<24481.1230910405@sss.pgh.pa.us>
	<Pine.GSO.4.64.0901021416310.15834@westnet.com>
Comments: In-reply-to Greg Smith <gsmith@gregsmith.com>
	message dated "Fri, 02 Jan 2009 14:25:47 -0500"
Date: Fri, 02 Jan 2009 15:01:45 -0500
Message-ID: <7537.1230926505@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200901/70
X-Sequence-Number: 130714

Greg Smith <gsmith@gregsmith.com> writes:
> On Fri, 2 Jan 2009, Tom Lane wrote:
>> ISTM that you *should* be able to see an improvement on even
>> single-spindle systems, due to better overlapping of CPU and I/O effort.

> The earlier synthetic tests I did:
> http://archives.postgresql.org/pgsql-hackers/2008-09/msg01401.php
> Showed a substantial speedup even in the single spindle case on a couple 
> of systems, but one didn't really seem to benefit.  So we could theorize 
> that Robert's test system is more like that one.  If someone can help out 
> with making a more formal test case showing this in action, I'll dig into 
> the details of what's different between that system and the others.

Well, I claim that if you start with a query that's about 50% CPU and
50% I/O effort, you ought to be able to get something approaching 2X
speedup if this patch really works.  Consider something like

create function waste_time(int) returns int as $$
begin
  for i in 1 .. $1 loop
    null;
  end loop;
  return 1;
end $$ language plpgsql;

select count(waste_time(42)) from very_large_table;

In principle you should be able to adjust the constant so that vmstat
shows about 50% CPU busy, and then enabling fadvise should improve
matters significantly.

Now the above proposed test case is too simple because it will generate
a seqscan, and if the kernel is not completely brain-dead it will not
need any fadvise hinting to do read-ahead.  But you should be able to
adapt the idea for whatever indexscan-based test case you are really
using.

Note: on a multi-CPU system you need to take vmstat or top numbers with
a grain of salt, since they might consider "one CPU 50% busy" as
"system only 50/N % busy".

			regards, tom lane