Skip site navigation (1) Skip section navigation (2)

Re: Vacuum, visibility maps and SKIP_PAGES_THRESHOLD

From: Cédric Villemain <cedric(dot)villemain(dot)debian(at)gmail(dot)com>
To: Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Vacuum, visibility maps and SKIP_PAGES_THRESHOLD
Date: 2011-05-27 14:07:25
Message-ID: BANLkTinVSKKJdMsn4KFm1zQjPXfE_bGMGw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
2011/5/27 Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com>:
> I wonder if we have tested the reasoning behind having
> SKIP_PAGES_THRESHOLD and the magic number of 32 assigned to it
> currently. While looking at the code after a long time and doing some
> tests, I realized that a manual VACUUM would always scan first 31
> pages of a relation which has not received any write activity since
> the last VACUUM. On closer inspection, I realized that we have
> deliberately put in this hook to ensure that we use visibility maps
> only when we see at least SKIP_PAGES_THRESHOLD worth of all-visible
> sequential pages to take advantage of possible OS seq scan
> optimizations.
>
> My statistical skills are limited, but wouldn't that mean that for a
> fairly well distributed write activity across a large table, if there
> are even 3-4% update/deletes, we would most likely hit a
> not-all-visible page for every 32 pages scanned ? That would mean that

The page skip is still based on VM.
So you wonder what are the chances of a VM not up-to-date when we access it ?

> almost entire relation will be scanned even if the visibility map
> tells us that only 3-4% pages require scanning ?  And the probability
> will increase with the increase in the percentage of updated/deleted
> tuples. Given that the likelihood of anyone calling VACUUM (manually
> or through autovac settings) on a table which has less than 3-4%
> updates/deletes is very low, I am worried that might be loosing all
> advantages of visibility maps for a fairly common use case.
>
> Do we have any numbers to prove what we have today is good ? Sorry, I
> may not have followed the discussions very closely in the past and not
> sure if this has been debated/tested already.
>
> Thanks,
> Pavan
>
>
>
>
>
> --
> Pavan Deolasee
> EnterpriseDB     http://www.enterprisedb.com
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>



-- 
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support

In response to

Responses

pgsql-hackers by date

Next:From: Heikki LinnakangasDate: 2011-05-27 14:11:17
Subject: Re: Vacuum, visibility maps and SKIP_PAGES_THRESHOLD
Previous:From: Tom LaneDate: 2011-05-27 14:06:10
Subject: Re: Vacuum, visibility maps and SKIP_PAGES_THRESHOLD

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group