Re: [ADMIN] pg_class reltuples/relpages not updated by autovacuum/vacuum

2011/5/29 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Greg Stark <gsstark(at)mit(dot)edu> writes:
>> On Sat, May 28, 2011 at 12:01 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> I also found that Greg was right in thinking that it would help if we
>>> tweaked lazy_scan_heap to not always scan the first
>>> SKIP_PAGES_THRESHOLD-1 pages even if they were
>>> all_visible_according_to_vm.
>
>> You fixed the logic only for the first 32 pages which helps with the
>> skew. But really the logic is backwards in general. Instead of
>> counting how many missed opportunities for skipped pages we've seen in
>> the past we should read the bits for the next 32 pages in advance and
>> decide what to do before we read those pages.
>
> OK, do you like the attached version of that logic?  (Other fragments
> of the patch as before.)

The idea was that remove only one page from the VACUUM will prevent
relfrozenxid update and reltuples (and relpages) update.
Now, I beleive that once we've skip at least one page thanks to
SKIP_PAGES_THRESHOLD, then we should be more agressive and remove as
many as possible pages from the VACUUM, tks to the VM.

>
>                        regards, tom lane
>
> diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c
> index 9393fa0727aaad7508e1163623322b4066412257..231447b31223bc5350ce49a136cffafaa53bc5fb 100644
> *** a/src/backend/commands/vacuumlazy.c
> --- b/src/backend/commands/vacuumlazy.c
> *************** lazy_scan_heap(Relation onerel, LVRelSta
> *** 311,317 ****
>        int                     i;
>        PGRUsage        ru0;
>        Buffer          vmbuffer = InvalidBuffer;
> !       BlockNumber all_visible_streak;
>
>        pg_rusage_init(&ru0);
>
> --- 305,312 ----
>        int                     i;
>        PGRUsage        ru0;
>        Buffer          vmbuffer = InvalidBuffer;
> !       BlockNumber next_not_all_visible_block;
> !       bool            skipping_all_visible_blocks;
>
>        pg_rusage_init(&ru0);
>
> *************** lazy_scan_heap(Relation onerel, LVRelSta
> *** 329,340 ****
>
>        nblocks = RelationGetNumberOfBlocks(onerel);
>        vacrelstats->rel_pages = nblocks;
>        vacrelstats->nonempty_pages = 0;
>        vacrelstats->latestRemovedXid = InvalidTransactionId;
>
>        lazy_space_alloc(vacrelstats, nblocks);
>
> !       all_visible_streak = 0;
>        for (blkno = 0; blkno < nblocks; blkno++)
>        {
>                Buffer          buf;
> --- 324,369 ----
>
>        nblocks = RelationGetNumberOfBlocks(onerel);
>        vacrelstats->rel_pages = nblocks;
> +       vacrelstats->scanned_pages = 0;
>        vacrelstats->nonempty_pages = 0;
>        vacrelstats->latestRemovedXid = InvalidTransactionId;
>
>        lazy_space_alloc(vacrelstats, nblocks);
>
> !       /*
> !        * We want to skip pages that don't require vacuuming according to the
> !        * visibility map, but only when we can skip at least SKIP_PAGES_THRESHOLD
> !        * consecutive pages.  Since we're reading sequentially, the OS should be
> !        * doing readahead for us, so there's no gain in skipping a page now and
> !        * then; that's likely to disable readahead and so be counterproductive.
> !        * Also, skipping even a single page means that we can't update
> !        * relfrozenxid, so we only want to do it if we can skip a goodly number
> !        * of pages.
> !        *
> !        * Before entering the main loop, establish the invariant that
> !        * next_not_all_visible_block is the next block number >= blkno that's
> !        * not all-visible according to the visibility map, or nblocks if there's
> !        * no such block.  Also, we set up the skipping_all_visible_blocks flag,
> !        * which is needed because we need hysteresis in the decision: once we've
> !        * started skipping blocks, we may as well skip everything up to the next
> !        * not-all-visible block.
> !        *
> !        * Note: if scan_all is true, we won't actually skip any pages; but we
> !        * maintain next_not_all_visible_block anyway, so as to set up the
> !        * all_visible_according_to_vm flag correctly for each page.
> !        */
> !       for (next_not_all_visible_block = 0;
> !                next_not_all_visible_block < nblocks;
> !                next_not_all_visible_block++)
> !       {
> !               if (!visibilitymap_test(onerel, next_not_all_visible_block,     &vmbuffer))
> !                       break;
> !       }
> !       if (next_not_all_visible_block >= SKIP_PAGES_THRESHOLD)
> !               skipping_all_visible_blocks = true;
> !       else
> !               skipping_all_visible_blocks = false;
> !
>        for (blkno = 0; blkno < nblocks; blkno++)
>        {
>                Buffer          buf;
> *************** lazy_scan_heap(Relation onerel, LVRelSta
> *** 347,387 ****
>                OffsetNumber frozen[MaxOffsetNumber];
>                int                     nfrozen;
>                Size            freespace;
> !               bool            all_visible_according_to_vm = false;
>                bool            all_visible;
>                bool            has_dead_tuples;
>
> !               /*
> !                * Skip pages that don't require vacuuming according to the visibility
> !                * map. But only if we've seen a streak of at least
> !                * SKIP_PAGES_THRESHOLD pages marked as clean. Since we're reading
> !                * sequentially, the OS should be doing readahead for us and there's
> !                * no gain in skipping a page now and then. You need a longer run of
> !                * consecutive skipped pages before it's worthwhile. Also, skipping
> !                * even a single page means that we can't update relfrozenxid or
> !                * reltuples, so we only want to do it if there's a good chance to
> !                * skip a goodly number of pages.
> !                */
> !               if (!scan_all)
>                {
> !                       all_visible_according_to_vm =
> !                               visibilitymap_test(onerel, blkno, &vmbuffer);
> !                       if (all_visible_according_to_vm)
>                        {
> !                               all_visible_streak++;
> !                               if (all_visible_streak >= SKIP_PAGES_THRESHOLD)
> !                               {
> !                                       vacrelstats->scanned_all = false;
> !                                       continue;
> !                               }
>                        }
>                        else
> !                               all_visible_streak = 0;
>                }
>
>                vacuum_delay_point();
>
> !               scanned_pages++;
>
>                /*
>                 * If we are close to overrunning the available space for dead-tuple
> --- 376,419 ----
>                OffsetNumber frozen[MaxOffsetNumber];
>                int                     nfrozen;
>                Size            freespace;
> !               bool            all_visible_according_to_vm;
>                bool            all_visible;
>                bool            has_dead_tuples;
>
> !               if (blkno == next_not_all_visible_block)
>                {
> !                       /* Time to advance next_not_all_visible_block */
> !                       for (next_not_all_visible_block++;
> !                                next_not_all_visible_block < nblocks;
> !                                next_not_all_visible_block++)
>                        {
> !                               if (!visibilitymap_test(onerel, next_not_all_visible_block,
> !                                                                               &vmbuffer))
> !                                       break;
>                        }
> +
> +                       /*
> +                        * We know we can't skip the current block.  But set up
> +                        * skipping_all_visible_blocks to do the right thing at the
> +                        * following blocks.
> +                        */
> +                       if (next_not_all_visible_block - blkno > SKIP_PAGES_THRESHOLD)
> +                               skipping_all_visible_blocks = true;
>                        else
> !                               skipping_all_visible_blocks = false;
> !                       all_visible_according_to_vm = false;
> !               }
> !               else
> !               {
> !                       /* Current block is all-visible */
> !                       if (skipping_all_visible_blocks && !scan_all)
> !                               continue;
> !                       all_visible_according_to_vm = true;
>                }
>
>                vacuum_delay_point();
>
> !               vacrelstats->scanned_pages++;
>
>                /*
>                 * If we are close to overrunning the available space for dead-tuple
>
> --
> 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