Re: Heavily modified big table bloat even in auto vacuum is running

On 19 November 2013 10:33 Amit Kapila wrote:
> If I understood correctly, then your patch's main intention is to
> correct the estimate of dead tuples, so that it can lead to Vacuum
> cleaning the table/index which otherwise is not happening as per
> configuration value (autovacuum_vacuum_threshold) in some of the cases,
> also it is not reducing the complete bloat (Unpatched - 1532MB
> ~Patched - 1474MB), as the main reason of bloat is extra space in
> index which can be reclaimed by reindex operation.
>
> So if above is correct then this patch has 3 advantages:
> a. Extra Vacuum on table/index due to better estimation of dead tuples.
> b. Space reclaim due to this extra vacuum c. may be some performance
> advantage as it will avoid the delay in cleaning dead tuples
>
> I think better way to test the patch is to see how much benefit is
> there due to above (a and b points) advantages. Different values of
> autovacuum_vacuum_threshold can be used to test.

I modified the test and did a performance test with following configuration changes,
autovacuum_vacuum_threshold as 3 times the number of records in the table.
Autovacuum_nap_time - 30s

The test script will generate the configured vacuum threshold data in 45sec.
After 180sec test, sleeps for 2 min.

After one hour test run the patched approach shown one autovacuum is more
than the master code. Not sure as this difference also may not be visible in long runs.

The performance effect of the patch is not much visible as I think the analyze
on the table estimates the number of dead tuples of the table with some estimation.
Because of this reason not much performance improvement is not visible as the
missed dead tuple calculation in vacuum is covered by the analyze. Please correct
me if anything missed in my analysis.

Updated patch and test scripts are attached in the mail.

Regards,
Hari babu.