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

On Thu, January 7, 2021 5:36 PM (JST), Amit Kapila wrote:
>
> On Wed, Jan 6, 2021 at 6:43 PM k(dot)jamison(at)fujitsu(dot)com
> <k(dot)jamison(at)fujitsu(dot)com> wrote:
> >
> > [Results for VACUUM on single relation]
> > Average of 5 runs.
> >
> > 1. % REGRESSION
> > % Regression: (patched - master)/master
> >
> > | rel_size | 128MB | 1GB | 20GB | 100GB |
> > |----------|--------|--------|--------|----------|
> > | NB/512 | 0.000% | 0.000% | 0.000% | -32.680% |
> > | NB/256 | 0.000% | 0.000% | 0.000% | 0.000% |
> > | NB/128 | 0.000% | 0.000% | 0.000% | -16.502% |
> > | NB/64 | 0.000% | 0.000% | 0.000% | -9.841% |
> > | NB/32 | 0.000% | 0.000% | 0.000% | -6.219% |
> > | NB/16 | 0.000% | 0.000% | 0.000% | 3.323% |
> > | NB/8 | 0.000% | 0.000% | 0.000% | 8.178% |
> >
> > For 100GB shared_buffers, we can observe regression
> > beyond NBuffers/32. So with this, we can conclude
> > that NBuffers/32 is the right threshold.
> > For NBuffers/16 and beyond, the patched performs
> > worse than master. In other words, the cost of for finding
> > to be invalidated buffers gets higher in the optimized path
> > than the traditional path.
> >
> > So in attached V39 patches, I have updated the threshold
> > BUF_DROP_FULL_SCAN_THRESHOLD to NBuffers/32.
> >
>
> Thanks for the detailed tests. NBuffers/32 seems like an appropriate
> value for the threshold based on these results. I would like to
> slightly modify part of the commit message in the first patch as below
> [1], otherwise, I am fine with the changes. Unless you or anyone else
> has any more comments, I am planning to push the 0001 and 0002
> sometime next week.
>
> [1]
> "The recovery path of DropRelFileNodeBuffers() is optimized so that
> scanning of the whole buffer pool can be avoided when the number of
> blocks to be truncated in a relation is below a certain threshold. For
> such cases, we find the buffers by doing lookups in BufMapping table.
> This improves the performance by more than 100 times in many cases
> when several small tables (tested with 1000 relations) are truncated
> and where the server is configured with a large value of shared
> buffers (greater than 100GB)."

Thank you for taking a look at the results of the tests. And it's also
consistent with the results from Tang too.
The commit message LGTM.

Regards,
Kirk Jamison