Re: Increasing default value for effective_io_concurrency?

On Mon, Jul 01, 2019 at 04:32:15PM -0700, Andres Freund wrote:
>Hi,
>
>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.
>

Sure, if we can improve those bits, that'd be nice. It's definitely hard
to decide what value is appropriate for a given storage system. But I'm
not sure it's something we can do easily, considering how opaque the
hardware is for us ...

I wonder

>
>> 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):
>
>I assume that the y axis is the time of the query?
>

The y-axis is the fraction of table matched by the query. The values in
the contingency table are query durations (average of 3 runs, but the
numbers vere very close).

>How much data is this compared to memory available for the kernel to do
>caching?
>

Multiple of RAM, in all cases. The queries were hitting random subsets of
the data, and the page cache was dropped after each test, to eliminate
cross-query caching.

>
>> 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.
>
>I'm surprised the slowdown for small e_i_c values is that big - it's not
>obvious to me why that is. Which os / os version / filesystem / io
>scheduler / io scheduler settings were used?
>

This is the system with NVMe storage, and SATA RAID:

Linux bench2 4.19.26 #1 SMP Sat Mar 2 19:50:14 CET 2019 x86_64 Intel(R)
Xeon(R) CPU E5-2620 v4 @ 2.10GHz GenuineIntel GNU/Linux

/dev/nvme0n1p1 on /mnt/data type ext4 (rw,relatime)
/dev/md0 on /mnt/raid type ext4 (rw,relatime,stripe=48)

The other system looks pretty much the same (same kernel, ext4).

regards

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