Re: WAL prefetch

On 14.06.2018 16:25, Robert Haas wrote:
> On Thu, Jun 14, 2018 at 9:23 AM, Konstantin Knizhnik
>  wrote:
>> 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.
> You might be right, but I feel like I've heard previous reports of
> significant speedups from prefetching on HDDs.  Perhaps I am
> mis-remembering.
>

It is true for RAIDs of HDD which can really win by issuing parallel IO 
operations.

But there are some many different factors that I will not be surprised 
by any result:)

The last problem I have observed with NVME device at one of the 
customer's system was huge performance degradation (> 10 times: from 
500Mb/sec to 50Mb/sec write speed)
after space exhaustion at the device. There is 3Tb NVME RAID device with 
1.5Gb database. ext4 was mounted without "discard" option.
After incorrect execution of rsync, space was exhausted. Then I removed 
all data and copied database from master node.
Then I observed huge lags in async. replication between master and 
replica. wal_receiver is saving received data too slowly: write speed is 
about ~50Mb/sec vs. 0.5Gb at master.
All my attempts to use fstrim or ex4defrag didn't help. The problem was 
solved only after deleting all database files, performing fstrim and 
copying database once again.
After it wal_sender is writing data with normal speed ~0.5Gb and there 
is no lag between master and replica.


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