Re: WAL prefetch

On 06/19/2018 04:50 PM, Konstantin Knizhnik wrote:
>
>
> On 19.06.2018 16:57, Ants Aasma wrote:
>> On Tue, Jun 19, 2018 at 4:04 PM Tomas Vondra
>> <tomas(dot)vondra(at)2ndquadrant(dot)com <mailto:tomas(dot)vondra(at)2ndquadrant(dot)com>>
>> wrote:
>>
>> Right. My point is that while spawning bgworkers probably helps, I
>> don't
>> expect it to be enough to fill the I/O queues on modern storage
>> systems.
>> Even if you start say 16 prefetch bgworkers, that's not going to be
>> enough for large arrays or SSDs. Those typically need way more
>> than 16
>> requests in the queue.
>>
>> Consider for example [1] from 2014 where Merlin reported how S3500
>> (Intel SATA SSD) behaves with different effective_io_concurrency
>> values:
>>
>> [1]
>> https://www.postgresql.org/message-id/CAHyXU0yiVvfQAnR9cyH=HWh1WbLRsioe=mzRJTHwtr=2azsTdQ@mail.gmail.com
>>
>> Clearly, you need to prefetch 32/64 blocks or so. Consider you may
>> have
>> multiple such devices in a single RAID array, and that this device is
>> from 2014 (and newer flash devices likely need even deeper queues).'
>>
>>
>> For reference, a typical datacenter SSD needs a queue depth of 128 to
>> saturate a single device. [1] Multiply that appropriately for RAID
>> arrays.So
>
> How it is related with results for S3500  where this is almost now
> performance improvement for effective_io_concurrency >8?
> Starting 128 or more workers for performing prefetch is definitely not
> acceptable...
>

I'm not sure what you mean by "almost now performance improvement", but
I guess you meant "almost no performance improvement" instead?

If that's the case, it's not quite true - increasing the queue depth
above 8 further improved the throughput by about ~10-20% (both by
duration and peak throughput measured by iotop).

But more importantly, this is just a single device - you typically have
multiple of them in a larger arrays, to get better capacity, performance
and/or reliability. So if you have 16 such drives, and you want to send
at least 8 requests to each, suddenly you need at least 128 requests.

And as pointed out before, S3500 is about 5-years old device (it was
introduced in Q2/2013). On newer devices the difference is usually way
more significant / the required queue depth is much higher.

Obviously, this is a somewhat simplified view, ignoring various details
(e.g. that there may be multiple concurrent queries, each sending I/O
requests - what matters is the combined number of requests, of course).
But I don't think this makes a huge difference.

regards

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