Heavily modified big table bloat even in auto vacuum is running

Haribabu kommi <haribabu.kommi@huawei.com> wrote:

To handle the above case instead of directly resetting the dead
tuples as zero, how if the exact dead tuples are removed from the
table stats. With this approach vacuum gets triggered frequently
thus it reduces the bloat.
Patch for the same is attached in the mail.

Please add this to the open CommitFest to ensure that it gets
reviewed:

https://commitfest.postgresql.org/action/commitfest_view/open

For more information about the process, see:

http://wiki.postgresql.org/wiki/CommitFest

You may also want to reveiw:

http://wiki.postgresql.org/wiki/Developer_FAQ

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Haribabu kommi <haribabu.kommi@huawei.com> writes:

To handle the above case instead of directly resetting the dead tuples as zero, how if the exact dead tuples
are removed from the table stats. With this approach vacuum gets triggered frequently thus it reduces the bloat.

This does not seem like a very good idea as-is, because it will mean that
n_dead_tuples can diverge arbitrarily far from reality over time, as a
result of accumulation of errors. It also doesn't seem like a very good
idea that VACUUM sets n_live_tuples while only adjusting n_dead_tuples
incrementally; ideally those counters should move in the same fashion.
In short, I think this patch will create at least as many problems as
it fixes.

What would make more sense to me is for VACUUM to estimate the number
of remaining dead tuples somehow and send that in its message. However,
since the whole point here is that we aren't accounting for transactions
that commit while VACUUM runs, it's not very clear how to do that.

Another way to look at it is that we want to keep any increments to
n_dead_tuples that occur after VACUUM takes its snapshot. Maybe we could
have VACUUM copy the n_dead_tuples value as it exists when VACUUM starts,
and then send that as the value to subtract when it's done?

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12 October 2013 11:30 Tom Lane wrote:

Haribabu kommi <haribabu.kommi@huawei.com> writes:

To handle the above case instead of directly resetting the dead tuples
as zero, how if the exact dead tuples are removed from the table stats. With this approach vacuum gets triggered frequently thus it reduces the bloat.

This does not seem like a very good idea as-is, because it will mean that n_dead_tuples can diverge arbitrarily far from reality over time, as a result of accumulation of errors. It also doesn't seem
like a very good idea that VACUUM sets n_live_tuples while only adjusting n_dead_tuples incrementally; ideally those counters should move in the same fashion.
In short, I think this patch will create at least as many problems as it fixes.

What would make more sense to me is for VACUUM to estimate the number of remaining dead tuples somehow and send that in its message. However, since the whole point here is that we aren't accounting for
transactions that commit while VACUUM runs, it's not very clear how to do that.

Another way to look at it is that we want to keep any increments to n_dead_tuples that occur after VACUUM takes its snapshot. Maybe we could have VACUUM copy the n_dead_tuples value as it exists when
VACUUM starts, and then send that as the value to subtract when it's done?

Taking of n_dead_tuples copy and pass the same at the vacuum end to subtract from table stats may not be correct, as vacuum may not be cleaned all the dead tuples because of tuple visibility
To other transactions. How about resets the n_dead_tuples as zero if it goes negative because of errors?

Regards,
Hari babu.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Oct 15, 2013 at 3:37 PM, Haribabu kommi
<haribabu.kommi@huawei.com> wrote:

On 12 October 2013 11:30 Tom Lane wrote:

Haribabu kommi <haribabu.kommi@huawei.com> writes:

To handle the above case instead of directly resetting the dead tuples
as zero, how if the exact dead tuples are removed from the table stats. With this approach vacuum gets triggered frequently thus it reduces the bloat.

This does not seem like a very good idea as-is, because it will mean that n_dead_tuples can diverge arbitrarily far from reality over time, as a result of accumulation of errors. It also doesn't seem
like a very good idea that VACUUM sets n_live_tuples while only adjusting n_dead_tuples incrementally; ideally those counters should move in the same fashion.
In short, I think this patch will create at least as many problems as it fixes.

What would make more sense to me is for VACUUM to estimate the number of remaining dead tuples somehow and send that in its message. However, since the whole point here is that we aren't accounting for
transactions that commit while VACUUM runs, it's not very clear how to do that.

