Re: Prereading using posix_fadvise (was Re: Commitfest patches)

Bruce Momjian wrote:
> Heikki Linnakangas wrote:
>>> So it has nothing to do with table size. The fadvise calls need to be
>>> (and are) 
>>> limited by what can be used in the near future, and not for the whole
>>> statement.
>> Right, I was sloppy. Instead of table size, what matters is the amount 
>> of data the scan needs to access. The point remains that if the data is 
>> already in OS cache, the posix_fadvise calls are a waste of time, 
>> regardless of how many pages ahead you advise.
> 
> I now understand what posix_fadvise() is allowing us to do. 
> posix_fadvise(POSIX_FADV_WILLNEED) allows us to tell the kernel we will
> need a certain block in the future --- this seems much cheaper than a
> background reader.

Yep.

> We know we will need the blocks, and telling the kernel can't hurt,
> except that there is overhead in telling the kernel.  Has anyone
> measured how much overhead?  I would be interested in a test program
> that read the same page over and over again from the kernel, with and
> without a posix_fadvise() call.

Agreed, that needs to be benchmarked next. There's also some overhead in 
doing the buffer manager hash table lookup to check whether the page is 
in shared_buffers. We could reduce that by the more complex approach 
Greg mentioned of allocating a buffer in shared_buffers when we do 
posix_fadvise.

> Should we consider only telling the kernel X pages ahead, meaning when
> we are on page 10 we tell it about page 16?

Yes. You don't want to fire off thousands of posix_fadvise calls 
upfront. That'll just flood the kernel, and it will most likely ignore 
any advise after the first few hundred or so. I'm not sure what the 
appropriate amount of read ahead would be, though. Probably depends a 
lot on the OS and hardware, and needs to be a adjustable.

In some cases we can't easily read ahead more than a certain number of 
pages. For example, in a regular index scan, we can easily fire off 
posix_advise calls for all the heap pages referenced by a single index 
page, but reading ahead more than that becomes much more complex.

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