On Wed, Jan 6, 2021 at 6:43 PM k.jamison@fujitsu.com
<k.jamison@fujitsu.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)."
--
With Regards,
Amit Kapila.