Re: [ADMIN] pg_class reltuples/relpages not updated by autovacuum/vacuum - Mailing list pgsql-hackers

Greg Stark <gsstark@mit.edu> writes:
> On Sat, May 28, 2011 at 12:01 PM, Tom Lane <tgl@sss.pgh.pa.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.)
        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