Re: [WIP] Pipelined Recovery

> Hi Imran,
>
> I would like to help more in the longer term, but just for now, just very fast
> review:

Hi Jakub, thanks a lot for the quick review, and sorry for the late reply.

>
> a. with original post (v1 patch) you have reported ~42% gain for
> simple-update, but now it's just down to just 14% or 31%, any idea why?
>

I am actually not sure about that. I should have done some long-running
pgbenches. I think using shorter replays was not a good idea for judging the
performance. This time I did some longer benches [1] of 30 mins, and it turned
out the pipeline gains are decreased relatively to the last run. The
simple-update
s-buff cases decreased from ~30% to ~20%. That doesn't sound good. Looks
like the longer replays have something that is killing pipeline gains.

> b. often in "default" small s-b scenarios (e.g. in
> rec-p1-tpcb-like-def-startup-proc.svg) which seem to be much more realistic,
> you seem to be having huge flat
> StartReadBuffer()->GetVictionBuffer()->FlushBuffer() that seems to be pricey
> (visually looks like 20-40% of the whole) and it kind of shows signs double
> page checksum validation there (so in "startup" we are calculating checksums
> twice). As you are trying to get most of pipelining concept (most work done
> outside startup/recovery), I think bgwriter is not effective enough there (or
> in any PG installation out there; check bgwriter_* GUCs, hard ceiling seems to
> be: 100 pages / 0.2s = 500 pages/sec =~ just 3.9 MB/s!), so it's not
> aggressive enough and it should be part of this work. Some ideas: maybe for
> start, try doing some longer run (dozen of minutes) and do manual inspection
> of pgstat_bgwriter to see what's happennig, maybe tune it so it is much more
> effective via recovery.conf add just add avg CPU% collection of bgwriter to
> the benchmark run. In theory if you could get better bgwriter efficency when
> evicting the pages you would be having now 3 effective "threads" working on
> recovering rather than just 2, that - in theory - should make your parts of
> code/patch much more effective too I think (hard to say for sure without
> running experiment :) ). So, in principle I believe that when startup/recovery
> is starting doing too many FlushBuffer(), bgwriter should get kicked in the
> butt and unlocked, but that might (or not?) be separate idea.
>

You are right; the bgwriter is also playing an important role in the startup
process. I tried using an aggressive setting for bgwriter

```
bgwriter_delay = 10ms
bgwriter_lru_maxpages = 5000
bgwriter_lru_multiplier = 10.0
```
And then I also did some stats review from the pg_stat_bgwriter and
pg_stat_io. You will find the stats details inside the bench log
files (result-[crash/archive]-rec/logs/rec-[p0/p1]-*.log).
The following are test cases c-t-1 & c-t-3 from the pdf report.

simple-update | def-s-buffer | def-bgwriter | pipeline-off | 6m 8s 150ms (p0)
simple-update | def-s-buffer | aggressive-bgwriter | pipeline-off | 5m
7s 160ms (p0)

Looks like aggressive bgwriter had ~20% improvement relative to a simple run.
The stats_io shows us that the writes are somewhat offloaded from the
startup proc.
The StartReadBuffer()->GetVictionBuffer()->FlushBuffer() scenarios were
decreased but are still visible in the flamegraphs.

(bgwriter-defaults)
backend_type | writes |
-------------------+----------+
background writer | 170400 |
startup | 22840303 |

(bgwriter-aggressive)
backend_type | writes |
-------------------+----------+
background writer | 14215870 |
startup | 8871504 |

But the overall performance gains by enabling the pipeline were more or less the
same. I have added some detailed bench scenarios in the pdf report by
running a single recovery with 4 diff scenarios (s-b & bgwriter combinations).
Benches showed that making bgwriter aggressive did offload the
writes and decreased some recovery time but related performance gain
by enabling the pipeline was almost the same in some cases (c-t-5 & c-t-7) and
was prominent in some (see tests c-t-1 & c-t-3).

I also did the archive recovery this time. Before that, we were
mimicking a crash
recovery by copying all the archived wal to pg_wal beforehand and then starting
the cluster. But we skip this in archive recovery and postgres will itself
copy each wal file via restore command (cp in this case). Due to the
archive recovery, there was a change in the workflow. Now XlogPageRead()
triggers WaitForWALToBecomeAvailable() more often. You can see this
shift in the flamegraphs. Also, enabling the pipeline will shift this
wait from the
startup to the producer worker. Benches will show different pipeline
gains for an archive & a crash recovery even with the same workloads.
For example,
see the 'perf' column in the pdf report for a-t-7, a-t-8 & c-t-7, c-t-8.
My guess for the perf decrease is that even if the pipeline offloads decoder
from the startup proc and makes the startop proc fast, but producer
may now have
to wait for wal to arrive (WaitForWALToBecomeAvailable). This can create a slow
producer and fast consumer scenario.

I used pg_wait_sampling to check whether our pipeline worker is going to wait
under an archive recovery.

(s-buf: 8GB. bgwriter: defaults)
(pgbench -n -c 20 -M prepared -j 20 -T 1800 -b tpcb-like postgres)

pid | event_type | event | queryid | count
--------+------------+-----------------------+---------+-------
402240 | IPC | MessageQueueReceive | 0 | 7135
402281 | IPC | RestoreCommand | 0 | 5377
402240 | IPC | CheckpointStart | 0 | 693
402240 | IPC | CheckpointDone | 0 | 363
402281 | IPC | MessageQueueSend | 0 | 293
402240 | IPC | RestoreCommand | 0 | 2

This shows the MessageQueueReceive wait event for 402240 (startup), meaning
the consumer is waiting for the producer. I was not able to see any
IPC wait events
while running a crash recovery, but multiples can be seen in the case
of the archive
recovery as shown above. You can also find more details related to
the wait_sampling
in the log files (result-[crash/archive]-rec/logs/rec-[p0/p1]-*.log).

I think a streaming replication workflow is more closely related to an
archive recovery
rather than a crash recovery, but could have more wait time compared
to archive rec due
to network overhead. I wonder if we can offload producer worker waits somehow
so that producer and consumer both can be fast; it seems not.

> c. kind of nitpicking: there's plenty of slow "vfs" operations there and
> somewhat they are involving kernel-side ntfs_file_read_iter(), are you running
> this on NTFS? (/media/imran/?) Maybe try on some more standard Linux fs ? (it
> still should hit pagecache most of the time, I'm simply not certain of
> efficency of NTFS on Linux - never saw it working there).
>
> -J.

Yes, I had no extra space left for testing on my ext4 partition. I had
dual booted env
so I mounted the ntfs partition for doing the benches. In the latest
benches [1],
I have mounted an ext4 partition, so no more ntfs. But looks like there is no
visible performance difference.

At last, after doing the benches, it looks like the pipeline isn't
doing well in the longer runs
compared to the last run. I will try to do some more R&D on that.

Thanks for the quick review, and will be looking forward to hearing
more from you.

[1]: https://drive.google.com/file/d/1cSzADxXaDWBCXBoJMr58kUnhFHNBx3pT/view?usp=sharing

Test script: https://github.com/imranzaheer612/pg-recovery-testing

Thanks,
Imran Zaheer