Another way to look at it is that we want to keep any increments to n_dead_tuples that occur after VACUUM takes its snapshot. Maybe we could have VACUUM copy the n_dead_tuples value as it exists when
VACUUM starts, and then send that as the value to subtract when it's done?

Taking of n_dead_tuples copy and pass the same at the vacuum end to subtract from table stats may not be correct, as vacuum may not be cleaned all the dead tuples because of tuple visibility
To other transactions. How about resets the n_dead_tuples as zero if it goes negative because of errors?

Wouldn't the way you are planing to change n_dead_tuples create
inconsistency for n_live_tuples and n_dead_tuples, because it would
have counted non deleted tuples as n_live_tuples as per below code:

if (tupgone)
{
..
tups_vacuumed += 1;
has_dead_tuples = true;
}
else
{
num_tuples += 1;
hastup = true;
..
}

So now if we just subtract tuples_deleted from n_dead_tuples, it will
count the tuples deleted during vacuum both as live tuples and dead
tuples.
There is one statistics for dead row version's that cannot be removed
(nkeep), if we could use that to estimate total remaining dead tuples,
then the solution can be inline with Tom's suggestion (What would make
more sense to me is for VACUUM to estimate the number of remaining
dead tuples somehow and send that in its message.).

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 20 October 2013 12:06 Amit Kapila wrote:

On Tue, Oct 15, 2013 at 3:37 PM, Haribabu kommi <haribabu.kommi@huawei.com> wrote:

On 12 October 2013 11:30 Tom Lane wrote:

Haribabu kommi <haribabu.kommi@huawei.com> writes:

To handle the above case instead of directly resetting the dead
tuples as zero, how if the exact dead tuples are removed from the table stats. With this approach vacuum gets triggered frequently thus it reduces the bloat.

This does not seem like a very good idea as-is, because it will mean
that n_dead_tuples can diverge arbitrarily far from reality over time, as a result of accumulation of errors. It also doesn't seem like a very good idea that VACUUM sets n_live_tuples while only adjusting n_dead_tuples incrementally; ideally those counters should move in the same fashion.
In short, I think this patch will create at least as many problems as it fixes.

What would make more sense to me is for VACUUM to estimate the number
of remaining dead tuples somehow and send that in its message. However, since the whole point here is that we aren't accounting for transactions that commit while VACUUM runs, it's not very clear how to do that.

Another way to look at it is that we want to keep any increments to
n_dead_tuples that occur after VACUUM takes its snapshot. Maybe we could have VACUUM copy the n_dead_tuples value as it exists when VACUUM starts, and then send that as the value to subtract when it's done?

Taking of n_dead_tuples copy and pass the same at the vacuum end to
subtract from table stats may not be correct, as vacuum may not be cleaned all the dead tuples because of tuple visibility To other transactions. How about resets the n_dead_tuples as zero if it goes negative because of errors?

Wouldn't the way you are planing to change n_dead_tuples create inconsistency for n_live_tuples and n_dead_tuples, because it would have counted non deleted tuples as n_live_tuples as per below code:

if (tupgone)
{
..
tups_vacuumed += 1;
has_dead_tuples = true;
}
else
{
num_tuples += 1;
hastup = true;
..
}

So now if we just subtract tuples_deleted from n_dead_tuples, it will count the tuples deleted during vacuum both as live tuples and dead tuples.
There is one statistics for dead row version's that cannot be removed (nkeep), if we could use that to estimate total remaining dead tuples, then the solution can be inline with Tom's suggestion (What
would make more sense to me is for VACUUM to estimate the number of remaining dead tuples somehow and send that in its message.).

Yes, it's correct. "nkeep" counter have the dead tuples which are recently dead and are not vacuumed. The removal of tuples vacuumed from dead tuples should be the same as "nkeep" counter.
So if we remove the nkeep from num_tuples which gives us the proper live tuples. How about following statement at the end scan for all blocks.

num_tuples -= nkeep;

please let me know if any corrections are required.
Patch with the above implementation is attached in the mail.

Regards,
Hari babu.

On Mon, Oct 21, 2013 at 10:54 AM, Haribabu kommi
<haribabu.kommi@huawei.com> wrote:

On 20 October 2013 12:06 Amit Kapila wrote:

On Tue, Oct 15, 2013 at 3:37 PM, Haribabu kommi <haribabu.kommi@huawei.com> wrote:

On 12 October 2013 11:30 Tom Lane wrote:

Haribabu kommi <haribabu.kommi@huawei.com> writes:

Another way to look at it is that we want to keep any increments to
n_dead_tuples that occur after VACUUM takes its snapshot. Maybe we could have VACUUM copy the n_dead_tuples value as it exists when VACUUM starts, and then send that as the value to subtract when it's done?

Taking of n_dead_tuples copy and pass the same at the vacuum end to
subtract from table stats may not be correct, as vacuum may not be cleaned all the dead tuples because of tuple visibility To other transactions. How about resets the n_dead_tuples as zero if it goes negative because of errors?

Wouldn't the way you are planing to change n_dead_tuples create inconsistency for n_live_tuples and n_dead_tuples, because it would have counted non deleted tuples as n_live_tuples as per below code:

if (tupgone)
{
..
tups_vacuumed += 1;
has_dead_tuples = true;
}
else
{
num_tuples += 1;
hastup = true;
..
}

So now if we just subtract tuples_deleted from n_dead_tuples, it will count the tuples deleted during vacuum both as live tuples and dead tuples.
There is one statistics for dead row version's that cannot be removed (nkeep), if we could use that to estimate total remaining dead tuples, then the solution can be inline with Tom's suggestion (What
would make more sense to me is for VACUUM to estimate the number of remaining dead tuples somehow and send that in its message.).

Yes, it's correct. "nkeep" counter have the dead tuples which are recently dead and are not vacuumed. The removal of tuples vacuumed from dead tuples should be the same as "nkeep" counter.
So if we remove the nkeep from num_tuples which gives us the proper live tuples. How about following statement at the end scan for all blocks.

num_tuples -= nkeep;

Actually what I had in mind was to use nkeep to estimate n_dead_tuples
similar to how num_tuples is used to estimate n_live_tuples. I think
it will match what Tom had pointed in his response (>>>>What would
make more sense to me is for VACUUM to estimate the number

of remaining dead tuples somehow and send that in its message. However, since the whole point here is that we aren't accounting for transactions that commit while VACUUM runs, it's not very clear how to do that.)

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 22 October 2013 10:15 Amit Kapila wrote:

On Mon, Oct 21, 2013 at 10:54 AM, Haribabu kommi <haribabu.kommi@huawei.com> wrote:

Yes, it's correct. "nkeep" counter have the dead tuples which are recently dead and are not vacuumed. The removal of tuples vacuumed from dead tuples should be the same as "nkeep" counter.
So if we remove the nkeep from num_tuples which gives us the proper live tuples. How about following statement at the end scan for all blocks.

num_tuples -= nkeep;

Actually what I had in mind was to use nkeep to estimate n_dead_tuples similar to how num_tuples is used to estimate n_live_tuples. I think it will match what Tom had pointed in his response (>>>>What

would make more sense to me is for VACUUM to estimate the number

of remaining dead tuples somehow and send that in its message.
However, since the whole point here is that we aren't accounting for
transactions that commit while VACUUM runs, it's not very clear how
to do that.)

I changed the patch as passing the "nkeep" counter data as the new dead tuples in the relation to stats like the new_rel_tuples.
The "nkeep" counter is an approximation of dead tuples data of a relation.
Instead of resetting dead tuples stats as zero, used this value to set n_dead_tuples same as n_live_tuples.

Patch is attached in the mail. Please let me know if any changes are required.

Regards,
Hari Babu.

On Tue, Oct 22, 2013 at 2:09 PM, Haribabu kommi
<haribabu.kommi@huawei.com> wrote:

On 22 October 2013 10:15 Amit Kapila wrote:

On Mon, Oct 21, 2013 at 10:54 AM, Haribabu kommi <haribabu.kommi@huawei.com> wrote:

Actually what I had in mind was to use nkeep to estimate n_dead_tuples similar to how num_tuples is used to estimate n_live_tuples. I think it will match what Tom had pointed in his response (>>>>What

would make more sense to me is for VACUUM to estimate the number

of remaining dead tuples somehow and send that in its message.
However, since the whole point here is that we aren't accounting for
transactions that commit while VACUUM runs, it's not very clear how
to do that.)

I changed the patch as passing the "nkeep" counter data as the new dead tuples in the relation to stats like the new_rel_tuples.
The "nkeep" counter is an approximation of dead tuples data of a relation.
Instead of resetting dead tuples stats as zero, used this value to set n_dead_tuples same as n_live_tuples.

Directly using nkeep might not work as it is not guaranteed that
Vacuum will scan all the pages, we need to estimate the value similar
to new_rel_tuples, something like as done in below function:

/* now we can compute the new value for pg_class.reltuples */
vacrelstats->new_rel_tuples = vac_estimate_reltuples(onerel, false,

nblocks,

vacrelstats->scanned_pages,

num_tuples);

I am not sure whether the same calculation as done for new_rel_tuples
works for new_dead_tuples, you can once check it.

I am thinking that if we have to do estimation anyway, then wouldn't
it be better to do the way Tom had initially suggested (Maybe we could
have VACUUM copy the n_dead_tuples value as it exists when VACUUM
starts, and then send that as the value to subtract when it's done?)

I think the reason you gave that due to tuple visibility check the
number of dead tuples calculated by above logic is not accurate is
right but still it will make the value of dead tuples more appropriate
than it's current value.

You can check if there is a way to do estimation of dead tuples
similar to new tuples, and it will be as solid as current logic of
vac_estimate_reltuples(), then it's okay, otherwise use the other
solution (using the value of n_dead_tuples at start of Vacuum) to
solve the problem.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 07 November 2013 09:42 Amit Kapila wrote:

On Tue, Oct 22, 2013 at 2:09 PM, Haribabu kommi
<haribabu.kommi@huawei.com> wrote:

On 22 October 2013 10:15 Amit Kapila wrote:

On Mon, Oct 21, 2013 at 10:54 AM, Haribabu kommi

<haribabu.kommi@huawei.com> wrote:

Actually what I had in mind was to use nkeep to estimate

n_dead_tuples

similar to how num_tuples is used to estimate n_live_tuples. I think
it will match what Tom had pointed in his response (>>>>What

would make more sense to me is for VACUUM to estimate the number

of remaining dead tuples somehow and send that in its message.
However, since the whole point here is that we aren't accounting
for transactions that commit while VACUUM runs, it's not very
clear how to do that.)

I changed the patch as passing the "nkeep" counter data as the new

dead tuples in the relation to stats like the new_rel_tuples.

The "nkeep" counter is an approximation of dead tuples data of a

relation.

Instead of resetting dead tuples stats as zero, used this value to

set n_dead_tuples same as n_live_tuples.

Directly using nkeep might not work as it is not guaranteed that Vacuum
will scan all the pages, we need to estimate the value similar to
new_rel_tuples, something like as done in below function:

/* now we can compute the new value for pg_class.reltuples */
vacrelstats->new_rel_tuples = vac_estimate_reltuples(onerel, false,

nblocks,

vacrelstats->scanned_pages,

num_tuples);

I am not sure whether the same calculation as done for new_rel_tuples
works for new_dead_tuples, you can once check it.

I didn't find any way to calculate new_dead_tuples like new_rel_tuples.
I will check it.

I am thinking that if we have to do estimation anyway, then wouldn't it
be better to do the way Tom had initially suggested (Maybe we could
have VACUUM copy the n_dead_tuples value as it exists when VACUUM
starts, and then send that as the value to subtract when it's done?)

I think the reason you gave that due to tuple visibility check the
number of dead tuples calculated by above logic is not accurate is
right but still it will make the value of dead tuples more appropriate
than it's current value.

You can check if there is a way to do estimation of dead tuples similar
to new tuples, and it will be as solid as current logic of
vac_estimate_reltuples(), then it's okay, otherwise use the other
solution (using the value of n_dead_tuples at start of Vacuum) to solve
the problem.

The two approaches calculations are approximation values only.

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.

2. nkeep counter contains the tuples which are still visible to other transactions.
This approach doesn't include tuples which are deleted on pages where vacuum operation is already finished.

In my opinion the second approach gives the value nearer to the actual value,
because it includes some of the new dead tuples also. Please correct me if anything wrong in my analysis.

Regards,
Hari babu.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Nov 8, 2013 at 10:56 AM, Haribabu kommi
<haribabu.kommi@huawei.com> wrote:

On 07 November 2013 09:42 Amit Kapila wrote:

I am not sure whether the same calculation as done for new_rel_tuples
works for new_dead_tuples, you can once check it.

I didn't find any way to calculate new_dead_tuples like new_rel_tuples.
I will check it.

I am thinking that if we have to do estimation anyway, then wouldn't it
be better to do the way Tom had initially suggested (Maybe we could
have VACUUM copy the n_dead_tuples value as it exists when VACUUM
starts, and then send that as the value to subtract when it's done?)

I think the reason you gave that due to tuple visibility check the
number of dead tuples calculated by above logic is not accurate is
right but still it will make the value of dead tuples more appropriate
than it's current value.

You can check if there is a way to do estimation of dead tuples similar
to new tuples, and it will be as solid as current logic of
vac_estimate_reltuples(), then it's okay, otherwise use the other
solution (using the value of n_dead_tuples at start of Vacuum) to solve
the problem.

The two approaches calculations are approximation values only.

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.

Wouldn't next or future vacuum's will make the estimate more appropraite?

2. nkeep counter contains the tuples which are still visible to other transactions.
This approach doesn't include tuples which are deleted on pages where vacuum operation is already finished.

In my opinion the second approach gives the value nearer to the actual value,
because it includes some of the new dead tuples also. Please correct me if anything wrong in my analysis.

I think main problem in nkeep logic is to come up with an
estimation algorithm similar to live tuples.

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.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 08 November 2013 18:35 Amit Kapila wrote:

On Fri, Nov 8, 2013 at 10:56 AM, Haribabu kommi
<haribabu.kommi@huawei.com> wrote:

On 07 November 2013 09:42 Amit Kapila wrote:

I am not sure whether the same calculation as done for

new_rel_tuples

works for new_dead_tuples, you can once check it.

I didn't find any way to calculate new_dead_tuples like

new_rel_tuples.

I will check it.

I am thinking that if we have to do estimation anyway, then wouldn't
it be better to do the way Tom had initially suggested (Maybe we
could have VACUUM copy the n_dead_tuples value as it exists when
VACUUM starts, and then send that as the value to subtract when it's
done?)

I think the reason you gave that due to tuple visibility check the
number of dead tuples calculated by above logic is not accurate is
right but still it will make the value of dead tuples more
appropriate than it's current value.

You can check if there is a way to do estimation of dead tuples
similar to new tuples, and it will be as solid as current logic of
vac_estimate_reltuples(), then it's okay, otherwise use the other
solution (using the value of n_dead_tuples at start of Vacuum) to
solve the problem.

The two approaches calculations are approximation values only.

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.

Wouldn't next or future vacuum's will make the estimate more
appropraite?

Possible only when nkeep counter value (tuples not cleaned) is very less value.

2. nkeep counter contains the tuples which are still visible to other

transactions.

This approach doesn't include tuples which are deleted on pages

where vacuum operation is already finished.

In my opinion the second approach gives the value nearer to the

actual

value, because it includes some of the new dead tuples also. Please

correct me if anything wrong in my analysis.
I think main problem in nkeep logic is to come up with an estimation
algorithm similar to live tuples.

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, the bloat is reduced and by changing the vacuum cost
Parameters the bloat is avoided.

The advantage with the fix is observed is the more number of times the auto vacuum is triggered on
The bloated table, as it satisfies the vacuum criteria because of proper dead tuples compared to the
original code.

Regards,
Hari babu.

On Mon, Nov 11, 2013 at 3:14 PM, Haribabu kommi
<haribabu.kommi@huawei.com> wrote:

On 08 November 2013 18:35 Amit Kapila wrote:

On Fri, Nov 8, 2013 at 10:56 AM, Haribabu kommi
<haribabu.kommi@huawei.com> wrote:

On 07 November 2013 09:42 Amit Kapila wrote:

I am not sure whether the same calculation as done for

new_rel_tuples

works for new_dead_tuples, you can once check it.

I didn't find any way to calculate new_dead_tuples like

new_rel_tuples.

I will check it.

The two approaches calculations are approximation values only.

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.

Wouldn't next or future vacuum's will make the estimate more
appropraite?

Possible only when nkeep counter value (tuples not cleaned) is very less value.

Do you really expect too many dead tuples during Vacuum?

2. nkeep counter contains the tuples which are still visible to other

transactions.

This approach doesn't include tuples which are deleted on pages

where vacuum operation is already finished.

In my opinion the second approach gives the value nearer to the

actual

value, because it includes some of the new dead tuples also. Please

correct me if anything wrong in my analysis.
I think main problem in nkeep logic is to come up with an estimation
algorithm similar to live tuples.

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 Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12 November 2013 08:47 Amit Kapila wrote:

On Mon, Nov 11, 2013 at 3:14 PM, Haribabu kommi
<haribabu.kommi@huawei.com> wrote:

On 08 November 2013 18:35 Amit Kapila wrote:

On Fri, Nov 8, 2013 at 10:56 AM, Haribabu kommi
<haribabu.kommi@huawei.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

With this patched approach the bloat is reduced.

Regards,
Hari babu.

On Wed, Nov 13, 2013 at 12:02 PM, Haribabu kommi
<haribabu.kommi@huawei.com> wrote:

On 12 November 2013 08:47 Amit Kapila wrote:

On Mon, Nov 11, 2013 at 3:14 PM, Haribabu kommi
<haribabu.kommi@huawei.com> wrote:

On 08 November 2013 18:35 Amit Kapila wrote:

On Fri, Nov 8, 2013 at 10:56 AM, Haribabu kommi
<haribabu.kommi@huawei.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 15 November 2013 10:00 Amit Kapila wrote:

On Wed, Nov 13, 2013 at 12:02 PM, Haribabu kommi
<haribabu.kommi@huawei.com> wrote:

On 12 November 2013 08:47 Amit Kapila wrote:

On Mon, Nov 11, 2013 at 3:14 PM, Haribabu kommi
<haribabu.kommi@huawei.com> wrote:

On 08 November 2013 18:35 Amit Kapila wrote:

On Fri, Nov 8, 2013 at 10:56 AM, Haribabu kommi
<haribabu.kommi@huawei.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.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Nov 15, 2013 at 10:52 AM, Haribabu kommi
<haribabu.kommi@huawei.com> wrote:

On 15 November 2013 10:00 Amit Kapila wrote:

On Wed, Nov 13, 2013 at 12:02 PM, Haribabu kommi
<haribabu.kommi@huawei.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.

On Fri, Nov 22, 2013 at 12:12 PM, Haribabu kommi
<haribabu.kommi@huawei.com> wrote:

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.

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.

Yes, that seems to be the reason why you are not seeing any
performance benefit, but still I think this is useful optimization to
do, as
analyze updates both the livetuples and dead tuples and similarly
vacuum should also update both the counts. Do you see any reason
why Vacuum should only update live tuples and not deadtuples?

Because of this reason not much performance improvement is not visible as the
missed dead tuple calculation in vacuum is covered by the analyze.

Yeah, so might be we can check once by configuring
analyze_threshold/scalefactor in a way that analyze doesn't get
trigger during your test.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 25 November 2013 10:43 Amit Kapila wrote:

On Fri, Nov 22, 2013 at 12:12 PM, Haribabu kommi
<haribabu.kommi@huawei.com> wrote:

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.

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.

Yes, that seems to be the reason why you are not seeing any
performance benefit, but still I think this is useful optimization to
do, as
analyze updates both the livetuples and dead tuples and similarly
vacuum should also update both the counts. Do you see any reason
why Vacuum should only update live tuples and not deadtuples?

As vacuum touches all the pages where the dead tuples are present. This is not the
Same with analyzer. Because of this reason, the analyzer estimates the dead tuples also.
With the proposed patch the vacuum also estimates the dead tuples.

Because of this reason not much performance improvement is not

visible

as the missed dead tuple calculation in vacuum is covered by the

analyze.

Yeah, so might be we can check once by configuring
analyze_threshold/scalefactor in a way that analyze doesn't get trigger
during your test.

I ran the test for one hour with a high analyze_threshold and results are below.

Auto vacuum count Bloat size
Master 15 155MB
Patched 23 134MB

Updated test script and configuration is attached in the mail.

Regards,
Hari babu.