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.jamison@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