Re: Visibility map and freezing - Mailing list pgsql-hackers

Simon Riggs wrote:
> On Tue, 2009-01-20 at 11:03 +0200, Heikki Linnakangas wrote:
>>> - Are there some conditions where whole-table-scanning vacuum is
>> more
>>>   effective than vacuums using visibility map? If so, we should
>> switch
>>>   to full-scan *automatically*, without relying on user
>> configurations.
>>
>> Hmm, the only downside I can see is that skipping a page here and
>> there could defeat the OS read-ahead. Perhaps we should call
>> posix_fadvise(SEQUENTIAL) to compensate. Or, we could modify the logic
>> to only skip pages when there's at least N consecutive pages that can
>> be skipped.
>
> I would rather we didn't skip any pages at all unless the gains are
> significant. Skipping the odd page makes no difference from a
> performance perspective but may have a robustness impact.
>
> "Significant gains" should take into account the size of both heap and
> indexes, and recognise that we still scan whole indexes in either case.

That sounds pretty complex, approaching what the planner does. I'd
rather keep it simple.

Attached is a simple patch to only start skipping pages after 20
consecutive pages marked as visible in the visibility map. This doesn't
do any "look-ahead", so it will always scan the first 20 pages of a
table before it starts to skip pages, and whenever there's even one page
that needs vacuuming, the next 19 pages will also be vacuumed.

We could adjust that figure 20 according to table size. Or by
seq_page_cost/random_page_cost. But I'm leaning towards a simple
hard-coded value for now.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com
*** src/backend/commands/vacuumlazy.c
--- src/backend/commands/vacuumlazy.c
***************
*** 271,276 **** lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
--- 271,277 ----
      int            i;
      PGRUsage    ru0;
      Buffer        vmbuffer = InvalidBuffer;
+     BlockNumber    all_visible_streak;

      pg_rusage_init(&ru0);

***************
*** 292,297 **** lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
--- 293,299 ----

      lazy_space_alloc(vacrelstats, nblocks);

+     all_visible_streak = 0;
      for (blkno = 0; blkno < nblocks; blkno++)
      {
          Buffer        buf;
***************
*** 309,315 **** lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,

          /*
           * Skip pages that don't require vacuuming according to the
!          * visibility map.
           */
          if (!scan_all)
          {
--- 311,324 ----

          /*
           * Skip pages that don't require vacuuming according to the
!          * visibility map. But only if we've seen a streak of at least
!          * 20 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)
          {
***************
*** 317,325 **** lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
                  visibilitymap_test(onerel, blkno, &vmbuffer);
              if (all_visible_according_to_vm)
              {
!                 vacrelstats->scanned_all = false;
!                 continue;
              }
          }

          vacuum_delay_point();
--- 326,340 ----
                  visibilitymap_test(onerel, blkno, &vmbuffer);
              if (all_visible_according_to_vm)
              {
!                 all_visible_streak++;
!                 if (all_visible_streak >= 20)
!                 {
!                     vacrelstats->scanned_all = false;
!                     continue;
!                 }
              }
+             else
+                 all_visible_streak = 0;
          }

          vacuum_delay_point();