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

On Fri, Nov 15, 2013 at 10:52 AM, Haribabu kommi
<haribabu(dot)kommi(at)huawei(dot)com> wrote:
> On 15 November 2013 10:00 Amit Kapila wrote:
>> On Wed, Nov 13, 2013 at 12:02 PM, Haribabu kommi
>> <haribabu(dot)kommi(at)huawei(dot)com> wrote:
>> >
>> > With the simulated bloat test run for 1 hour the bloat occurred as
>> > below,
>> >
>> > Unpatched - 1532MB
>> > Patched - 1474MB
>>
>> In your test run, have you checked what happen if after heavy update
>> (or once bloat occurs), if you keep the system idle (or just have read
>> load on system) for some time, what is the result?
>
> In the simulated test run which is shared in the previous mail, after a heavy update
> the system is idle for 15 mins.
>
> With the master code, the vacuum is not triggered during this idle time as it is
> Not satisfies the vacuum threshold, because it doesn't consider the dead tuples occurred
> During vacuum operation.
>
> With the fix the one extra vacuum can gets triggered compared to master code after two or three
> heavy updates because of accumulation of dead tuples.
>
>> You haven't answered one of my questions in previous mail ( >With the
>> fix of setting the dead tuples properly, the bloat is reduced and by
>> changing the vacuum cost Parameters the bloat is avoided.
>> Which fix here you are referring to?)
>
> The bloat reduced is same with initial and latest patch.
> The vacuum cost parameters change (which doesn't contain any fix) is avoided the bloat.

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.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com