public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v1] Autovacuum tables in descending order of age
8+ messages / 4 participants
[nested] [flat]
* [PATCH v1] Autovacuum tables in descending order of age
@ 2019-11-28 07:50 David Fetter <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: David Fetter @ 2019-11-28 07:50 UTC (permalink / raw)
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index c1dd8168ca..54b9ff2f80 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);
+ }
+ 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);
+ }
+ 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.
*/
^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v3] Autovacuum tables in descending order of age
@ 2019-11-28 07:50 David Fetter <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: David Fetter @ 2019-11-28 07:50 UTC (permalink / raw)
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index c1dd8168ca..038b3f1272 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,7 +1946,9 @@ do_autovacuum(void)
HeapTuple tuple;
TableScanDesc relScan;
Form_pg_database dbForm;
- List *table_oids = NIL;
+ RelFrozenXidAge *table_ages;
+ int table_ages_size = 1024;
+ int table_ages_count = 0;
List *orphan_oids = NIL;
HASHCTL ctl;
HTAB *table_toast_map;
@@ -1951,6 +1963,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 +2115,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 +2213,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,12 +2334,15 @@ do_autovacuum(void)
"Autovacuum Portal",
ALLOCSET_DEFAULT_SIZES);
+
+ qsort(table_ages, table_ages_count, sizeof(table_ages), rfxa_comparator);
+
/*
* Perform operations on collected tables.
*/
- foreach(cell, table_oids)
+ for (int i = 0; i < table_ages_count; i++)
{
- Oid relid = lfirst_oid(cell);
+ Oid relid = table_ages[i].table_oid;
HeapTuple classTup;
autovac_table *tab;
bool isshared;
@@ -2608,6 +2650,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.
*/
^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v2] Autovacuum tables in descending order of age
@ 2019-11-28 07:50 David Fetter <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: David Fetter @ 2019-11-28 07:50 UTC (permalink / raw)
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.
*/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: parse/analyze API refactoring
@ 2022-02-28 18:51 Nathan Bossart <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Nathan Bossart @ 2022-02-28 18:51 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: Bossart, Nathan <[email protected]>; pgsql-hackers
On Mon, Feb 28, 2022 at 07:46:40AM +0100, Peter Eisentraut wrote:
> You set this commit fest entry to Waiting on Author, but there were no
> reviews posted and the patch still applies and builds AFAICT, so I don't
> know what you meant by that.
Apologies for the lack of clarity. I believe my only feedback was around
deduplicating the pg_analyze_and_rewrite_*() functions. Would you rather
handle that in a separate patch?
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: parse/analyze API refactoring
@ 2022-03-09 10:35 Peter Eisentraut <[email protected]>
parent: Nathan Bossart <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Peter Eisentraut @ 2022-03-09 10:35 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Bossart, Nathan <[email protected]>; pgsql-hackers
On 28.02.22 19:51, Nathan Bossart wrote:
> On Mon, Feb 28, 2022 at 07:46:40AM +0100, Peter Eisentraut wrote:
>> You set this commit fest entry to Waiting on Author, but there were no
>> reviews posted and the patch still applies and builds AFAICT, so I don't
>> know what you meant by that.
>
> Apologies for the lack of clarity. I believe my only feedback was around
> deduplicating the pg_analyze_and_rewrite_*() functions. Would you rather
> handle that in a separate patch?
I have committed my original patches. I'll leave the above-mentioned
topic as ideas for the future.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: parse/analyze API refactoring
@ 2022-03-09 22:16 Nathan Bossart <[email protected]>
parent: Peter Eisentraut <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Nathan Bossart @ 2022-03-09 22:16 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: Bossart, Nathan <[email protected]>; pgsql-hackers
On Wed, Mar 09, 2022 at 11:35:32AM +0100, Peter Eisentraut wrote:
> I have committed my original patches. I'll leave the above-mentioned topic
> as ideas for the future.
Sounds good.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH 04/13] doc review: basebackup_to_shell.required_role
@ 2022-04-07 14:04 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Justin Pryzby @ 2022-04-07 14:04 UTC (permalink / raw)
c6306db24bd913375f99494e38ab315befe44e11
See also:
https://www.postgresql.org/message-id/CA%2BTgmoaBQ5idAh7OsQGAbLY166SVkj7KkKROkTyN5sOF6QDuww%40mail.g...
---
doc/src/sgml/basebackup-to-shell.sgml | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/basebackup-to-shell.sgml b/doc/src/sgml/basebackup-to-shell.sgml
index 9f44071d502..15231ef3939 100644
--- a/doc/src/sgml/basebackup-to-shell.sgml
+++ b/doc/src/sgml/basebackup-to-shell.sgml
@@ -65,8 +65,8 @@
</term>
<listitem>
<para>
- A role which replication whose privileges users are required to possess
- in order to make use of the <literal>shell</literal> backup target.
+ The role required in order to
+ make use of the <literal>shell</literal> backup target.
If this is not set, any replication user may make use of the
<literal>shell</literal> backup target.
</para>
--
2.17.1
--KlAEzMkarCnErv5Q
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="0005-Extraneous-blank-lines.patch"
^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH 11/19] doc review: basebackup_to_shell.required_role
@ 2022-04-07 14:04 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Justin Pryzby @ 2022-04-07 14:04 UTC (permalink / raw)
c6306db24bd913375f99494e38ab315befe44e11
See also:
https://www.postgresql.org/message-id/CA%2BTgmoaBQ5idAh7OsQGAbLY166SVkj7KkKROkTyN5sOF6QDuww%40mail.g...
---
doc/src/sgml/basebackup-to-shell.sgml | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/basebackup-to-shell.sgml b/doc/src/sgml/basebackup-to-shell.sgml
index 9f44071d502..15231ef3939 100644
--- a/doc/src/sgml/basebackup-to-shell.sgml
+++ b/doc/src/sgml/basebackup-to-shell.sgml
@@ -65,8 +65,8 @@
</term>
<listitem>
<para>
- A role which replication whose privileges users are required to possess
- in order to make use of the <literal>shell</literal> backup target.
+ The role required in order to
+ make use of the <literal>shell</literal> backup target.
If this is not set, any replication user may make use of the
<literal>shell</literal> backup target.
</para>
--
2.17.1
--8X7/QrJGcKSMr1RN
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="0012-v15-comment-typos.patch"
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2022-04-07 14:04 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-11-28 07:50 [PATCH v3] Autovacuum tables in descending order of age David Fetter <[email protected]>
2019-11-28 07:50 [PATCH v2] Autovacuum tables in descending order of age David Fetter <[email protected]>
2019-11-28 07:50 [PATCH v1] Autovacuum tables in descending order of age David Fetter <[email protected]>
2022-02-28 18:51 Re: parse/analyze API refactoring Nathan Bossart <[email protected]>
2022-03-09 10:35 ` Re: parse/analyze API refactoring Peter Eisentraut <[email protected]>
2022-03-09 22:16 ` Re: parse/analyze API refactoring Nathan Bossart <[email protected]>
2022-04-07 14:04 [PATCH 04/13] doc review: basebackup_to_shell.required_role Justin Pryzby <[email protected]>
2022-04-07 14:04 [PATCH 11/19] doc review: basebackup_to_shell.required_role Justin Pryzby <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox