Re: There's random access and then there's random access - Mailing list pgsql-hackers

From Mark Mielke
Subject Re: There's random access and then there's random access
Date
Msg-id 4755A2E0.4090502@mark.mielke.cc
Whole thread Raw
In response to Re: There's random access and then there's random access  (Gregory Stark <stark@enterprisedb.com>)
Responses Re: There's random access and then there's random access
Re: There's random access and then there's random access
List pgsql-hackers
Gregory Stark wrote: <blockquote cite="mid:87ve7egxow.fsf@oxford.xeocode.com" type="cite"><pre wrap="">"Gregory Stark"
<aclass="moz-txt-link-rfc2396E" href="mailto:stark@enterprisedb.com"><stark@enterprisedb.com></a>
writes</pre><blockquotetype="cite"><pre wrap="">The two interfaces I'm aware of for this are posix_fadvise() and
libaio.
I've run tests with a synthetic benchmark which generates a large file then
reads a random selection of blocks from within it using either synchronous
reads like we do now or either of those interfaces. I saw impressive speed
gains on a machine with only three drives in a raid array. I did this a
while ago so I don't have the results handy. I'll rerun the tests again and
post them.   </pre></blockquote><pre wrap="">Here's the results of running the synthetic test program on a 3-drive
raid
array. Note that the results *exceeded* the 3x speedup I expected, even for
ordered blocks. Either the drive (or the OS) is capable of reordering the
block requests better than the offset into the file would appear or some other
effect is kicking in.

The test is with an 8GB file, picking 8,192 random 8k blocks from within it.
The pink diamonds represent the bandwidth obtained if the random blocks are
sorted before fetching (like a bitmap indexscan) and the blue if they're
unsorted. </pre></blockquote> I didn't see exceeded 3X in the graph. But I certainly see 2X+ for most of the graphic,
and~3X for very small reads. Possibly, it is avoiding unnecessary read-ahead at the drive or OS levels? <br /><br /> I
thinkwe expected to see raw reads significantly faster for the single process case. I thought your simulation was going
toinvolve a tweak to PostgreSQL on a real query to see what overall effect it would have on typical queries and on
specialqueries like Matthew's. Are you able to tweak the index scan and bitmap scan methods to do posfix_fadvise()
beforerunning? Even if it doesn't do anything more intelligent such as you described in another post?<br /><br />
Cheers,<br/> mark<br /><br /><pre class="moz-signature" cols="72">-- 
 
Mark Mielke <a class="moz-txt-link-rfc2396E" href="mailto:mark@mielke.cc"><mark@mielke.cc></a>
</pre>

pgsql-hackers by date:

Previous
From: "Walter Cruz"
Date:
Subject: Re: weird - invalid string enlargement request size
Next
From: Gregory Stark
Date:
Subject: Re: There's random access and then there's random access