Alvaro Herrera escribió:
> Okay, here's a patch along these lines. I haven't considered Jim's
> suggestion downthread about discounting dead tuples from relpages; maybe
> we can do that by subtracting the pages attributed to dead ones,
> estimating via tuple density (reltuples/relpages).
Patch attached.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
*** a/src/backend/postmaster/autovacuum.c
--- b/src/backend/postmaster/autovacuum.c
***************
*** 167,173 **** typedef struct avw_dbase
PgStat_StatDBEntry *adw_entry;
} avw_dbase;
! /* struct to keep track of tables to vacuum and/or analyze, in 1st pass */
typedef struct av_relation
{
Oid ar_toastrelid; /* hash key - must be first */
--- 167,173 ----
PgStat_StatDBEntry *adw_entry;
} avw_dbase;
! /* struct to keep track of TOAST<->main relation mappings */
typedef struct av_relation
{
Oid ar_toastrelid; /* hash key - must be first */
***************
*** 177,182 **** typedef struct av_relation
--- 177,201 ----
* reloptions, or NULL if none */
} av_relation;
+ /*
+ * A tasklist is a set of tables to process, collected during a worker's first
+ * phase. For each table we keep track of its Browne strength, so that we can
+ * process in priority order.
+ */
+ typedef struct avw_tltable
+ {
+ Oid tt_reloid;
+ float4 tt_browne_strength;
+ } avw_tltable;
+
+ typedef struct avw_tasklist
+ {
+ int tl_maxelts;
+ int tl_nelts;
+ avw_tltable **tl_elts;
+ } avw_tasklist;
+
+
/* struct to keep track of tables to vacuum and/or analyze, after rechecking */
typedef struct autovac_table
{
***************
*** 299,305 **** static autovac_table *table_recheck_autovac(Oid relid, HTAB *table_toast_map,
static void relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts,
Form_pg_class classForm,
PgStat_StatTabEntry *tabentry,
! bool *dovacuum, bool *doanalyze, bool *wraparound);
static void autovacuum_do_vac_analyze(autovac_table *tab,
BufferAccessStrategy bstrategy);
--- 318,326 ----
static void relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts,
Form_pg_class classForm,
PgStat_StatTabEntry *tabentry,
! bool *dovacuum, bool *doanalyze, bool *wraparound,
! float4 *deadtuples, float4 *relpages,
! uint32 *xidage);
static void autovacuum_do_vac_analyze(autovac_table *tab,
BufferAccessStrategy bstrategy);
***************
*** 1890,1895 **** get_database_list(void)
--- 1911,1979 ----
return dblist;
}
+ static avw_tasklist *
+ tasklist_initialize(void)
+ {
+ avw_tasklist *tasklist;
+
+ tasklist = palloc(sizeof(avw_tasklist));
+ tasklist->tl_maxelts = 32;
+ tasklist->tl_nelts = 0;
+ tasklist->tl_elts = palloc(tasklist->tl_maxelts * sizeof(avw_tltable *));
+
+ return tasklist;
+ }
+
+ /*
+ * Add a table to the tasklisk.
+ */
+ static void
+ tasklist_add_table(avw_tasklist *tasklist, Oid relid, bool dovacuum,
+ bool doanalyze, bool wraparound, float4 deadtuples,
+ float4 relpages, uint32 xidage)
+ {
+ avw_tltable *tab;
+
+ /* enlarge the array if necessary */
+ if (tasklist->tl_nelts >= tasklist->tl_maxelts)
+ {
+ tasklist->tl_maxelts *= 2;
+ tasklist->tl_elts = repalloc(tasklist->tl_elts, tasklist->tl_maxelts *
+ sizeof(avw_tltable *));
+ }
+
+ tab = palloc0(sizeof(avw_tltable));
+
+ tab->tt_reloid = relid;
+ if (dovacuum)
+ {
+ tab->tt_browne_strength = deadtuples / relpages +
+ exp(xidage * logf(relpages) / UINT_MAX);
+ }
+
+ tasklist->tl_elts[tasklist->tl_nelts++] = tab;
+ }
+
+ /*
+ * qsort comparator: sorts avw_tltable elements by value of Browne strength,
+ * descending
+ */
+ static int
+ avw_tt_compar(const void *a, const void *b)
+ {
+ const avw_tltable *taba = *(avw_tltable *const *) a;
+ const avw_tltable *tabb = *(avw_tltable *const *) b;
+
+ return tabb->tt_browne_strength - taba->tt_browne_strength;
+ }
+
+ static void
+ tasklist_sort(avw_tasklist *tasklist)
+ {
+ qsort(tasklist->tl_elts, tasklist->tl_nelts, sizeof(avw_tltable *),
+ avw_tt_compar);
+ }
+
/*
* Process a database table-by-table
*
***************
*** 1903,1917 **** do_autovacuum(void)
HeapTuple tuple;
HeapScanDesc relScan;
Form_pg_database dbForm;
- List *table_oids = NIL;
HASHCTL ctl;
HTAB *table_toast_map;
- ListCell *volatile cell;
PgStat_StatDBEntry *shared;
PgStat_StatDBEntry *dbentry;
BufferAccessStrategy bstrategy;
ScanKeyData key;
TupleDesc pg_class_desc;
/*
* StartTransactionCommand and CommitTransactionCommand will automatically
--- 1987,2001 ----
HeapTuple tuple;
HeapScanDesc relScan;
Form_pg_database dbForm;
HASHCTL ctl;
HTAB *table_toast_map;
PgStat_StatDBEntry *shared;
PgStat_StatDBEntry *dbentry;
BufferAccessStrategy bstrategy;
ScanKeyData key;
TupleDesc pg_class_desc;
+ avw_tasklist *tasklist;
+ int i;
/*
* StartTransactionCommand and CommitTransactionCommand will automatically
***************
*** 1986,1991 **** do_autovacuum(void)
--- 2070,2078 ----
&ctl,
HASH_ELEM | HASH_FUNCTION);
+ /* initialize our tasklist */
+ tasklist = tasklist_initialize();
+
/*
* Scan pg_class to determine which tables to vacuum.
*
***************
*** 2020,2025 **** do_autovacuum(void)
--- 2107,2115 ----
bool dovacuum;
bool doanalyze;
bool wraparound;
+ float4 deadtuples;
+ float4 relpages;
+ uint32 xidage;
relid = HeapTupleGetOid(tuple);
***************
*** 2030,2036 **** do_autovacuum(void)
/* Check if it needs vacuum or analyze */
relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
! &dovacuum, &doanalyze, &wraparound);
/*
* Check if it is a temp table (presumably, of some other backend's).
--- 2120,2127 ----
/* Check if it needs vacuum or analyze */
relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
! &dovacuum, &doanalyze, &wraparound,
! &deadtuples, &relpages, &xidage);
/*
* Check if it is a temp table (presumably, of some other backend's).
***************
*** 2077,2085 **** do_autovacuum(void)
}
else
{
! /* relations that need work are added to table_oids */
if (dovacuum || doanalyze)
! table_oids = lappend_oid(table_oids, relid);
/*
* Remember the association for the second pass. Note: we must do
--- 2168,2177 ----
}
else
{
! /* relations that need work are added to our tasklist */
if (dovacuum || doanalyze)
! tasklist_add_table(tasklist, relid, dovacuum, doanalyze,
! wraparound, deadtuples, relpages, xidage);
/*
* Remember the association for the second pass. Note: we must do
***************
*** 2129,2134 **** do_autovacuum(void)
--- 2221,2229 ----
bool dovacuum;
bool doanalyze;
bool wraparound;
+ float4 deadtuples;
+ float4 relpages;
+ uint32 xidage;
/*
* We cannot safely process other backends' temp tables, so skip 'em.
***************
*** 2158,2168 **** do_autovacuum(void)
shared, dbentry);
relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
! &dovacuum, &doanalyze, &wraparound);
/* ignore analyze for toast tables */
if (dovacuum)
! table_oids = lappend_oid(table_oids, relid);
}
heap_endscan(relScan);
--- 2253,2265 ----
shared, dbentry);
relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
! &dovacuum, &doanalyze, &wraparound,
! &deadtuples, &relpages, &xidage);
/* ignore analyze for toast tables */
if (dovacuum)
! tasklist_add_table(tasklist, relid, dovacuum, doanalyze,
! wraparound, deadtuples, relpages, xidage);
}
heap_endscan(relScan);
***************
*** 2185,2202 **** do_autovacuum(void)
ALLOCSET_DEFAULT_MINSIZE,
ALLOCSET_DEFAULT_MAXSIZE);
/*
* Perform operations on collected tables.
*/
! foreach(cell, table_oids)
{
- Oid relid = lfirst_oid(cell);
autovac_table *tab;
bool skipit;
int stdVacuumCostDelay;
int stdVacuumCostLimit;
dlist_iter iter;
CHECK_FOR_INTERRUPTS();
/*
--- 2282,2304 ----
ALLOCSET_DEFAULT_MINSIZE,
ALLOCSET_DEFAULT_MAXSIZE);
+ /* sort our task list */
+ tasklist_sort(tasklist);
+
/*
* Perform operations on collected tables.
*/
! for (i = 0; i < tasklist->tl_nelts; i++)
{
autovac_table *tab;
+ Oid relid;
bool skipit;
int stdVacuumCostDelay;
int stdVacuumCostLimit;
dlist_iter iter;
+ relid = tasklist->tl_elts[i]->tt_reloid;
+
CHECK_FOR_INTERRUPTS();
/*
***************
*** 2499,2505 **** table_recheck_autovac(Oid relid, HTAB *table_toast_map,
shared, dbentry);
relation_needs_vacanalyze(relid, avopts, classForm, tabentry,
! &dovacuum, &doanalyze, &wraparound);
/* ignore ANALYZE for toast tables */
if (classForm->relkind == RELKIND_TOASTVALUE)
--- 2601,2608 ----
shared, dbentry);
relation_needs_vacanalyze(relid, avopts, classForm, tabentry,
! &dovacuum, &doanalyze, &wraparound,
! NULL, NULL, NULL);
/* ignore ANALYZE for toast tables */
if (classForm->relkind == RELKIND_TOASTVALUE)
***************
*** 2565,2573 **** table_recheck_autovac(Oid relid, HTAB *table_toast_map,
/*
* relation_needs_vacanalyze
*
! * Check whether a relation needs to be vacuumed or analyzed; return each into
! * "dovacuum" and "doanalyze", respectively. Also return whether the vacuum is
! * being forced because of Xid wraparound.
*
* relopts is a pointer to the AutoVacOpts options (either for itself in the
* case of a plain table, or for either itself or its parent table in the case
--- 2668,2682 ----
/*
* relation_needs_vacanalyze
*
! * Check whether a relation needs to be vacuumed or analyzed, and return each
! * into "dovacuum" and "doanalyze", respectively.
! *
! * Output parameters:
! * wraparound: whether the vacuum is being forced because of Xid wraparound
! * deadtuples: number of dead tuples in the table (from the pgstat tabentry;
! * zero if table is not present in pgstat)
! * relpages: number of pages in the table (from pg_class)
! * xidage: age of relfrozenxid, compared to recentXid
*
* relopts is a pointer to the AutoVacOpts options (either for itself in the
* case of a plain table, or for either itself or its parent table in the case
***************
*** 2606,2612 **** relation_needs_vacanalyze(Oid relid,
/* output params below */
bool *dovacuum,
bool *doanalyze,
! bool *wraparound)
{
bool force_vacuum;
bool av_enabled;
--- 2715,2724 ----
/* output params below */
bool *dovacuum,
bool *doanalyze,
! bool *wraparound,
! float4 *deadtuples,
! float4 *relpages,
! uint32 *xidage)
{
bool force_vacuum;
bool av_enabled;
***************
*** 2715,2724 **** relation_needs_vacanalyze(Oid relid,
/*
* Skip a table not found in stat hash, unless we have to force vacuum
* for anti-wrap purposes. If it's not acted upon, there's no need to
! * vacuum it.
*/
*dovacuum = force_vacuum;
*doanalyze = false;
}
/* ANALYZE refuses to work with pg_statistics */
--- 2827,2848 ----
/*
* Skip a table not found in stat hash, unless we have to force vacuum
* for anti-wrap purposes. If it's not acted upon, there's no need to
! * vacuum it. It seems okay to say that there are no tuples to remove,
! * either.
*/
*dovacuum = force_vacuum;
*doanalyze = false;
+ vactuples = 0;
+ }
+
+ if (*dovacuum)
+ {
+ if (deadtuples)
+ *deadtuples = vactuples;
+ if (relpages)
+ *relpages = classForm->relpages;
+ if (xidage)
+ *xidage = recentXid - classForm->relfrozenxid;
}
/* ANALYZE refuses to work with pg_statistics */
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
