Prereading using posix_fadvise (was Re: Commitfest patches)

Gregory Stark wrote:
> I described which interfaces worked on Linux and Solaris based on empirical
> tests. I posted source code for synthetic benchmarks so we could test it on a
> wide range of hardware. I posted graphs based on empirical results. I posted
> mathematical formulas analysing just how much preread would be expected to
> exercise a raid array fully. I'm not sure what else I can do to effect a more
> thorough examination.

I'm sure posix_fadvise is a win in the case where it's supposed to help:
a scan that does a lot of random reads, on RAID array. And you've posted
results demonstrating that. What we need to make sure is that there's no
significant loss when it's not helping.

It seems that the worst case for this patch is a scan on a table that
doesn't fit in shared_buffers, but is fully cached in the OS cache. In
that case, the posix_fadvise calls would be a certain waste of time.

That could be alleviated by deciding at plan time whether to preread or
not, based on effective_cache_size.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com