Re: Increasing default value for effective_io_concurrency?

On Wed, Jul 03, 2019 at 11:04:59AM -0400, Robert Haas wrote:
>On Mon, Jul 1, 2019 at 7:32 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
>> On 2019-06-29 22:15:19 +0200, Tomas Vondra wrote:
>> > I think we should consider changing the effective_io_concurrency default
>> > value, i.e. the guc that determines how many pages we try to prefetch in
>> > a couple of places (the most important being Bitmap Heap Scan).
>>
>> Maybe we need improve the way it's used / implemented instead - it seems
>> just too hard to determine the correct setting as currently implemented.
>
>Perhaps the translation from effective_io_concurrency to a prefetch
>distance, which is found in the slightly-misnamed ComputeIoConcurrency
>function, should be changed. The comments therein say:
>
> * Experimental results show that both of these formulas
>aren't aggressive
> * enough, but we don't really have any better proposals.
>
>Perhaps we could test experimentally what works well with N spindles
>and then fit a formula to that curve and stick it in here, so that our
>tuning is based on practice rather than theory.
>
>I'm not sure if that approach is adequate or not. It just seems like
>something to try.
>

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.

I wonder if we might provide something like pg_test_prefetch which would
measure performance with different values, similarly to pg_test_fsync.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services