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

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