Обсуждение: Re: [GENERAL] Odd VACUUM behavior when it is expected to truncate last empty pages

(moving this to hackers since I suspect we got an issue to fix here)

On Wed, Aug 3, 2011 at 6:35 AM, Sergey Konoplev <gray.ru@gmail.com> wrote:
> Hi all,
>
> I have PostgreSQL 9.0.3 installed on my Gentoo Linux box. The
> configuration is default. There is no any activity in the database but
> the described below.
>
> What I am trying to achieve is the effect described in this article
> http://blog.endpoint.com/2010/09/reducing-bloat-without-locking.html.
> In short I am updating last pages of a table to move the tuples to the
> earlier pages to make VACUUM able to truncate the empty tail. However
> I faced a strange VACUUM behavior. So the situation is:
>
> 1. do some UPDATEs on the table so it has several last pages free,
> 2. perform VACUUM of this table the 1st time, no tail pages will be
> truncated (why?),
> 3. perform VACUUM the 2nd time straight after the 1st one and it will
> truncate the tail pages (why this time?).
>

There is a check to truncate only if minimum 1000 or relpages/16 pages
(compile time constants) can be truncated and attempt truncation only
if thats true, otherwise we assume that the cost of attempting
truncation is not worth the cost of pages salvaged. With that logic,
we should have never truncated just 3 pages in a relation with 3000+
pages. But I can see that happening because of visibility maps. So if
we stop scanning beyond a certain percentage of pages, we might be
fooled into believing that the rest of the pages can possibly be
truncated and then do a hard check to find that out.

The first vacuum probably scans even the last few pages because you
just updated the tuples of those pages and then updates the visibility
map for those pages. The second vacuum then stops much before because
the visibility map tells us that all remaining pages are visible (and
thus set nonempty_pages to a lower number) and so the check I
mentioned earlier succeeds and we attempt the truncation. Now, this is
just a theory and a reproducible case will help to confirm this.

The only problem, other than a surprising behavior that you noted,
that I see with this approach is that we might repeatedly try to
truncate a relation which in fact does not have anything to truncate.
The worst  thing is we might unnecessarily take an exclusive lock on
the table.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB     http://www.enterprisedb.com

On Wed, Aug 3, 2011 at 12:33 PM, Pavan Deolasee
<pavan.deolasee@gmail.com> wrote:
>
>
> The only problem, other than a surprising behavior that you noted,
> that I see with this approach is that we might repeatedly try to
> truncate a relation which in fact does not have anything to truncate.
> The worst  thing is we might unnecessarily take an exclusive lock on
> the table.
>

So it seems we tried to fix this issue sometime back
http://archives.postgresql.org/pgsql-hackers/2008-12/msg01994.php

But I don't quite understand how the fix would really work.
nonempty_pages would most likely be set at a value lower than relpages
if the last page in the relation is all-visible according to the
visibility map. Did we mean to test (nonempty_pages > 0) there ? But
even that may not work except for the case when there are no dead
tuples in the relation.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB     http://www.enterprisedb.com

Thank you very much, your explanation helped a lot.

This is the tool I needed the solution for
http://code.google.com/p/pc-tools/ if you are interested.

On 4 August 2011 01:10, Pavan Deolasee <pavan.deolasee@gmail.com> wrote:
> On Wed, Aug 3, 2011 at 12:33 PM, Pavan Deolasee
> <pavan.deolasee@gmail.com> wrote:
>>
>>
>> The only problem, other than a surprising behavior that you noted,
>> that I see with this approach is that we might repeatedly try to
>> truncate a relation which in fact does not have anything to truncate.
>> The worst  thing is we might unnecessarily take an exclusive lock on
>> the table.
>>
>
> So it seems we tried to fix this issue sometime back
> http://archives.postgresql.org/pgsql-hackers/2008-12/msg01994.php
>
> But I don't quite understand how the fix would really work.
> nonempty_pages would most likely be set at a value lower than relpages
> if the last page in the relation is all-visible according to the
> visibility map. Did we mean to test (nonempty_pages > 0) there ? But
> even that may not work except for the case when there are no dead
> tuples in the relation.
>
> Thanks,
> Pavan
>
> --
> Pavan Deolasee
> EnterpriseDB     http://www.enterprisedb.com
>



--
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp