> 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
rec-bench-03-08-2026.pdf
Description: Adobe PDF document
