(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 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

Search Discussions

  • Pavan Deolasee at Aug 3, 2011 at 9:11 pm

    On Wed, Aug 3, 2011 at 12:33 PM, Pavan Deolasee 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 at Aug 5, 2011 at 3:54 am
    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 wrote:
    On Wed, Aug 3, 2011 at 12:33 PM, Pavan Deolasee
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedAug 3, '11 at 4:34p
activeAug 5, '11 at 3:54a
posts3
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Pavan Deolasee: 2 posts Sergey Konoplev: 1 post

People

Translate

site design / logo © 2021 Grokbase