Increasing default value for effective_io_concurrency?

Hi,

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).

The default is 1 since forever, but from my experience hardly the right
value, no matter what storage system you use. I've always ended up with
values that are either 0 (so, disabled prefetching) or significantly
higher (at least 8 or 16). In fact, e_i_c=1 can easily be detrimental
depending on the workload and storage system.

Which is an issue, because people often don't know how to tune this and
I see systems with the default value quite often.

So I do propose to increase the defaut to a value between 4 and 16.


I'm hardly the first person to notice this, as illustrated for example
by this [1] post by Merlin Moncure on pgsql-hackers from 2017, which
measured this behavior on Intel S3500 SSD:

  effective_io_concurrency 1: 46.3 sec, ~ 170 mb/sec peak via iostat
  effective_io_concurrency 2:  49.3 sec, ~ 158 mb/sec peak via iostat
  effective_io_concurrency 4:  29.1 sec, ~ 291 mb/sec peak via iostat
  effective_io_concurrency 8:  23.2 sec, ~ 385 mb/sec peak via iostat
  effective_io_concurrency 16:  22.1 sec, ~ 409 mb/sec peak via iostat
  effective_io_concurrency 32:  20.7 sec, ~ 447 mb/sec peak via iostat
  effective_io_concurrency 64:  20.0 sec, ~ 468 mb/sec peak via iostat
  effective_io_concurrency 128:  19.3 sec, ~ 488 mb/sec peak via iostat
  effective_io_concurrency 256:  19.2 sec, ~ 494 mb/sec peak via iostat

That's just one anecdotal example of behavior, of course, so I've
decided to do a couple of tests on different storage systems. Attached
is a couple of scripts I used to generate synthetic data sets with data
laid out in different patterns (random vs. regular), and running queries
scanning various fractions of the table (1%, 5%, ...) using plans using
bitmap index scans.

I've done that on three different storage systems:

1) SATA RAID (3 x 7.2k drives in RAID0)
2) SSD RAID (6 x SATA SSD in RAID0)
3) NVMe drive

Attached is a spreadsheet with a summary of results fo the tested cases.
In general, the data support what I already wrote above - the current
default is pretty bad.

In some cases it helps a bit, but a bit higher value (4 or 8) performs
significantly better. Consider for example this "sequential" data set
from the 6xSSD RAID system (x-axis shows e_i_c values, pct means what
fraction of pages matches the query):

    pct         0         1        4         16        64       128
    ---------------------------------------------------------------
      1     25990     18624      3269      2219      2189      2171
      5     88116     60242     14002      8663      8560      8726
     10    120556     99364     29856     17117     16590     17383
     25    101080    184327     79212     47884     46846     46855
     50    130709    309857    163614    103001     94267     94809
     75    126516    435653    248281    156586    139500    140087

compared to the e_i_c=0 case, it looks like this:

    pct       1        4         16        64       128
    ----------------------------------------------------
      1     72%      13%         9%        8%        8%
      5     68%      16%        10%       10%       10%
     10     82%      25%        14%       14%       14%
     25    182%      78%        47%       46%       46%
     50    237%     125%        79%       72%       73%
     75    344%     196%       124%      110%      111%

So for 1% of the table the e_i_c=1 is faster by about ~30%, but with
e_i_c=4 (or more) it's ~10x faster. This is a fairly common pattern, not
just on this storage system.

The e_i_c=1 can perform pretty poorly, especially when the query matches
large fraction of the table - for example in this example it's 2-3x
slower compared to no prefetching, and higher e_i_c values limit the
damage quite a bit.

It's not entirely terrible because in most cases those queries would use
seqscan (the benchmark forces queries to use bitmap heap scan), but it's
not something we can ignore either because of possible underestimates.

Furthermore, there are cases with much worse behavior. For example, one
of the tests on SATA RAID behaves like this:

    pct       1        4         16        64       128
    ----------------------------------------------------
      1    147%     101%        61%       52%       55%
      5    180%     106%        71%       71%       70%
     10    208%     106%        73%       80%       79%
     25    225%     118%        84%       96%       86%
     50    234%     123%        91%      102%       95%
     75    241%     127%        94%      103%       98%

Pretty much all cases are significantly slower with e_i_c=1.

Of course, I'm sure there may be other things to consider. For example,
these tests were done in isolation, while on actual systems there will
be other queries running concurrently (and those may also generate I/O).


regards

[1] https://www.postgresql.org/message-id/flat/55AA2469.20306%40dalibo.com#dda46134fb309ae09233b1547411c029

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