Why linear read ahead (prefetch) may improve performance

[derk]

In MySQL,

Linear read-ahead is a technique that predicts what pages might be needed soon based on pages in the buffer pool being accessed sequentially... if you set innodb_read_ahead_threshold to 48, InnoDB triggers a linear read-ahead request only when 48 pages in the current extent have been accessed sequentially.

In the default setting, an extent contains 64 pages. So the prefetch mechanism will "asynchronously read-ahead the entire following extent" (i.e., 64 - 48 = 16 pages). The assumption here behind linear read-ahead is that the remaining 16 pages are very likely to be read after 48 sequential page reads.

Qestion

Without prefetching, even though the remaining 16 pages are going to be read next, MySQL can just read them sequentially. This should have the same performance as using prefetching. So what are the benefits of linear prefetching? Only when the remaining 16 pages may get randomly accessed can make it meaningful?

Thanks!

[Jolied]

The pre-fetch can help the OS disk access scheduler, especially on mechanical drives, as it knows the other pages need to be read so might be able to save some head movements. It can similarly help other caching layers that may be present in the IO subsystem, though you don't want it to be too aggressive about this as it could risk dropping useful things from cache to replace them with pages that won't be needed.

If the pages are being accessed sequentially immediately then there is likely to be little difference, but if there is some latency between the page reads (perhaps some CPU processing is done between them, which takes long enough for another thread to request pages, or maybe the calling application if cursoring through results and paginating), that potential to avoid head movements and such can make a measurable difference.