On Fri, Nov 29, 2019 at 07:01:39PM +0100, David Fetter wrote:
> Folks,
>
> Per a suggestion Christophe made, please find attached a patch to
> $Subject:
>
> Apart from carefully fudging with pg_resetwal, and short of running in
> production for a few weeks, what would be some good ways to test this?
Per discussion on IRC with Sehrope Sarkuni, please find attached a
patch with one fewer bug, this one in the repalloc() calls.
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
>From 4ba277a12936f188a411696728194bf2cf158989 Mon Sep 17 00:00:00 2001
From: David Fetter <da...@fetter.org>
Date: Wed, 27 Nov 2019 23:50:48 -0800
Subject: [PATCH v2] Autovacuum tables in descending order of age
To: hackers
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="------------2.23.0"
This is a multi-part message in MIME format.
--------------2.23.0
Content-Type: text/plain; charset=UTF-8; format=fixed
Content-Transfer-Encoding: 8bit
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index c1dd8168ca..cc55393137 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -288,6 +288,15 @@ typedef struct
AutoVacuumWorkItem av_workItems[NUM_WORKITEMS];
} AutoVacuumShmemStruct;
+/*
+ * Struct for deciding which tables to vacuum first
+ */
+typedef struct
+{
+ uint32 relfrozenxid_age;
+ Oid table_oid;
+} RelFrozenXidAge;
+
static AutoVacuumShmemStruct *AutoVacuumShmem;
/*
@@ -319,6 +328,7 @@ static void rebuild_database_list(Oid newdb);
static int db_comparator(const void *a, const void *b);
static void autovac_balance_cost(void);
+static int rfxa_comparator(const void *a, const void *b);
static void do_autovacuum(void);
static void FreeWorkerInfo(int code, Datum arg);
@@ -1936,6 +1946,9 @@ do_autovacuum(void)
HeapTuple tuple;
TableScanDesc relScan;
Form_pg_database dbForm;
+ RelFrozenXidAge *table_ages;
+ int table_ages_size = 1024;
+ int table_ages_count = 0;
List *table_oids = NIL;
List *orphan_oids = NIL;
HASHCTL ctl;
@@ -1951,6 +1964,7 @@ do_autovacuum(void)
bool found_concurrent_worker = false;
int i;
+ table_ages = (RelFrozenXidAge *)palloc(table_ages_size * sizeof(RelFrozenXidAge));
/*
* StartTransactionCommand and CommitTransactionCommand will automatically
* switch to other contexts. We need this one to keep the list of
@@ -2102,9 +2116,22 @@ do_autovacuum(void)
effective_multixact_freeze_max_age,
&dovacuum, &doanalyze, &wraparound);
- /* Relations that need work are added to table_oids */
+ /* Relations that need work are added to table_ages */
if (dovacuum || doanalyze)
- table_oids = lappend_oid(table_oids, relid);
+ {
+ RelFrozenXidAge rfxa;
+ rfxa.relfrozenxid_age = DirectFunctionCall1(xid_age,
+ classForm->relfrozenxid);
+ rfxa.table_oid = relid;
+ if (table_ages_count == table_ages_size)
+ {
+ table_ages_size *= 2;
+ table_ages = (RelFrozenXidAge *)repalloc(table_ages, table_ages_size * sizeof(RelFrozenXidAge));
+ }
+ table_ages[table_ages_count] = rfxa;
+ table_ages_count++;
+ }
+
/*
* Remember TOAST associations for the second pass. Note: we must do
@@ -2187,7 +2214,20 @@ do_autovacuum(void)
/* ignore analyze for toast tables */
if (dovacuum)
- table_oids = lappend_oid(table_oids, relid);
+ {
+ RelFrozenXidAge rfxa;
+
+ rfxa.relfrozenxid_age = DirectFunctionCall1(xid_age,
+ classForm->relfrozenxid);
+ rfxa.table_oid = relid;
+
+ if (table_ages_count == table_ages_size)
+ {
+ table_ages_size *= 2;
+ table_ages = (RelFrozenXidAge *)repalloc(table_ages, table_ages_size * sizeof(RelFrozenXidAge));
+ }
+ table_ages[table_ages_count++] = rfxa;
+ }
}
table_endscan(relScan);
@@ -2295,6 +2335,12 @@ do_autovacuum(void)
"Autovacuum Portal",
ALLOCSET_DEFAULT_SIZES);
+
+ qsort(table_ages, table_ages_count, sizeof(table_ages), rfxa_comparator);
+ for (int i = 0; i < table_ages_count; i++)
+ {
+ table_oids = lappend_oid(table_oids, table_ages[i].table_oid);
+ }
/*
* Perform operations on collected tables.
*/
@@ -2608,6 +2654,22 @@ deleted:
CommitTransactionCommand();
}
+/*
+ * qsort comparator for RelFrozenXidAges
+ *
+ * N.B. This comparator sorts in descending order.
+ */
+static int
+rfxa_comparator(const void *a, const void *b)
+{
+ const RelFrozenXidAge *pa = a;
+ const RelFrozenXidAge *pb = b;
+ if (pa->relfrozenxid_age == pb->relfrozenxid_age)
+ return 0;
+ else
+ return (pa->relfrozenxid_age > pb->relfrozenxid_age) ? 1 : -1;
+}
+
/*
* Execute a previously registered work item.
*/
--------------2.23.0--
