Recalculating OldestXmin in a long-running vacuum - Mailing list pgsql-patches

Hi,

It seems to me that we could easily reclaim a bit more dead tuples in a
vacuum by recalculating the OldestXmin every now and then. In a large
table with a constant stream of updates/deletes and concurrent vacuums,
this could make a big difference.

With the attached patch, OldestXmin is recalculated in a vacuum every
100 pages. That's a quite arbitrary number, but feels like a good one to me.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com
Index: src/backend/commands/vacuumlazy.c
===================================================================
RCS file: /home/hlinnaka/pgcvsrepository/pgsql/src/backend/commands/vacuumlazy.c,v
retrieving revision 1.82
diff -c -r1.82 vacuumlazy.c
*** src/backend/commands/vacuumlazy.c    5 Jan 2007 22:19:27 -0000    1.82
--- src/backend/commands/vacuumlazy.c    16 Jan 2007 11:02:35 -0000
***************
*** 66,71 ****
--- 66,73 ----
  #define REL_TRUNCATE_MINIMUM    1000
  #define REL_TRUNCATE_FRACTION    16

+ /* OldestXmin is recalculated every OLDEST_XMIN_REFRESH_INTERVAL pages */
+ #define OLDEST_XMIN_REFRESH_INTERVAL 100

  typedef struct LVRelStats
  {
***************
*** 256,261 ****
--- 258,270 ----

          vacuum_delay_point();

+         /* Get a new OldestXmin every OLDEST_XMIN_REFRESH_INTERVAL pages
+          * so that we get to reclaim a little bit more dead tuples in a
+          * long-running vacuum.
+          */
+         if (blkno % OLDEST_XMIN_REFRESH_INTERVAL == (OLDEST_XMIN_REFRESH_INTERVAL - 1))
+             OldestXmin = GetOldestXmin(onerel->rd_rel->relisshared, true);
+
          /*
           * If we are close to overrunning the available space for dead-tuple
           * TIDs, pause and do a cycle of vacuuming before we tackle this page.
Index: src/backend/storage/ipc/procarray.c
===================================================================
RCS file: /home/hlinnaka/pgcvsrepository/pgsql/src/backend/storage/ipc/procarray.c,v
retrieving revision 1.20
diff -c -r1.20 procarray.c
*** src/backend/storage/ipc/procarray.c    5 Jan 2007 22:19:38 -0000    1.20
--- src/backend/storage/ipc/procarray.c    16 Jan 2007 10:52:10 -0000
***************
*** 416,426 ****
      /*
       * Normally we start the min() calculation with our own XID.  But if
       * called by checkpointer, we will not be inside a transaction, so use
!      * next XID as starting point for min() calculation.  (Note that if there
!      * are no xacts running at all, that will be the subtrans truncation
!      * point!)
       */
!     if (IsTransactionState())
          result = GetTopTransactionId();
      else
          result = ReadNewTransactionId();
--- 416,429 ----
      /*
       * Normally we start the min() calculation with our own XID.  But if
       * called by checkpointer, we will not be inside a transaction, so use
!      * next XID as starting point for min() calculation.  We also don't
!      * include our own transaction if ignoreVacuum is true and we're a
!      * vacuum process ourselves.
!      *
!      * (Note that if there are no xacts running at all, that will be the
!      * subtrans truncation point!)
       */
!     if (IsTransactionState() && !(ignoreVacuum && MyProc->inVacuum))
          result = GetTopTransactionId();
      else
          result = ReadNewTransactionId();