On Wed, Jan 6, 2021 at 6:43 PM k.jami...@fujitsu.com <k.jami...@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.
