On Monday, October 5, 2020 8:50 PM, Amit Kapila wrote: > On Mon, Oct 5, 2020 at 3:04 PM k.jami...@fujitsu.com > > > 2. Also, the other thing is I have asked for some testing to avoid > > > the small regression we have for a smaller number of shared buffers > > > which I don't see the results nor any change in the code. I think it > > > is better if you post the pending/open items each time you post a new > version of the patch. > > > > Ah. Apologies for forgetting to include updates about that, but since > > I keep on updating the patch I've decided not to post results yet as > > performance may vary per patch-update due to possible bugs. > > But for the performance case of not using recovery check, I just removed it > from below. > > Does it meet the intention? > > > > BlockNumber smgrcachednblocks(SMgrRelation reln, ForkNumber > forknum) { > > - if (InRecovery && reln->smgr_cached_nblocks[forknum] != > InvalidBlockNumber) > > + if (reln->smgr_cached_nblocks[forknum] != InvalidBlockNumber) > > return reln->smgr_cached_nblocks[forknum]; > > > > Yes, we can do that for the purpose of testing.
With the latest patches attached, and removing the recovery check in smgrnblocks, I tested the performance of vacuum. (3 trial runs, 3.5 GB db populated with 1000 tables) Execution Time (seconds) | s_b | master | patched | %reg | |-------|--------|---------|----------| | 128MB | 15.265 | 15.260 | -0.03% | | 1GB | 14.808 | 15.009 | 1.34% | | 20GB | 24.673 | 11.681 | -111.22% | | 100GB | 74.298 | 11.724 | -533.73% | These are good results and we can see the improvements for large shared buffers, For small s_b, the performance is almost the same. I repeated the recovery performance test from the previous mail, and ran three trials for each shared_buffer setting. We can also clearly see the improvement here. Recovery Time (seconds) | s_b | master | patched | %reg | |-------|--------|---------|--------| | 128MB | 3.043 | 3.010 | -1.10% | | 1GB | 3.417 | 3.477 | 1.73% | | 20GB | 20.597 | 2.409 | -755% | | 100GB | 66.862 | 2.409 | -2676% | For default and small shared_buffers, the recovery performance is almost the same. But for bigger shared_buffers, we can see the benefit and improvement. For 20GB, from 20.597 s to 2.409 s. For 100GB s_b, from 66.862 s to 2.409 s. I have updated the latest patches, with 0002 being the new one. Instead of introducing a new API, I just added the bool parameter to smgrnblocks and modified its callers. Comments and feedback are highly appreciated. Regards, Kirk Jamison
0001-v1-Prevent-invalidating-blocks-in-smgrextend-during-recovery.patch
Description: 0001-v1-Prevent-invalidating-blocks-in-smgrextend-during-recovery.patch
0002-v1-Add-bool-param-in-arg-of-smgrextend-for-cached-block.patch
Description: 0002-v1-Add-bool-param-in-arg-of-smgrextend-for-cached-block.patch
0003-v2-Optimize-DropRelFileNodeBuffers-during-recovery.patch
Description: 0003-v2-Optimize-DropRelFileNodeBuffers-during-recovery.patch