public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v7 2/3] Remove "dead" flag from catcache tuple
2+ messages / 2 participants
[nested] [flat]
* [PATCH v7 2/3] Remove "dead" flag from catcache tuple
@ 2020-11-18 07:57 Kyotaro Horiguchi <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Kyotaro Horiguchi @ 2020-11-18 07:57 UTC (permalink / raw)
---
src/backend/utils/cache/catcache.c | 43 +++++++++++++-----------------
src/include/utils/catcache.h | 10 -------
2 files changed, 18 insertions(+), 35 deletions(-)
diff --git a/src/backend/utils/cache/catcache.c b/src/backend/utils/cache/catcache.c
index 644d92dd9a..611b65168d 100644
--- a/src/backend/utils/cache/catcache.c
+++ b/src/backend/utils/cache/catcache.c
@@ -480,6 +480,13 @@ CatCacheRemoveCTup(CatCache *cache, CatCTup *ct)
Assert(ct->refcount == 0);
Assert(ct->my_cache == cache);
+ /* delink from linked list if not yet */
+ if (ct->cache_elem.prev)
+ {
+ dlist_delete(&ct->cache_elem);
+ ct->cache_elem.prev = NULL;
+ }
+
if (ct->c_list)
{
/*
@@ -487,14 +494,10 @@ CatCacheRemoveCTup(CatCache *cache, CatCTup *ct)
* which will recurse back to me, and the recursive call will do the
* work. Set the "dead" flag to make sure it does recurse.
*/
- ct->dead = true;
CatCacheRemoveCList(cache, ct->c_list);
return; /* nothing left to do */
}
- /* delink from linked list */
- dlist_delete(&ct->cache_elem);
-
/*
* Free keys when we're dealing with a negative entry, normal entries just
* point into tuple, allocated together with the CatCTup.
@@ -534,7 +537,7 @@ CatCacheRemoveCList(CatCache *cache, CatCList *cl)
/* if the member is dead and now has no references, remove it */
if (
#ifndef CATCACHE_FORCE_RELEASE
- ct->dead &&
+ ct->cache_elem.prev == NULL &&
#endif
ct->refcount == 0)
CatCacheRemoveCTup(cache, ct);
@@ -609,7 +612,9 @@ CatCacheInvalidate(CatCache *cache, uint32 hashValue)
if (ct->refcount > 0 ||
(ct->c_list && ct->c_list->refcount > 0))
{
- ct->dead = true;
+ dlist_delete(&ct->cache_elem);
+ ct->cache_elem.prev = NULL;
+
/* list, if any, was marked dead above */
Assert(ct->c_list == NULL || ct->c_list->dead);
}
@@ -688,7 +693,8 @@ ResetCatalogCache(CatCache *cache)
if (ct->refcount > 0 ||
(ct->c_list && ct->c_list->refcount > 0))
{
- ct->dead = true;
+ dlist_delete(&ct->cache_elem);
+ ct->cache_elem.prev = NULL;
/* list, if any, was marked dead above */
Assert(ct->c_list == NULL || ct->c_list->dead);
}
@@ -1268,9 +1274,6 @@ SearchCatCacheInternal(CatCache *cache,
{
ct = dlist_container(CatCTup, cache_elem, iter.cur);
- if (ct->dead)
- continue; /* ignore dead entries */
-
if (ct->hash_value != hashValue)
continue; /* quickly skip entry if wrong hash val */
@@ -1522,7 +1525,6 @@ ReleaseCatCache(HeapTuple tuple)
offsetof(CatCTup, tuple));
/* Safety checks to ensure we were handed a cache entry */
- Assert(ct->ct_magic == CT_MAGIC);
Assert(ct->refcount > 0);
ct->refcount--;
@@ -1530,7 +1532,7 @@ ReleaseCatCache(HeapTuple tuple)
if (
#ifndef CATCACHE_FORCE_RELEASE
- ct->dead &&
+ ct->cache_elem.prev == NULL &&
#endif
ct->refcount == 0 &&
(ct->c_list == NULL || ct->c_list->refcount == 0))
@@ -1737,8 +1739,8 @@ SearchCatCacheList(CatCache *cache,
{
ct = dlist_container(CatCTup, cache_elem, iter.cur);
- if (ct->dead || ct->negative)
- continue; /* ignore dead and negative entries */
+ if (ct->negative)
+ continue; /* ignore negative entries */
if (ct->hash_value != hashValue)
continue; /* quickly skip entry if wrong hash val */
@@ -1799,14 +1801,13 @@ SearchCatCacheList(CatCache *cache,
{
foreach(ctlist_item, ctlist)
{
+ Assert (ct->cache_elem.prev != NULL);
+
ct = (CatCTup *) lfirst(ctlist_item);
Assert(ct->c_list == NULL);
Assert(ct->refcount > 0);
ct->refcount--;
if (
-#ifndef CATCACHE_FORCE_RELEASE
- ct->dead &&
-#endif
ct->refcount == 0 &&
(ct->c_list == NULL || ct->c_list->refcount == 0))
CatCacheRemoveCTup(cache, ct);
@@ -1834,9 +1835,6 @@ SearchCatCacheList(CatCache *cache,
/* release the temporary refcount on the member */
Assert(ct->refcount > 0);
ct->refcount--;
- /* mark list dead if any members already dead */
- if (ct->dead)
- cl->dead = true;
}
Assert(i == nmembers);
@@ -1960,11 +1958,9 @@ CatalogCacheCreateEntry(CatCache *cache, HeapTuple ntp, Datum *arguments,
* Finish initializing the CatCTup header, and add it to the cache's
* linked list and counts.
*/
- ct->ct_magic = CT_MAGIC;
ct->my_cache = cache;
ct->c_list = NULL;
ct->refcount = 0; /* for the moment */
- ct->dead = false;
ct->negative = negative;
ct->hash_value = hashValue;
ct->lastaccess = catcacheclock;
@@ -2158,9 +2154,6 @@ PrintCatCacheLeakWarning(HeapTuple tuple)
CatCTup *ct = (CatCTup *) (((char *) tuple) -
offsetof(CatCTup, tuple));
- /* Safety check to ensure we were handed a cache entry */
- Assert(ct->ct_magic == CT_MAGIC);
-
elog(WARNING, "cache reference leak: cache %s (%d), tuple %u/%u has count %d",
ct->my_cache->cc_relname, ct->my_cache->id,
ItemPointerGetBlockNumber(&(tuple->t_self)),
diff --git a/src/include/utils/catcache.h b/src/include/utils/catcache.h
index 291e857e38..53b0bf31eb 100644
--- a/src/include/utils/catcache.h
+++ b/src/include/utils/catcache.h
@@ -87,9 +87,6 @@ typedef struct catcache
typedef struct catctup
{
- int ct_magic; /* for identifying CatCTup entries */
-#define CT_MAGIC 0x57261502
-
uint32 hash_value; /* hash value for this tuple's keys */
/*
@@ -106,19 +103,12 @@ typedef struct catctup
dlist_node cache_elem; /* list member of per-bucket list */
/*
- * A tuple marked "dead" must not be returned by subsequent searches.
- * However, it won't be physically deleted from the cache until its
- * refcount goes to zero. (If it's a member of a CatCList, the list's
- * refcount must go to zero, too; also, remember to mark the list dead at
- * the same time the tuple is marked.)
- *
* A negative cache entry is an assertion that there is no tuple matching
* a particular key. This is just as useful as a normal entry so far as
* avoiding catalog searches is concerned. Management of positive and
* negative entries is identical.
*/
int refcount; /* number of active references */
- bool dead; /* dead but not yet removed? */
bool negative; /* negative cache entry? */
HeapTupleData tuple; /* tuple management header */
uint64 lastaccess; /* timestamp in us of the last usage */
--
2.27.0
----Next_Part(Wed_Jan_27_10_13_08_2021_482)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="v7-0003-catcachebench.patch"
^ permalink raw reply [nested|flat] 2+ messages in thread
* Recheck if ANALYZE is needed after VACUUM finishes by autovacuum
@ 2025-02-05 06:31 Masahiro Ikeda <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Masahiro Ikeda @ 2025-02-05 06:31 UTC (permalink / raw)
To: pgsql-hackers
Hi,
I would like to propose adding logic to recheck whether an ANALYZE is
necessary at the end of VACUUM. I believe this could be a viable idea
to mitigate a problem encountered by my customer.
The customer’s issue occurred as follows:
1. Every 5 minutes, a large amount of data (2 million records) was
updated via DELETE/COPY operations.
2. Autovacuum executed VACUUM/ANALYZE, and the VACUUM process
took 2 hours to complete.
3. Before the next autovacuum run (autovacuum_naptime=1min), no
further DELETE/COPY operations occurred, leaving mod_since_analyze=0.
4. Autovacuum then ran VACUUM without ANALYZE, taking another 2 hours
to complete.
5. At the end of this VACUUM, pg_class.reltuples was updated to a very
small value (150), far lower than the actual row count (2 million).
6. The execution plan changed, resulting in a significant degradation
in query performance (500ms → 1000s). The bitmap index scan node was
estimated to return 30 rows but actually returned 50 million rows.
7. Query performance remained degraded until the next autovacuum run,
which finally executed ANALYZE. However, since the next VACUUM would
also take 2 hours, performance degradation persisted during that
period.
As you may already be thinking, there are ways to address or mitigate
this
issue depending on the application requirements and hardware
configuration.
For example, using TRUNCATE or adjusting parameters like cost_delay to
speed
up VACUUM can prevent table bloat and avoid significant query
performance
degradation. However, certain system requirements may make these
solutions
impractical. Even in such cases, if there is a way to mitigate the
problem,
it would be worth adopting.
Therefore, I decided to propose $SUBJECT.
VACUUM updates pg_class.reltuples based on the state of tuples it
observes
during processing. However, it cannot observe tuples stored in pages
that
have already been vacuumed, which causes an issue. For example, in cases
like
this, there is a possibility that pg_class.reltuples may be updated to a
value
significantly smaller than the actual row count. To address such
situations,
I am considering introducing a mechanism to recheck whether an ANALYZE
is
necessary, particularly when a VACUUM process takes a long time. This
would
ensure that ANALYZE is executed promptly in such cases.
What do you think about this approach?
This approach cannot completely prevent the issue. At the very least,
between
the completion of VACUUM and the subsequent execution of ANALYZE,
pg_class.reltuples will contain highly inaccurate values. However, it
can
significantly reduce the time during which the problem occurs, thereby
mitigating the impact of incorrect statistics.
As a potential complete solution, we could consider skipping the update
of
statistics by VACUUM when an immediate ANALYZE is expected. However, I
see
this as a next step after adding the logic to recheck the necessity of
ANALYZE.
Attached are two files:
* reproduce_small_reltuples.tar.gz: Steps to reproduce the issue where
reltuples
is updated to a small value at the end of VACUUM.
* v1-0001-Add-logic-to-recheck-if-ANALYZE-is-needed-after-V.patch: A
patch
implementing the proposed logic to recheck the necessity of ANALYZE
after VACUUM.
Concerns about the v1 patch: There may be other issues or better ideas,
but here are my current concerns:
* The number of accesses to pg_class increases, which could lead to
performance issues, although I haven’t conducted performance tests.
* When checking the necessity of ANALYZE, should the latest parameter
values
be referenced? The current implementation uses the parameter values at
the
time of VACUUM execution, except for pg_class.reltuples.
I welcome your feedback. I’m open to alternative ideas and not stick to
this particular approach.
Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION
Attachments:
[text/x-diff] v1-0001-Add-logic-to-recheck-if-ANALYZE-is-needed-after-V.patch (15.9K, ../../[email protected]/2-v1-0001-Add-logic-to-recheck-if-ANALYZE-is-needed-after-V.patch)
download | inline diff:
From 319f4c304f5e9ecb0933c63696fb36205442276b Mon Sep 17 00:00:00 2001
From: Masahiro Ikeda <[email protected]>
Date: Fri, 17 Jan 2025 17:46:19 +0900
Subject: [PATCH v1] Add logic to recheck if ANALYZE is needed after VACUUM
finishes by autovacuum
In cases where VACUUM runs for a long time and frequent data updates
occur, the reported pg_class.reltuples value by VACUUM may become
extremely small, potentially leading to query performance degradation.
This update introduces a mechanism to detect if statistical information
has been updated during a prolonged VACUUM process, allowing ANALYZE to
be executed immediately to mitigate the issue.
Note that query performance degradation may still occur for a short
period before ANALYZE is executed. To fully prevent this, further
changes, such as skipping the update of pg_class.reltuples by VACUUM,
would be necessary.
---
src/backend/postmaster/autovacuum.c | 213 ++++++++++++++++--
.../test_misc/t/008_recheck_autoanalyze.pl | 91 ++++++++
2 files changed, 289 insertions(+), 15 deletions(-)
create mode 100644 src/test/modules/test_misc/t/008_recheck_autoanalyze.pl
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 0ab921a169b..486c354cea3 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -201,6 +201,8 @@ typedef struct autovac_table
VacuumParams at_params;
double at_storage_param_vac_cost_delay;
int at_storage_param_vac_cost_limit;
+ float4 anl_scale_factor;
+ int anl_base_thresh;
bool at_dobalance;
bool at_sharedrel;
char *at_relname;
@@ -334,12 +336,18 @@ static autovac_table *table_recheck_autovac(Oid relid, HTAB *table_toast_map,
static void recheck_relation_needs_vacanalyze(Oid relid, AutoVacOpts *avopts,
Form_pg_class classForm,
int effective_multixact_freeze_max_age,
- bool *dovacuum, bool *doanalyze, bool *wraparound);
+ bool *dovacuum, bool *doanalyze, bool *wraparound,
+ float4 *anl_scale_factor, int *anl_base_thresh);
+static void recheck_relation_needs_analyze(TupleDesc pg_class_desc, autovac_table *tab);
+
static void relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts,
Form_pg_class classForm,
PgStat_StatTabEntry *tabentry,
int effective_multixact_freeze_max_age,
- bool *dovacuum, bool *doanalyze, bool *wraparound);
+ bool *dovacuum, bool *doanalyze, bool *wraparound,
+ float4 *anl_scale_factor, int *anl_base_thresh);
+static void relation_needs_analyze(autovac_table *tab, Form_pg_class classForm,
+ PgStat_StatTabEntry *tabentry, bool *doanalyze);
static void autovacuum_do_vac_analyze(autovac_table *tab,
BufferAccessStrategy bstrategy);
@@ -1997,6 +2005,8 @@ do_autovacuum(void)
bool dovacuum;
bool doanalyze;
bool wraparound;
+ float4 anl_scale_factor;
+ int anl_base_thresh;
if (classForm->relkind != RELKIND_RELATION &&
classForm->relkind != RELKIND_MATVIEW)
@@ -2037,7 +2047,8 @@ do_autovacuum(void)
/* Check if it needs vacuum or analyze */
relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
effective_multixact_freeze_max_age,
- &dovacuum, &doanalyze, &wraparound);
+ &dovacuum, &doanalyze, &wraparound,
+ &anl_scale_factor, &anl_base_thresh);
/* Relations that need work are added to table_oids */
if (dovacuum || doanalyze)
@@ -2090,6 +2101,8 @@ do_autovacuum(void)
bool dovacuum;
bool doanalyze;
bool wraparound;
+ float4 anl_scale_factor;
+ int anl_base_thresh;
/*
* We cannot safely process other backends' temp tables, so skip 'em.
@@ -2120,7 +2133,8 @@ do_autovacuum(void)
relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
effective_multixact_freeze_max_age,
- &dovacuum, &doanalyze, &wraparound);
+ &dovacuum, &doanalyze, &wraparound,
+ &anl_scale_factor, &anl_base_thresh);
/* ignore analyze for toast tables */
if (dovacuum)
@@ -2470,6 +2484,80 @@ do_autovacuum(void)
did_vacuum = true;
+ /*
+ * If only VACUUM was executed, recheck whether the pgstat data
+ * indicates that ANALYZE is needed for this table.
+ *
+ * If VACUUM runs for a long time and frequent data updates occur,
+ * the reported pg_class.reltuples value may become extremely small,
+ * potentially leading to query performance degradation. Although
+ * the next ANALYZE will correct this, the next VACUUM might run
+ * for a long time again, and the degradation could persist for an
+ * extended period. This allows ANALYZE to be executed immediately.
+ */
+ if ((tab->at_params.options & VACOPT_VACUUM) &&
+ !(tab->at_params.options & VACOPT_ANALYZE))
+ {
+#ifdef USE_INJECTION_POINTS
+ char *rel_nspname = get_namespace_name(get_rel_namespace(relid));
+ if (strcmp(rel_nspname, "pg_catalog") != 0 &&
+ strcmp(rel_nspname, "information_schema") != 0 &&
+ strcmp(rel_nspname, "pg_toast") != 0)
+ INJECTION_POINT("before-recheck-autoanalyze");
+#endif
+ /* start transaction to fetch latest relation's relcache entry */
+ CommitTransactionCommand();
+ StartTransactionCommand();
+
+ recheck_relation_needs_analyze(pg_class_desc, tab);
+
+ if (tab->at_params.options & VACOPT_ANALYZE)
+ {
+ /*
+ * We will abort vacuuming the current table if something errors out,
+ * and continue with the next one in schedule; in particular, this
+ * happens if we are interrupted with SIGINT.
+ */
+ PG_TRY();
+ {
+ /* Use PortalContext for any per-table allocations */
+ MemoryContextSwitchTo(PortalContext);
+
+ /* have at it */
+ autovacuum_do_vac_analyze(tab, bstrategy);
+
+ /*
+ * Clear a possible query-cancel signal, to avoid a late reaction
+ * to an automatically-sent signal because of vacuuming the
+ * current table (we're done with it, so it would make no sense to
+ * cancel at this point.)
+ */
+ QueryCancelPending = false;
+ }
+ PG_CATCH();
+ {
+ /*
+ * Abort the transaction, start a new one, and proceed with the
+ * next table in our list.
+ */
+ HOLD_INTERRUPTS();
+ errcontext("automatic analyze of table \"%s.%s.%s\"",
+ tab->at_datname, tab->at_nspname, tab->at_relname);
+ EmitErrorReport();
+
+ /* this resets ProcGlobal->statusFlags[i] too */
+ AbortOutOfAnyTransaction();
+ FlushErrorState();
+ MemoryContextReset(PortalContext);
+
+ /* restart our transaction for the following operations */
+ StartTransactionCommand();
+ RESUME_INTERRUPTS();
+ }
+ PG_END_TRY();
+ }
+ }
+
/* ProcGlobal->statusFlags[i] are reset at the next end of xact */
/* be tidy */
@@ -2706,6 +2794,49 @@ extract_autovac_opts(HeapTuple tup, TupleDesc pg_class_desc)
return av;
}
+/*
+ * table_recheck_autoanalyze
+ *
+ * Recheck whether a table needs analyze after vacuum completes,
+ * and update tab->at_params.options.
+ */
+static void
+recheck_relation_needs_analyze(TupleDesc pg_class_desc, autovac_table *tab)
+{
+ HeapTuple classTup;
+ Form_pg_class classForm;
+ PgStat_StatTabEntry *tabentry;
+ bool doanalyze = false;
+
+ /* ANALYZE refuses to work with pg_statistic */
+ if (tab->at_relid == StatisticRelationId)
+ return;
+
+ /* fetch the relation's relcache entry */
+ classTup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(tab->at_relid));
+ if (!HeapTupleIsValid(classTup))
+ return;
+ classForm = (Form_pg_class) GETSTRUCT(classTup);
+
+ /* ignore ANALYZE for toast tables */
+ if (classForm->relkind == RELKIND_TOASTVALUE)
+ {
+ heap_freetuple(classTup);
+ return;
+ }
+
+ /* fetch the pgstat table entry */
+ tabentry = pgstat_fetch_stat_tabentry_ext(classForm->relisshared,
+ tab->at_relid);
+
+ relation_needs_analyze(tab, classForm, tabentry, &doanalyze);
+
+ /* update options */
+ if (doanalyze)
+ tab->at_params.options = VACOPT_ANALYZE;
+
+ heap_freetuple(classTup);
+}
/*
* table_recheck_autovac
@@ -2727,6 +2858,8 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
autovac_table *tab = NULL;
bool wraparound;
AutoVacOpts *avopts;
+ float4 anl_scale_factor;
+ int anl_base_thresh;
/* fetch the relation's relcache entry */
classTup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(relid));
@@ -2752,7 +2885,8 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
recheck_relation_needs_vacanalyze(relid, avopts, classForm,
effective_multixact_freeze_max_age,
- &dovacuum, &doanalyze, &wraparound);
+ &dovacuum, &doanalyze, &wraparound,
+ &anl_scale_factor, &anl_base_thresh);
/* OK, it needs something done */
if (doanalyze || dovacuum)
@@ -2830,6 +2964,8 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
avopts->vacuum_cost_limit : 0;
tab->at_storage_param_vac_cost_delay = avopts ?
avopts->vacuum_cost_delay : -1;
+ tab->anl_scale_factor = anl_scale_factor;
+ tab->anl_base_thresh = anl_base_thresh;
tab->at_relname = NULL;
tab->at_nspname = NULL;
tab->at_datname = NULL;
@@ -2862,7 +2998,9 @@ recheck_relation_needs_vacanalyze(Oid relid,
int effective_multixact_freeze_max_age,
bool *dovacuum,
bool *doanalyze,
- bool *wraparound)
+ bool *wraparound,
+ float4 *anl_scale_factor,
+ int *anl_base_thresh)
{
PgStat_StatTabEntry *tabentry;
@@ -2872,7 +3010,8 @@ recheck_relation_needs_vacanalyze(Oid relid,
relation_needs_vacanalyze(relid, avopts, classForm, tabentry,
effective_multixact_freeze_max_age,
- dovacuum, doanalyze, wraparound);
+ dovacuum, doanalyze, wraparound,
+ anl_scale_factor, anl_base_thresh);
/* ignore ANALYZE for toast tables */
if (classForm->relkind == RELKIND_TOASTVALUE)
@@ -2925,7 +3064,9 @@ relation_needs_vacanalyze(Oid relid,
/* output params below */
bool *dovacuum,
bool *doanalyze,
- bool *wraparound)
+ bool *wraparound,
+ float4 *anl_scale_factor,
+ int *anl_base_thresh)
{
bool force_vacuum;
bool av_enabled;
@@ -2933,11 +3074,9 @@ relation_needs_vacanalyze(Oid relid,
/* constants from reloptions or GUC variables */
int vac_base_thresh,
- vac_ins_base_thresh,
- anl_base_thresh;
+ vac_ins_base_thresh;
float4 vac_scale_factor,
- vac_ins_scale_factor,
- anl_scale_factor;
+ vac_ins_scale_factor;
/* thresholds calculated from above constants */
float4 vacthresh,
@@ -2983,11 +3122,11 @@ relation_needs_vacanalyze(Oid relid,
? relopts->vacuum_ins_threshold
: autovacuum_vac_ins_thresh;
- anl_scale_factor = (relopts && relopts->analyze_scale_factor >= 0)
+ *anl_scale_factor = (relopts && relopts->analyze_scale_factor >= 0)
? relopts->analyze_scale_factor
: autovacuum_anl_scale;
- anl_base_thresh = (relopts && relopts->analyze_threshold >= 0)
+ *anl_base_thresh = (relopts && relopts->analyze_threshold >= 0)
? relopts->analyze_threshold
: autovacuum_anl_thresh;
@@ -3048,7 +3187,7 @@ relation_needs_vacanalyze(Oid relid,
vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
vacinsthresh = (float4) vac_ins_base_thresh + vac_ins_scale_factor * reltuples;
- anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;
+ anlthresh = (float4) *anl_base_thresh + *anl_scale_factor * reltuples;
/*
* Note that we don't need to take special consideration for stat
@@ -3085,6 +3224,50 @@ relation_needs_vacanalyze(Oid relid,
*doanalyze = false;
}
+/*
+ * relation_needs_analyze
+ *
+ * Check whether a relation needs to be analyzed. It reuses analyze equation
+ * parameters when called from relation_needs_vacanalyze().
+ */
+static void
+relation_needs_analyze(autovac_table *tab,
+ Form_pg_class classForm,
+ PgStat_StatTabEntry *tabentry,
+ /* output params below */
+ bool *doanalyze)
+{
+ /*
+ * If we found stats for the table, and autovacuum is currently enabled,
+ * make a threshold-based decision whether to analyze.
+ */
+ if (PointerIsValid(tabentry) && AutoVacuumingActive())
+ {
+ float4 reltuples;
+ float4 anlthresh;
+ float4 anltuples;
+
+ Assert(classForm != NULL);
+ Assert(OidIsValid(tab->at_relid));
+
+ reltuples = classForm->reltuples;
+ anltuples = tabentry->mod_since_analyze;
+
+ /* Vacuum could update the value to -1 */
+ if (reltuples < 0)
+ reltuples = 0;
+
+ /* Determine if this table needs analyze. */
+ anlthresh = (float4) tab->anl_base_thresh + tab->anl_scale_factor * reltuples;
+ *doanalyze = (anltuples > anlthresh);
+
+ elog(DEBUG3, "%s: anl: %.0f (threshold %.0f)",
+ NameStr(classForm->relname), anltuples, anlthresh);
+ }
+ else
+ *doanalyze = false;
+}
+
/*
* autovacuum_do_vac_analyze
* Vacuum and/or analyze the specified table
diff --git a/src/test/modules/test_misc/t/008_recheck_autoanalyze.pl b/src/test/modules/test_misc/t/008_recheck_autoanalyze.pl
new file mode 100644
index 00000000000..bd64ec7bc36
--- /dev/null
+++ b/src/test/modules/test_misc/t/008_recheck_autoanalyze.pl
@@ -0,0 +1,91 @@
+# Copyright (c) 2025, PostgreSQL Global Development Group
+
+# Tests rechecking whether an ANALYZE is required after an autovacuum
+# performs only a VACUUM on a table, even if the table didn't initially
+# require an ANALYZE when the VACUUM started.
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use Test::More;
+
+if ($ENV{enable_injection_points} ne 'yes')
+{
+ plan skip_all => 'Injection points not supported by this build';
+}
+
+# Initialize postgres
+my $psql_err = '';
+my $psql_out = '';
+my $node = PostgreSQL::Test::Cluster->new('node');
+$node->init;
+
+$node->append_conf(
+ 'postgresql.conf', qq[
+# This ensures a quick worker spawn.
+autovacuum_naptime = 1s
+log_autovacuum_min_duration = 0
+]);
+$node->start;
+
+# Check if the extension injection_points is available, as it may be
+# possible that this script is run with installcheck, where the module
+# would not be installed by default.
+if (!$node->check_extension('injection_points'))
+{
+ plan skip_all => 'Extension injection_points not installed';
+}
+
+$node->safe_psql('postgres', 'CREATE EXTENSION injection_points;');
+
+# From this point, autovacuum worker will wait after vacuuming one heap relation.
+$node->safe_psql('postgres',
+ "SELECT injection_points_attach('before-recheck-autoanalyze', 'wait');");
+
+# Grab location in logs of primary
+my $offset = -s $node->logfile;
+
+# Create a table and insert 70 rows of data, which exceeds the value of
+# autovacuum_vacuum_insert_threshold and doesn't exceeds the value of
+# autovacuum_analyze_threshold.
+$node->safe_psql(
+ 'postgres', qq[
+ CREATE TABLE public.test (id int) WITH
+ (autovacuum_vacuum_insert_threshold = 50, autovacuum_analyze_threshold = 100);
+ INSERT INTO public.test SELECT generate_series(1, 70);
+]);
+
+# Wait until an autovacuum worker starts.
+$node->wait_for_event('autovacuum worker', 'before-recheck-autoanalyze');
+
+# And grab one of them.
+my $av_pid = $node->safe_psql(
+ 'postgres',
+ "SELECT pid FROM pg_stat_activity "
+ . "WHERE backend_type = 'autovacuum worker' AND wait_event = 'before-recheck-autoanalyze';");
+
+# insert data to exceeds the value of autovacuum_analyze_threshold
+$node->safe_psql('postgres',
+ "INSERT INTO public.test SELECT generate_series(1, 70);");
+
+# Wakeup the injection point.
+$node->safe_psql('postgres',
+ "SELECT injection_points_wakeup('before-recheck-autoanalyze');");
+
+# Wait for the autovacuum worker to exit before scanning the logs.
+$node->poll_query_until('postgres',
+ "SELECT count(*) = 0 FROM pg_stat_activity "
+ . "WHERE pid = '$av_pid' AND backend_type = 'autovacuum worker';");
+
+# Check that ANALYZE is executed.
+ok($node->log_contains(
+ qr/\[$av_pid\] LOG: automatic analyze of table "postgres.public.test"/,
+ $offset),
+ "ANALYZE triggered by recheck after vacuum");
+
+# Release injection point.
+$node->safe_psql('postgres',
+ "SELECT injection_points_detach('before-recheck-autoanalyze');");
+
+$node->stop;
+done_testing();
--
2.34.1
[application/gzip] reproduce_small_reltuples.tar.gz (2.9K, ../../[email protected]/3-reproduce_small_reltuples.tar.gz)
download
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-02-05 06:31 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-11-18 07:57 [PATCH v7 2/3] Remove "dead" flag from catcache tuple Kyotaro Horiguchi <[email protected]>
2025-02-05 06:31 Recheck if ANALYZE is needed after VACUUM finishes by autovacuum Masahiro Ikeda <[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