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

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?

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?)

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