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

At Thu, 7 Jan 2021 09:25:22 +0000, "k(dot)jamison(at)fujitsu(dot)com" <k(dot)jamison(at)fujitsu(dot)com> wrote in
> 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.

+1.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center