On Wed, Jul 03, 2019 at 11:04:59AM -0400, Robert Haas wrote:
On Mon, Jul 1, 2019 at 7:32 PM Andres Freund <and...@anarazel.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
