From: Tom Lane <tgl@sss.pgh.pa.us>
To: Greg Smith <gsmith@gregsmith.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Gregory Stark <stark@enterprisedb.com>
Cc: ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
Cc: Postgres <pgsql-hackers@postgresql.org>
Subject: Re: posix_fadvise v22
Date: Fri, 02 Jan 2009 15:01:45 -0500
Message-ID: <7537.1230926505@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, gsmith@gregsmith.com, robertmhaas@gmail.com, stark@enterprisedb.com, itagaki.takahiro@oss.ntt.co.jp
Subject: Re: posix_fadvise v22
In-Reply-To: <7537.1230926505@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox