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. > 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? How much data is this compared to memory available for the kernel to do 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? Greetings, Andres Freund
