On Mon, Jul 08, 2019 at 08:11:55PM -0400, Bruce Momjian wrote:
>On Wed, Jul 3, 2019 at 11:42:49AM -0400, Robert Haas wrote:
>> On Wed, Jul 3, 2019 at 11:24 AM Tomas Vondra
>> <tomas.vondra@2ndquadrant.com> wrote:
>> > Maybe. And it would probably work for the systems I used for benchmarks.
>> >
>> > It however assumes two things: (a) the storage system actually has
>> > spindles and (b) you know how many spindles there are. Which is becoming
>> > less and less safe these days - flash storage becomes pretty common, and
>> > even when there are spindles they are often hidden behind the veil of
>> > virtualization in a SAN, or something.
>>
>> Yeah, that's true.
>>
>> > I wonder if we might provide something like pg_test_prefetch which would
>> > measure performance with different values, similarly to pg_test_fsync.
>>
>> That's not a bad idea, but I'm not sure if the results that we got in
>> a synthetic test - presumably unloaded - would be a good guide to what
>> to use in a production situation. Maybe it would; I'm just not sure.
>
>I think it would be better than what we have now.
>
TBH I don't know how useful would that tool be. AFAICS the key assumptions
prefetching relies are that (a) issuing the prefetch request is much
cheaper than jut doing the I/O, and (b) the prefetch request can be
completed before we actually need the page.
(a) is becoming not quite true on new hardware - if you look at results
from the NVMe device, the improvements are much smaller compared to the
other storage systems. The speedup is ~1.6x, no matter the e_i_c value,
while on other storage types it's easily 10x in some cases.
But this is something we could measure using the new tool, because it's
mostly hardware dependent.
But (b) is the hard bit, because it depends on how much time it takes to
process a page read from the heap - if it takes a lot of time, lower e_i_c
values are fine. If it's fast, we need to increase the prefetch distance.
Essentially, from the tests I've done it seems fetching just 1 page in
advance is way too conservative, because (1) it does not really increase
I/O concurrency at the storage level and (2) we often get into situation
where the prefetch is still in progress when we actually need the page.
I don't know how to meaningfully benchmark this, though - it's way to
dependent on the particular workload / query.
Of course, backend concurrency just makes it even more complicated.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
