Re: WAL prefetch

On 14.06.2018 15:44, Robert Haas wrote:
> On Wed, Jun 13, 2018 at 11:45 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>>> I have tested wal_prefetch at two powerful servers with 24 cores, 3Tb NVME
>>> RAID 10 storage device and 256Gb of RAM connected using InfiniBand.
>>> The speed of synchronous replication between two nodes is increased from 56k
>>> TPS to 60k TPS (on pgbench with scale 1000).
>> That's a reasonable improvement.
> Somehow I would have expected more. That's only a 7% speedup.
>
> I am also surprised that HDD didn't show any improvement.

My be pgbench is not the best use case for prefetch. It is updating more
or less random pages and if database is large enough and
full_page_writes is true (default value)
then most pages will be updated only once since last checkpoint and most
of updates will be represented in WAL by full page records.
And such records do not require reading any data from disk.

> Since HDD's
> are bad at random I/O, I would have expected prefetching to help more
> in that case.
>
Speed of random HDD access is limited by speed of disk head movement.
By running several IO requests in parallel we just increase probability
of head movement, so actually parallel access to HDD may even decrease
IO speed rather than increase it.
In theory, given several concurrent IO requests, driver can execute them
in optimal order, trying to minimize head movement. But if there are
really access to random pages,
then probability that we can win something by such optimization is very
small.

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company