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

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:
> > On 12 November 2013 08:47 Amit Kapila wrote:
> >> On Mon, Nov 11, 2013 at 3:14 PM, Haribabu kommi
> >> <haribabu(dot)kommi(at)huawei(dot)com> wrote:
> >> > On 08 November 2013 18:35 Amit Kapila wrote:
> >> >> On Fri, Nov 8, 2013 at 10:56 AM, Haribabu kommi
> >> >> <haribabu(dot)kommi(at)huawei(dot)com> wrote:
> >> >> > On 07 November 2013 09:42 Amit Kapila wrote:
> >> >> > 1. Taking a copy of n_dead_tuples before VACUUM starts and then
> >> >> subtract it once it is done.
> >> >> > This approach doesn't include the tuples which are remains
> >> >> > during
> >> >> the vacuum operation.
> >
> > Patch is modified as take a copy of n_dead_tuples during vacuum start
> > and use the same while calculating the new dead tuples at end of
> vacuum.
> >
> >> >> By the way, do you have test case or can you try to write a test
> >> case
> >> >> which can show this problem and then after fix, you can verify if
> >> the
> >> >> problem is resolved.
> >> >
> >> > The simulated index bloat problem can be generated using the
> >> > attached
> >> script and sql.
> >> > With the fix of setting the dead tuples properly,
> >>
> >> Which fix here you are referring to, is it the one which you have
> >> proposed with your initial mail?
> >>
> >> > the bloat is reduced and by changing the vacuum cost Parameters
> the
> >> > bloat is avoided.
> >
> > 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.

Regards,
Hari babu.