Hi Amit,
>I think one table with a varying amount of data is sufficient for the vacuum
>test.
>I think with more number of tables there is a greater chance of variation.
>We have previously used multiple tables in one of the tests because of
>the Truncate operation (which uses DropRelFileNodesAllBuffers that
>takes multiple relations as input) and that is not true for Vacuum operation
>which I suppose you are testing here.
I retested performance on single table for several times, the table size is
varying with the BUF_DROP_FULL_SCAN_THRESHOLD for different shared buffers.
When shared_buffers is below 20G, there were no significant changes between
master(HEAD) and patched.
And according to the results compared between 20G and 100G, we can get
optimization up to NBuffers/128, but there is no benefit from NBuffers/256.
I've tested many times, most times the same results came out, I don't know why.
But If I used 5 tables(each table size is set as BUF_DROP_FULL_SCAN_THRESHOLD),
then we can get benefit from it(NBuffers/256).
Here is my test results for single table. If you have any question or
suggestion, kindly let me know.
%reg= (patched- master(HEAD))/ patched
Optimized percentage:
shared_buffers %reg(NBuffers/512) %reg(NBuffers/256)
%reg(NBuffers/128) %reg(NBuffers/64) %reg(NBuffers/32)
%reg(NBuffers/16) %reg(NBuffers/8) %reg(NBuffers/4)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
128M 0% 0% -1%
0% 1% 0%
0% 0%
1G -1% 0% -1%
0% 0% 0%
0% 0%
20G 0% 0% -33%
0% 0% -13%
0% 0%
100G -32% 0% -49%
0% 10% 30%
0% 6%
Result details(unit: second):
patched (sec)
shared_buffers NBuffers/512 NBuffers/256 NBuffers/128 NBuffers/64
NBuffers/32 NBuffers/16 NBuffers/8 NBuffers/4
128M 0.107 0.107 0.107 0.107
0.108 0.107 0.108 0.208
1G 0.107 0.107 0.107 0.108
0.208 0.208 0.308 0.409
20G 0.208 0.308 0.308 0.409
0.609 0.808 1.511 2.713
100G 0.309 0.408 0.609 1.010
2.011 5.017 6.620 13.931
master(HEAD) (sec)
shared_buffers NBuffers/512 NBuffers/256 NBuffers/128 NBuffers/64
NBuffers/32 NBuffers/16 NBuffers/8 NBuffers/4
128M 0.107 0.107 0.108 0.107
0.107 0.107 0.108 0.208
1G 0.108 0.107 0.108 0.108
0.208 0.207 0.308 0.409
20G 0.208 0.309 0.409 0.409
0.609 0.910 1.511 2.712
100G 0.408 0.408 0.909 1.010
1.811 3.515 6.619 13.032
Regards
Tang
