RE: [Patch] Optimize dropping of relation buffers using dlist

On Monday, October 5, 2020 8:50 PM, Amit Kapila wrote:

> On Mon, Oct 5, 2020 at 3:04 PM k(dot)jamison(at)fujitsu(dot)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