agora inbox for [email protected]
help / color / mirror / Atom feed[PATCH 1/4] Remove entries that haven't been used for a certain time
6+ messages / 3 participants
[nested] [flat]
* [PATCH 1/4] Remove entries that haven't been used for a certain time
@ 2017-12-26 08:43 Kyotaro Horiguchi <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Kyotaro Horiguchi @ 2017-12-26 08:43 UTC (permalink / raw)
Catcache entries can be left alone for several reasons. It is not
desirable that they eat up memory. With this patch, This adds
consideration of removal of entries that haven't been used for a
certain time before enlarging the hash array.
---
doc/src/sgml/config.sgml | 38 ++++++
src/backend/access/transam/xact.c | 3 +
src/backend/utils/cache/catcache.c | 153 +++++++++++++++++++++++-
src/backend/utils/cache/plancache.c | 163 ++++++++++++++++++++++++++
src/backend/utils/misc/guc.c | 33 ++++++
src/backend/utils/misc/postgresql.conf.sample | 2 +
src/include/utils/catcache.h | 19 +++
src/include/utils/plancache.h | 7 +-
8 files changed, 413 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 5b913f00c1..76745047af 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1617,6 +1617,44 @@ include_dir 'conf.d'
</listitem>
</varlistentry>
+ <varlistentry id="guc-syscache-memory-target" xreflabel="syscache_memory_target">
+ <term><varname>syscache_memory_target</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>syscache_memory_target</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the maximum amount of memory to which syscache is expanded
+ without pruning. The value defaults to 0, indicating that pruning is
+ always considered. After exceeding this size, syscache pruning is
+ considered according to
+ <xref linkend="guc-syscache-prune-min-age"/>. If you need to keep
+ certain amount of syscache entries with intermittent usage, try
+ increase this setting.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-syscache-prune-min-age" xreflabel="syscache_prune_min_age">
+ <term><varname>syscache_prune_min_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>syscache_prune_min_age</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the minimum amount of unused time in seconds at which a
+ syscache entry is considered to be removed. -1 indicates that syscache
+ pruning is disabled at all. The value defaults to 600 seconds
+ (<literal>10 minutes</literal>). The syscache entries that are not
+ used for the duration can be removed to prevent syscache bloat. This
+ behavior is suppressed until the size of syscache exceeds
+ <xref linkend="guc-syscache-memory-target"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-max-stack-depth" xreflabel="max_stack_depth">
<term><varname>max_stack_depth</varname> (<type>integer</type>)
<indexterm>
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index 8e6aef332c..e4a4a5874c 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -732,6 +732,9 @@ void
SetCurrentStatementStartTimestamp(void)
{
stmtStartTimestamp = GetCurrentTimestamp();
+
+ /* Set this timestamp as aproximated current time */
+ SetCatCacheClock(stmtStartTimestamp);
}
/*
diff --git a/src/backend/utils/cache/catcache.c b/src/backend/utils/cache/catcache.c
index 5ddbf6eab1..9f421cd242 100644
--- a/src/backend/utils/cache/catcache.c
+++ b/src/backend/utils/cache/catcache.c
@@ -71,9 +71,24 @@
#define CACHE6_elog(a,b,c,d,e,f,g)
#endif
+/*
+ * GUC variable to define the minimum size of hash to cosider entry eviction.
+ * This variable is shared among various cache mechanisms.
+ */
+int cache_memory_target = 0;
+
+/* GUC variable to define the minimum age of entries that will be cosidered to
+ * be evicted in seconds. This variable is shared among various cache
+ * mechanisms.
+ */
+int cache_prune_min_age = 600;
+
/* Cache management header --- pointer is NULL until created */
static CatCacheHeader *CacheHdr = NULL;
+/* Timestamp used for any operation on caches. */
+TimestampTz catcacheclock = 0;
+
static inline HeapTuple SearchCatCacheInternal(CatCache *cache,
int nkeys,
Datum v1, Datum v2,
@@ -866,9 +881,130 @@ InitCatCache(int id,
*/
MemoryContextSwitchTo(oldcxt);
+ /* initilize catcache reference clock if haven't done yet */
+ if (catcacheclock == 0)
+ catcacheclock = GetCurrentTimestamp();
+
return cp;
}
+/*
+ * CatCacheCleanupOldEntries - Remove infrequently-used entries
+ *
+ * Catcache entries can be left alone for several reasons. We remove them if
+ * they are not accessed for a certain time to prevent catcache from
+ * bloating. The eviction is performed with the similar algorithm with buffer
+ * eviction using access counter. Entries that are accessed several times can
+ * live longer than those that have had no access in the same duration.
+ */
+static bool
+CatCacheCleanupOldEntries(CatCache *cp)
+{
+ int i;
+ int nremoved = 0;
+ size_t hash_size;
+#ifdef CATCACHE_STATS
+ /* These variables are only for debugging purpose */
+ int ntotal = 0;
+ /*
+ * nth element in nentries stores the number of cache entries that have
+ * lived unaccessed for corresponding multiple in ageclass of
+ * cache_prune_min_age. The index of nremoved_entry is the value of the
+ * clock-sweep counter, which takes from 0 up to 2.
+ */
+ double ageclass[] = {0.05, 0.1, 1.0, 2.0, 3.0, 0.0};
+ int nentries[] = {0, 0, 0, 0, 0, 0};
+ int nremoved_entry[3] = {0, 0, 0};
+ int j;
+#endif
+
+ /* Return immediately if no pruning is wanted */
+ if (cache_prune_min_age < 0)
+ return false;
+
+ /*
+ * Return without pruning if the size of the hash is below the target.
+ * Since the area for bucket array is dominant, consider only it.
+ */
+ hash_size = cp->cc_nbuckets * sizeof(dlist_head);
+ if (hash_size < (Size) cache_memory_target * 1024L)
+ return false;
+
+ /* Search the whole hash for entries to remove */
+ for (i = 0; i < cp->cc_nbuckets; i++)
+ {
+ dlist_mutable_iter iter;
+
+ dlist_foreach_modify(iter, &cp->cc_bucket[i])
+ {
+ CatCTup *ct = dlist_container(CatCTup, cache_elem, iter.cur);
+ long entry_age;
+ int us;
+
+
+ /*
+ * Calculate the duration from the time of the last access to the
+ * "current" time. Since catcacheclock is not advanced within a
+ * transaction, the entries that are accessed within the current
+ * transaction won't be pruned.
+ */
+ TimestampDifference(ct->lastaccess, catcacheclock, &entry_age, &us);
+
+#ifdef CATCACHE_STATS
+ /* count catcache entries for each age class */
+ ntotal++;
+ for (j = 0 ;
+ ageclass[j] != 0.0 &&
+ entry_age > cache_prune_min_age * ageclass[j] ;
+ j++);
+ if (ageclass[j] == 0.0) j--;
+ nentries[j]++;
+#endif
+
+ /*
+ * Try to remove entries older than cache_prune_min_age seconds.
+ * Entries that are not accessed after last pruning are removed in
+ * that seconds, and that has been accessed several times are
+ * removed after leaving alone for up to three times of the
+ * duration. We don't try shrink buckets since pruning effectively
+ * caps catcache expansion in the long term.
+ */
+ if (entry_age > cache_prune_min_age)
+ {
+#ifdef CATCACHE_STATS
+ Assert (ct->naccess >= 0 && ct->naccess <= 2);
+ nremoved_entry[ct->naccess]++;
+#endif
+ if (ct->naccess > 0)
+ ct->naccess--;
+ else
+ {
+ if (!ct->c_list || ct->c_list->refcount == 0)
+ {
+ CatCacheRemoveCTup(cp, ct);
+ nremoved++;
+ }
+ }
+ }
+ }
+ }
+
+#ifdef CATCACHE_STATS
+ ereport(DEBUG1,
+ (errmsg ("removed %d/%d, age(-%.0fs:%d, -%.0fs:%d, *-%.0fs:%d, -%.0fs:%d, -%.0fs:%d) naccessed(0:%d, 1:%d, 2:%d)",
+ nremoved, ntotal,
+ ageclass[0] * cache_prune_min_age, nentries[0],
+ ageclass[1] * cache_prune_min_age, nentries[1],
+ ageclass[2] * cache_prune_min_age, nentries[2],
+ ageclass[3] * cache_prune_min_age, nentries[3],
+ ageclass[4] * cache_prune_min_age, nentries[4],
+ nremoved_entry[0], nremoved_entry[1], nremoved_entry[2]),
+ errhidestmt(true)));
+#endif
+
+ return nremoved > 0;
+}
+
/*
* Enlarge a catcache, doubling the number of buckets.
*/
@@ -1282,6 +1418,11 @@ SearchCatCacheInternal(CatCache *cache,
*/
dlist_move_head(bucket, &ct->cache_elem);
+ /* Update access information for pruning */
+ if (ct->naccess < 2)
+ ct->naccess++;
+ ct->lastaccess = catcacheclock;
+
/*
* If it's a positive entry, bump its refcount and return it. If it's
* negative, we can report failure to the caller.
@@ -1813,7 +1954,6 @@ ReleaseCatCacheList(CatCList *list)
CatCacheRemoveCList(list->my_cache, list);
}
-
/*
* CatalogCacheCreateEntry
* Create a new CatCTup entry, copying the given HeapTuple and other
@@ -1906,6 +2046,8 @@ CatalogCacheCreateEntry(CatCache *cache, HeapTuple ntp, Datum *arguments,
ct->dead = false;
ct->negative = negative;
ct->hash_value = hashValue;
+ ct->naccess = 0;
+ ct->lastaccess = catcacheclock;
dlist_push_head(&cache->cc_bucket[hashIndex], &ct->cache_elem);
@@ -1913,10 +2055,13 @@ CatalogCacheCreateEntry(CatCache *cache, HeapTuple ntp, Datum *arguments,
CacheHdr->ch_ntup++;
/*
- * If the hash table has become too full, enlarge the buckets array. Quite
- * arbitrarily, we enlarge when fill factor > 2.
+ * If the hash table has become too full, try cleanup by removing
+ * infrequently used entries to make a room for the new entry. If it
+ * failed, enlarge the bucket array instead. Quite arbitrarily, we try
+ * this when fill factor > 2.
*/
- if (cache->cc_ntup > cache->cc_nbuckets * 2)
+ if (cache->cc_ntup > cache->cc_nbuckets * 2 &&
+ !CatCacheCleanupOldEntries(cache))
RehashCatCache(cache);
return ct;
diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/plancache.c
index 0ad3e3c736..701ead152c 100644
--- a/src/backend/utils/cache/plancache.c
+++ b/src/backend/utils/cache/plancache.c
@@ -63,12 +63,14 @@
#include "storage/lmgr.h"
#include "tcop/pquery.h"
#include "tcop/utility.h"
+#include "utils/catcache.h"
#include "utils/inval.h"
#include "utils/memutils.h"
#include "utils/resowner_private.h"
#include "utils/rls.h"
#include "utils/snapmgr.h"
#include "utils/syscache.h"
+#include "utils/timestamp.h"
/*
@@ -86,6 +88,12 @@
* guarantee to save a CachedPlanSource without error.
*/
static CachedPlanSource *first_saved_plan = NULL;
+static CachedPlanSource *last_saved_plan = NULL;
+static int num_saved_plans = 0;
+static TimestampTz oldest_saved_plan = 0;
+
+/* GUC variables */
+int min_cached_plans = 1000;
static void ReleaseGenericPlan(CachedPlanSource *plansource);
static List *RevalidateCachedQuery(CachedPlanSource *plansource,
@@ -105,6 +113,7 @@ static TupleDesc PlanCacheComputeResultDesc(List *stmt_list);
static void PlanCacheRelCallback(Datum arg, Oid relid);
static void PlanCacheFuncCallback(Datum arg, int cacheid, uint32 hashvalue);
static void PlanCacheSysCallback(Datum arg, int cacheid, uint32 hashvalue);
+static void PruneCachedPlan(void);
/*
@@ -208,6 +217,8 @@ CreateCachedPlan(RawStmt *raw_parse_tree,
plansource->generic_cost = -1;
plansource->total_custom_cost = 0;
plansource->num_custom_plans = 0;
+ plansource->last_access = GetCatCacheClock();
+
MemoryContextSwitchTo(oldcxt);
@@ -423,6 +434,28 @@ CompleteCachedPlan(CachedPlanSource *plansource,
plansource->is_valid = true;
}
+/* moves the plansource to the first in the list */
+static inline void
+MovePlansourceToFirst(CachedPlanSource *plansource)
+{
+ if (first_saved_plan != plansource)
+ {
+ /* delink this element */
+ if (plansource->next_saved)
+ plansource->next_saved->prev_saved = plansource->prev_saved;
+ if (plansource->prev_saved)
+ plansource->prev_saved->next_saved = plansource->next_saved;
+ if (last_saved_plan == plansource)
+ last_saved_plan = plansource->prev_saved;
+
+ /* insert at the beginning */
+ first_saved_plan->prev_saved = plansource;
+ plansource->next_saved = first_saved_plan;
+ plansource->prev_saved = NULL;
+ first_saved_plan = plansource;
+ }
+}
+
/*
* SaveCachedPlan: save a cached plan permanently
*
@@ -470,6 +503,11 @@ SaveCachedPlan(CachedPlanSource *plansource)
* Add the entry to the global list of cached plans.
*/
plansource->next_saved = first_saved_plan;
+ if (first_saved_plan)
+ first_saved_plan->prev_saved = plansource;
+ else
+ last_saved_plan = plansource;
+ plansource->prev_saved = NULL;
first_saved_plan = plansource;
plansource->is_saved = true;
@@ -492,7 +530,11 @@ DropCachedPlan(CachedPlanSource *plansource)
if (plansource->is_saved)
{
if (first_saved_plan == plansource)
+ {
first_saved_plan = plansource->next_saved;
+ if (first_saved_plan)
+ first_saved_plan->prev_saved = NULL;
+ }
else
{
CachedPlanSource *psrc;
@@ -502,10 +544,19 @@ DropCachedPlan(CachedPlanSource *plansource)
if (psrc->next_saved == plansource)
{
psrc->next_saved = plansource->next_saved;
+ if (psrc->next_saved)
+ psrc->next_saved->prev_saved = psrc;
break;
}
}
}
+
+ if (last_saved_plan == plansource)
+ {
+ last_saved_plan = plansource->prev_saved;
+ if (last_saved_plan)
+ last_saved_plan->next_saved = NULL;
+ }
plansource->is_saved = false;
}
@@ -537,6 +588,13 @@ ReleaseGenericPlan(CachedPlanSource *plansource)
Assert(plan->magic == CACHEDPLAN_MAGIC);
plansource->gplan = NULL;
ReleaseCachedPlan(plan, false);
+
+ /* decrement "saved plans" counter */
+ if (plansource->is_saved)
+ {
+ Assert (num_saved_plans > 0);
+ num_saved_plans--;
+ }
}
}
@@ -1148,6 +1206,17 @@ GetCachedPlan(CachedPlanSource *plansource, ParamListInfo boundParams,
if (useResOwner && !plansource->is_saved)
elog(ERROR, "cannot apply ResourceOwner to non-saved cached plan");
+ /*
+ * set last-accessed timestamp and move this plan to the first of the list
+ */
+ if (plansource->is_saved)
+ {
+ plansource->last_access = GetCatCacheClock();
+
+ /* move this plan to the first of the list */
+ MovePlansourceToFirst(plansource);
+ }
+
/* Make sure the querytree list is valid and we have parse-time locks */
qlist = RevalidateCachedQuery(plansource, queryEnv);
@@ -1156,6 +1225,11 @@ GetCachedPlan(CachedPlanSource *plansource, ParamListInfo boundParams,
if (!customplan)
{
+ /* Prune cached plans if needed */
+ if (plansource->is_saved &&
+ min_cached_plans >= 0 && num_saved_plans > min_cached_plans)
+ PruneCachedPlan();
+
if (CheckCachedPlan(plansource))
{
/* We want a generic plan, and we already have a valid one */
@@ -1168,6 +1242,11 @@ GetCachedPlan(CachedPlanSource *plansource, ParamListInfo boundParams,
plan = BuildCachedPlan(plansource, qlist, NULL, queryEnv);
/* Just make real sure plansource->gplan is clear */
ReleaseGenericPlan(plansource);
+
+ /* count this new saved plan */
+ if (plansource->is_saved)
+ num_saved_plans++;
+
/* Link the new generic plan into the plansource */
plansource->gplan = plan;
plan->refcount++;
@@ -1856,6 +1935,90 @@ PlanCacheSysCallback(Datum arg, int cacheid, uint32 hashvalue)
ResetPlanCache();
}
+/*
+ * PrunePlanCache: removes generic plan of "old" saved plans.
+ */
+static void
+PruneCachedPlan(void)
+{
+ CachedPlanSource *plansource;
+ TimestampTz currclock = GetCatCacheClock();
+ long age;
+ int us;
+ int nremoved = 0;
+
+ /* do nothing if not wanted */
+ if (cache_prune_min_age < 0 || num_saved_plans <= min_cached_plans)
+ return;
+
+ /* Fast check for oldest cache */
+ if (oldest_saved_plan > 0)
+ {
+ TimestampDifference(oldest_saved_plan, currclock, &age, &us);
+ if (age < cache_prune_min_age)
+ return;
+ }
+
+ /* last plan is the oldest. */
+ for (plansource = last_saved_plan; plansource; plansource = plansource->prev_saved)
+ {
+ long plan_age;
+ int us;
+
+ Assert(plansource->magic == CACHEDPLANSOURCE_MAGIC);
+
+ /* we want to prune no more plans */
+ if (num_saved_plans <= min_cached_plans)
+ break;
+
+ /*
+ * No work if it already doesn't have gplan and move it to the
+ * beginning so that we don't see it at the next time
+ */
+ if (!plansource->gplan)
+ continue;
+
+ /*
+ * Check age for pruning. Can exit immediately when finding a
+ * not-older element.
+ */
+ TimestampDifference(plansource->last_access, currclock, &plan_age, &us);
+ if (plan_age <= cache_prune_min_age)
+ {
+ /* this entry is the next oldest */
+ oldest_saved_plan = plansource->last_access;
+ break;
+ }
+
+ /*
+ * Here, remove generic plans of this plansrouceif it is not actually
+ * used and move it to the beginning of the list. Just update
+ * last_access and move it to the beginning if the plan is used.
+ */
+ if (plansource->gplan->refcount <= 1)
+ {
+ ReleaseGenericPlan(plansource);
+ nremoved++;
+ }
+
+ plansource->last_access = currclock;
+ }
+
+ /* move the "removed" plansrouces altogehter to the beginning of the list */
+ if (plansource != last_saved_plan && plansource)
+ {
+ plansource->next_saved->prev_saved = NULL;
+ first_saved_plan->prev_saved = last_saved_plan;
+ last_saved_plan->next_saved = first_saved_plan;
+ first_saved_plan = plansource->next_saved;
+ plansource->next_saved = NULL;
+ last_saved_plan = plansource;
+ }
+
+ if (nremoved > 0)
+ elog(DEBUG1, "plancache removed %d/%d", nremoved, num_saved_plans);
+}
+
/*
* ResetPlanCache: invalidate all cached plans.
*/
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index b05fb209bb..e49346707d 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -79,6 +79,7 @@
#include "tsearch/ts_cache.h"
#include "utils/builtins.h"
#include "utils/bytea.h"
+#include "utils/catcache.h"
#include "utils/guc_tables.h"
#include "utils/memutils.h"
#include "utils/pg_locale.h"
@@ -2105,6 +2106,38 @@ static struct config_int ConfigureNamesInt[] =
NULL, NULL, NULL
},
+ {
+ {"cache_memory_target", PGC_USERSET, RESOURCES_MEM,
+ gettext_noop("Sets the minimum syscache size to keep."),
+ gettext_noop("Cache is not pruned before exceeding this size."),
+ GUC_UNIT_KB
+ },
+ &cache_memory_target,
+ 0, 0, MAX_KILOBYTES,
+ NULL, NULL, NULL
+ },
+
+ {
+ {"cache_prune_min_age", PGC_USERSET, RESOURCES_MEM,
+ gettext_noop("Sets the minimum unused duration of cache entries before removal."),
+ gettext_noop("Cache entries that live unused for longer than this seconds are considered to be removed."),
+ GUC_UNIT_S
+ },
+ &cache_prune_min_age,
+ 600, -1, INT_MAX,
+ NULL, NULL, NULL
+ },
+
+ {
+ {"min_cached_plans", PGC_USERSET, RESOURCES_MEM,
+ gettext_noop("Sets the minimum number of cached plans kept on memory."),
+ gettext_noop("Timeout invalidation of plancache is not activated until the number of plancaches reaches this value. -1 means timeout invalidation is always active.")
+ },
+ &min_cached_plans,
+ 1000, -1, INT_MAX,
+ NULL, NULL, NULL
+ },
+
/*
* We use the hopefully-safely-small value of 100kB as the compiled-in
* default for max_stack_depth. InitializeGUCOptions will increase it if
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 9e39baf466..3f2760ef9d 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -126,6 +126,8 @@
#work_mem = 4MB # min 64kB
#maintenance_work_mem = 64MB # min 1MB
#autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem
+#cache_memory_target = 0kB # in kB
+#cache_prune_min_age = 600s # -1 disables pruning
#max_stack_depth = 2MB # min 100kB
#dynamic_shared_memory_type = posix # the default is the first option
# supported by the operating system:
diff --git a/src/include/utils/catcache.h b/src/include/utils/catcache.h
index 7b22f9c7bc..599303be56 100644
--- a/src/include/utils/catcache.h
+++ b/src/include/utils/catcache.h
@@ -22,6 +22,7 @@
#include "access/htup.h"
#include "access/skey.h"
+#include "datatype/timestamp.h"
#include "lib/ilist.h"
#include "utils/relcache.h"
@@ -119,6 +120,8 @@ typedef struct catctup
bool dead; /* dead but not yet removed? */
bool negative; /* negative cache entry? */
HeapTupleData tuple; /* tuple management header */
+ int naccess; /* # of access to this entry, up to 2 */
+ TimestampTz lastaccess; /* approx. timestamp of the last usage */
/*
* The tuple may also be a member of at most one CatCList. (If a single
@@ -189,6 +192,22 @@ typedef struct catcacheheader
/* this extern duplicates utils/memutils.h... */
extern PGDLLIMPORT MemoryContext CacheMemoryContext;
+/* for guc.c, not PGDLLPMPORT'ed */
+extern int cache_prune_min_age;
+extern int cache_memory_target;
+
+/* to use as access timestamp of catcache entries */
+extern TimestampTz catcacheclock;
+
+/*
+ * SetCatCacheClock - set timestamp for catcache access record
+ */
+static inline void
+SetCatCacheClock(TimestampTz ts)
+{
+ catcacheclock = ts;
+}
+
extern void CreateCacheMemoryContext(void);
extern CatCache *InitCatCache(int id, Oid reloid, Oid indexoid,
diff --git a/src/include/utils/plancache.h b/src/include/utils/plancache.h
index ab20aa04b0..f3c5b2010d 100644
--- a/src/include/utils/plancache.h
+++ b/src/include/utils/plancache.h
@@ -110,11 +110,13 @@ typedef struct CachedPlanSource
bool is_valid; /* is the query_list currently valid? */
int generation; /* increments each time we create a plan */
/* If CachedPlanSource has been saved, it is a member of a global list */
- struct CachedPlanSource *next_saved; /* list link, if so */
+ struct CachedPlanSource *prev_saved; /* list prev link, if so */
+ struct CachedPlanSource *next_saved; /* list next link, if so */
/* State kept to help decide whether to use custom or generic plans: */
double generic_cost; /* cost of generic plan, or -1 if not known */
double total_custom_cost; /* total cost of custom plans so far */
int num_custom_plans; /* number of plans included in total */
+ TimestampTz last_access; /* timestamp of the last usage */
} CachedPlanSource;
/*
@@ -143,6 +145,9 @@ typedef struct CachedPlan
MemoryContext context; /* context containing this CachedPlan */
} CachedPlan;
+/* GUC variables */
+extern int min_cached_plans;
+extern int plancache_prune_min_age;
extern void InitPlanCache(void);
extern void ResetPlanCache(void);
--
2.16.3
----Next_Part(Tue_Jul_03_09_29_14_2018_474)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="v5-0002-introduce-dynhash-pruning.patch"
^ permalink raw reply [nested|flat] 6+ messages in thread
* [PATCH 1/3] Remove entries that haven't been used for a certain time
@ 2017-12-26 08:43 Kyotaro Horiguchi <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Kyotaro Horiguchi @ 2017-12-26 08:43 UTC (permalink / raw)
Catcache entries can be left alone for several reasons. It is not
desirable that they eat up memory. With this patch, This adds
consideration of removal of entries that haven't been used for a
certain time before enlarging the hash array.
---
doc/src/sgml/config.sgml | 38 ++++++
src/backend/access/transam/xact.c | 3 +
src/backend/utils/cache/catcache.c | 153 +++++++++++++++++++++++-
src/backend/utils/cache/plancache.c | 163 ++++++++++++++++++++++++++
src/backend/utils/misc/guc.c | 33 ++++++
src/backend/utils/misc/postgresql.conf.sample | 2 +
src/include/utils/catcache.h | 19 +++
src/include/utils/plancache.h | 7 +-
8 files changed, 413 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 7bfbc87109..4ba4327007 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1617,6 +1617,44 @@ include_dir 'conf.d'
</listitem>
</varlistentry>
+ <varlistentry id="guc-syscache-memory-target" xreflabel="syscache_memory_target">
+ <term><varname>syscache_memory_target</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>syscache_memory_target</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the maximum amount of memory to which syscache is expanded
+ without pruning. The value defaults to 0, indicating that pruning is
+ always considered. After exceeding this size, syscache pruning is
+ considered according to
+ <xref linkend="guc-syscache-prune-min-age"/>. If you need to keep
+ certain amount of syscache entries with intermittent usage, try
+ increase this setting.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-syscache-prune-min-age" xreflabel="syscache_prune_min_age">
+ <term><varname>syscache_prune_min_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>syscache_prune_min_age</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the minimum amount of unused time in seconds at which a
+ syscache entry is considered to be removed. -1 indicates that syscache
+ pruning is disabled at all. The value defaults to 600 seconds
+ (<literal>10 minutes</literal>). The syscache entries that are not
+ used for the duration can be removed to prevent syscache bloat. This
+ behavior is suppressed until the size of syscache exceeds
+ <xref linkend="guc-syscache-memory-target"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-max-stack-depth" xreflabel="max_stack_depth">
<term><varname>max_stack_depth</varname> (<type>integer</type>)
<indexterm>
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index 8e6aef332c..e4a4a5874c 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -732,6 +732,9 @@ void
SetCurrentStatementStartTimestamp(void)
{
stmtStartTimestamp = GetCurrentTimestamp();
+
+ /* Set this timestamp as aproximated current time */
+ SetCatCacheClock(stmtStartTimestamp);
}
/*
diff --git a/src/backend/utils/cache/catcache.c b/src/backend/utils/cache/catcache.c
index 5ddbf6eab1..9f421cd242 100644
--- a/src/backend/utils/cache/catcache.c
+++ b/src/backend/utils/cache/catcache.c
@@ -71,9 +71,24 @@
#define CACHE6_elog(a,b,c,d,e,f,g)
#endif
+/*
+ * GUC variable to define the minimum size of hash to cosider entry eviction.
+ * This variable is shared among various cache mechanisms.
+ */
+int cache_memory_target = 0;
+
+/* GUC variable to define the minimum age of entries that will be cosidered to
+ * be evicted in seconds. This variable is shared among various cache
+ * mechanisms.
+ */
+int cache_prune_min_age = 600;
+
/* Cache management header --- pointer is NULL until created */
static CatCacheHeader *CacheHdr = NULL;
+/* Timestamp used for any operation on caches. */
+TimestampTz catcacheclock = 0;
+
static inline HeapTuple SearchCatCacheInternal(CatCache *cache,
int nkeys,
Datum v1, Datum v2,
@@ -866,9 +881,130 @@ InitCatCache(int id,
*/
MemoryContextSwitchTo(oldcxt);
+ /* initilize catcache reference clock if haven't done yet */
+ if (catcacheclock == 0)
+ catcacheclock = GetCurrentTimestamp();
+
return cp;
}
+/*
+ * CatCacheCleanupOldEntries - Remove infrequently-used entries
+ *
+ * Catcache entries can be left alone for several reasons. We remove them if
+ * they are not accessed for a certain time to prevent catcache from
+ * bloating. The eviction is performed with the similar algorithm with buffer
+ * eviction using access counter. Entries that are accessed several times can
+ * live longer than those that have had no access in the same duration.
+ */
+static bool
+CatCacheCleanupOldEntries(CatCache *cp)
+{
+ int i;
+ int nremoved = 0;
+ size_t hash_size;
+#ifdef CATCACHE_STATS
+ /* These variables are only for debugging purpose */
+ int ntotal = 0;
+ /*
+ * nth element in nentries stores the number of cache entries that have
+ * lived unaccessed for corresponding multiple in ageclass of
+ * cache_prune_min_age. The index of nremoved_entry is the value of the
+ * clock-sweep counter, which takes from 0 up to 2.
+ */
+ double ageclass[] = {0.05, 0.1, 1.0, 2.0, 3.0, 0.0};
+ int nentries[] = {0, 0, 0, 0, 0, 0};
+ int nremoved_entry[3] = {0, 0, 0};
+ int j;
+#endif
+
+ /* Return immediately if no pruning is wanted */
+ if (cache_prune_min_age < 0)
+ return false;
+
+ /*
+ * Return without pruning if the size of the hash is below the target.
+ * Since the area for bucket array is dominant, consider only it.
+ */
+ hash_size = cp->cc_nbuckets * sizeof(dlist_head);
+ if (hash_size < (Size) cache_memory_target * 1024L)
+ return false;
+
+ /* Search the whole hash for entries to remove */
+ for (i = 0; i < cp->cc_nbuckets; i++)
+ {
+ dlist_mutable_iter iter;
+
+ dlist_foreach_modify(iter, &cp->cc_bucket[i])
+ {
+ CatCTup *ct = dlist_container(CatCTup, cache_elem, iter.cur);
+ long entry_age;
+ int us;
+
+
+ /*
+ * Calculate the duration from the time of the last access to the
+ * "current" time. Since catcacheclock is not advanced within a
+ * transaction, the entries that are accessed within the current
+ * transaction won't be pruned.
+ */
+ TimestampDifference(ct->lastaccess, catcacheclock, &entry_age, &us);
+
+#ifdef CATCACHE_STATS
+ /* count catcache entries for each age class */
+ ntotal++;
+ for (j = 0 ;
+ ageclass[j] != 0.0 &&
+ entry_age > cache_prune_min_age * ageclass[j] ;
+ j++);
+ if (ageclass[j] == 0.0) j--;
+ nentries[j]++;
+#endif
+
+ /*
+ * Try to remove entries older than cache_prune_min_age seconds.
+ * Entries that are not accessed after last pruning are removed in
+ * that seconds, and that has been accessed several times are
+ * removed after leaving alone for up to three times of the
+ * duration. We don't try shrink buckets since pruning effectively
+ * caps catcache expansion in the long term.
+ */
+ if (entry_age > cache_prune_min_age)
+ {
+#ifdef CATCACHE_STATS
+ Assert (ct->naccess >= 0 && ct->naccess <= 2);
+ nremoved_entry[ct->naccess]++;
+#endif
+ if (ct->naccess > 0)
+ ct->naccess--;
+ else
+ {
+ if (!ct->c_list || ct->c_list->refcount == 0)
+ {
+ CatCacheRemoveCTup(cp, ct);
+ nremoved++;
+ }
+ }
+ }
+ }
+ }
+
+#ifdef CATCACHE_STATS
+ ereport(DEBUG1,
+ (errmsg ("removed %d/%d, age(-%.0fs:%d, -%.0fs:%d, *-%.0fs:%d, -%.0fs:%d, -%.0fs:%d) naccessed(0:%d, 1:%d, 2:%d)",
+ nremoved, ntotal,
+ ageclass[0] * cache_prune_min_age, nentries[0],
+ ageclass[1] * cache_prune_min_age, nentries[1],
+ ageclass[2] * cache_prune_min_age, nentries[2],
+ ageclass[3] * cache_prune_min_age, nentries[3],
+ ageclass[4] * cache_prune_min_age, nentries[4],
+ nremoved_entry[0], nremoved_entry[1], nremoved_entry[2]),
+ errhidestmt(true)));
+#endif
+
+ return nremoved > 0;
+}
+
/*
* Enlarge a catcache, doubling the number of buckets.
*/
@@ -1282,6 +1418,11 @@ SearchCatCacheInternal(CatCache *cache,
*/
dlist_move_head(bucket, &ct->cache_elem);
+ /* Update access information for pruning */
+ if (ct->naccess < 2)
+ ct->naccess++;
+ ct->lastaccess = catcacheclock;
+
/*
* If it's a positive entry, bump its refcount and return it. If it's
* negative, we can report failure to the caller.
@@ -1813,7 +1954,6 @@ ReleaseCatCacheList(CatCList *list)
CatCacheRemoveCList(list->my_cache, list);
}
-
/*
* CatalogCacheCreateEntry
* Create a new CatCTup entry, copying the given HeapTuple and other
@@ -1906,6 +2046,8 @@ CatalogCacheCreateEntry(CatCache *cache, HeapTuple ntp, Datum *arguments,
ct->dead = false;
ct->negative = negative;
ct->hash_value = hashValue;
+ ct->naccess = 0;
+ ct->lastaccess = catcacheclock;
dlist_push_head(&cache->cc_bucket[hashIndex], &ct->cache_elem);
@@ -1913,10 +2055,13 @@ CatalogCacheCreateEntry(CatCache *cache, HeapTuple ntp, Datum *arguments,
CacheHdr->ch_ntup++;
/*
- * If the hash table has become too full, enlarge the buckets array. Quite
- * arbitrarily, we enlarge when fill factor > 2.
+ * If the hash table has become too full, try cleanup by removing
+ * infrequently used entries to make a room for the new entry. If it
+ * failed, enlarge the bucket array instead. Quite arbitrarily, we try
+ * this when fill factor > 2.
*/
- if (cache->cc_ntup > cache->cc_nbuckets * 2)
+ if (cache->cc_ntup > cache->cc_nbuckets * 2 &&
+ !CatCacheCleanupOldEntries(cache))
RehashCatCache(cache);
return ct;
diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/plancache.c
index 0ad3e3c736..701ead152c 100644
--- a/src/backend/utils/cache/plancache.c
+++ b/src/backend/utils/cache/plancache.c
@@ -63,12 +63,14 @@
#include "storage/lmgr.h"
#include "tcop/pquery.h"
#include "tcop/utility.h"
+#include "utils/catcache.h"
#include "utils/inval.h"
#include "utils/memutils.h"
#include "utils/resowner_private.h"
#include "utils/rls.h"
#include "utils/snapmgr.h"
#include "utils/syscache.h"
+#include "utils/timestamp.h"
/*
@@ -86,6 +88,12 @@
* guarantee to save a CachedPlanSource without error.
*/
static CachedPlanSource *first_saved_plan = NULL;
+static CachedPlanSource *last_saved_plan = NULL;
+static int num_saved_plans = 0;
+static TimestampTz oldest_saved_plan = 0;
+
+/* GUC variables */
+int min_cached_plans = 1000;
static void ReleaseGenericPlan(CachedPlanSource *plansource);
static List *RevalidateCachedQuery(CachedPlanSource *plansource,
@@ -105,6 +113,7 @@ static TupleDesc PlanCacheComputeResultDesc(List *stmt_list);
static void PlanCacheRelCallback(Datum arg, Oid relid);
static void PlanCacheFuncCallback(Datum arg, int cacheid, uint32 hashvalue);
static void PlanCacheSysCallback(Datum arg, int cacheid, uint32 hashvalue);
+static void PruneCachedPlan(void);
/*
@@ -208,6 +217,8 @@ CreateCachedPlan(RawStmt *raw_parse_tree,
plansource->generic_cost = -1;
plansource->total_custom_cost = 0;
plansource->num_custom_plans = 0;
+ plansource->last_access = GetCatCacheClock();
+
MemoryContextSwitchTo(oldcxt);
@@ -423,6 +434,28 @@ CompleteCachedPlan(CachedPlanSource *plansource,
plansource->is_valid = true;
}
+/* moves the plansource to the first in the list */
+static inline void
+MovePlansourceToFirst(CachedPlanSource *plansource)
+{
+ if (first_saved_plan != plansource)
+ {
+ /* delink this element */
+ if (plansource->next_saved)
+ plansource->next_saved->prev_saved = plansource->prev_saved;
+ if (plansource->prev_saved)
+ plansource->prev_saved->next_saved = plansource->next_saved;
+ if (last_saved_plan == plansource)
+ last_saved_plan = plansource->prev_saved;
+
+ /* insert at the beginning */
+ first_saved_plan->prev_saved = plansource;
+ plansource->next_saved = first_saved_plan;
+ plansource->prev_saved = NULL;
+ first_saved_plan = plansource;
+ }
+}
+
/*
* SaveCachedPlan: save a cached plan permanently
*
@@ -470,6 +503,11 @@ SaveCachedPlan(CachedPlanSource *plansource)
* Add the entry to the global list of cached plans.
*/
plansource->next_saved = first_saved_plan;
+ if (first_saved_plan)
+ first_saved_plan->prev_saved = plansource;
+ else
+ last_saved_plan = plansource;
+ plansource->prev_saved = NULL;
first_saved_plan = plansource;
plansource->is_saved = true;
@@ -492,7 +530,11 @@ DropCachedPlan(CachedPlanSource *plansource)
if (plansource->is_saved)
{
if (first_saved_plan == plansource)
+ {
first_saved_plan = plansource->next_saved;
+ if (first_saved_plan)
+ first_saved_plan->prev_saved = NULL;
+ }
else
{
CachedPlanSource *psrc;
@@ -502,10 +544,19 @@ DropCachedPlan(CachedPlanSource *plansource)
if (psrc->next_saved == plansource)
{
psrc->next_saved = plansource->next_saved;
+ if (psrc->next_saved)
+ psrc->next_saved->prev_saved = psrc;
break;
}
}
}
+
+ if (last_saved_plan == plansource)
+ {
+ last_saved_plan = plansource->prev_saved;
+ if (last_saved_plan)
+ last_saved_plan->next_saved = NULL;
+ }
plansource->is_saved = false;
}
@@ -537,6 +588,13 @@ ReleaseGenericPlan(CachedPlanSource *plansource)
Assert(plan->magic == CACHEDPLAN_MAGIC);
plansource->gplan = NULL;
ReleaseCachedPlan(plan, false);
+
+ /* decrement "saved plans" counter */
+ if (plansource->is_saved)
+ {
+ Assert (num_saved_plans > 0);
+ num_saved_plans--;
+ }
}
}
@@ -1148,6 +1206,17 @@ GetCachedPlan(CachedPlanSource *plansource, ParamListInfo boundParams,
if (useResOwner && !plansource->is_saved)
elog(ERROR, "cannot apply ResourceOwner to non-saved cached plan");
+ /*
+ * set last-accessed timestamp and move this plan to the first of the list
+ */
+ if (plansource->is_saved)
+ {
+ plansource->last_access = GetCatCacheClock();
+
+ /* move this plan to the first of the list */
+ MovePlansourceToFirst(plansource);
+ }
+
/* Make sure the querytree list is valid and we have parse-time locks */
qlist = RevalidateCachedQuery(plansource, queryEnv);
@@ -1156,6 +1225,11 @@ GetCachedPlan(CachedPlanSource *plansource, ParamListInfo boundParams,
if (!customplan)
{
+ /* Prune cached plans if needed */
+ if (plansource->is_saved &&
+ min_cached_plans >= 0 && num_saved_plans > min_cached_plans)
+ PruneCachedPlan();
+
if (CheckCachedPlan(plansource))
{
/* We want a generic plan, and we already have a valid one */
@@ -1168,6 +1242,11 @@ GetCachedPlan(CachedPlanSource *plansource, ParamListInfo boundParams,
plan = BuildCachedPlan(plansource, qlist, NULL, queryEnv);
/* Just make real sure plansource->gplan is clear */
ReleaseGenericPlan(plansource);
+
+ /* count this new saved plan */
+ if (plansource->is_saved)
+ num_saved_plans++;
+
/* Link the new generic plan into the plansource */
plansource->gplan = plan;
plan->refcount++;
@@ -1856,6 +1935,90 @@ PlanCacheSysCallback(Datum arg, int cacheid, uint32 hashvalue)
ResetPlanCache();
}
+/*
+ * PrunePlanCache: removes generic plan of "old" saved plans.
+ */
+static void
+PruneCachedPlan(void)
+{
+ CachedPlanSource *plansource;
+ TimestampTz currclock = GetCatCacheClock();
+ long age;
+ int us;
+ int nremoved = 0;
+
+ /* do nothing if not wanted */
+ if (cache_prune_min_age < 0 || num_saved_plans <= min_cached_plans)
+ return;
+
+ /* Fast check for oldest cache */
+ if (oldest_saved_plan > 0)
+ {
+ TimestampDifference(oldest_saved_plan, currclock, &age, &us);
+ if (age < cache_prune_min_age)
+ return;
+ }
+
+ /* last plan is the oldest. */
+ for (plansource = last_saved_plan; plansource; plansource = plansource->prev_saved)
+ {
+ long plan_age;
+ int us;
+
+ Assert(plansource->magic == CACHEDPLANSOURCE_MAGIC);
+
+ /* we want to prune no more plans */
+ if (num_saved_plans <= min_cached_plans)
+ break;
+
+ /*
+ * No work if it already doesn't have gplan and move it to the
+ * beginning so that we don't see it at the next time
+ */
+ if (!plansource->gplan)
+ continue;
+
+ /*
+ * Check age for pruning. Can exit immediately when finding a
+ * not-older element.
+ */
+ TimestampDifference(plansource->last_access, currclock, &plan_age, &us);
+ if (plan_age <= cache_prune_min_age)
+ {
+ /* this entry is the next oldest */
+ oldest_saved_plan = plansource->last_access;
+ break;
+ }
+
+ /*
+ * Here, remove generic plans of this plansrouceif it is not actually
+ * used and move it to the beginning of the list. Just update
+ * last_access and move it to the beginning if the plan is used.
+ */
+ if (plansource->gplan->refcount <= 1)
+ {
+ ReleaseGenericPlan(plansource);
+ nremoved++;
+ }
+
+ plansource->last_access = currclock;
+ }
+
+ /* move the "removed" plansrouces altogehter to the beginning of the list */
+ if (plansource != last_saved_plan && plansource)
+ {
+ plansource->next_saved->prev_saved = NULL;
+ first_saved_plan->prev_saved = last_saved_plan;
+ last_saved_plan->next_saved = first_saved_plan;
+ first_saved_plan = plansource->next_saved;
+ plansource->next_saved = NULL;
+ last_saved_plan = plansource;
+ }
+
+ if (nremoved > 0)
+ elog(DEBUG1, "plancache removed %d/%d", nremoved, num_saved_plans);
+}
+
/*
* ResetPlanCache: invalidate all cached plans.
*/
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 859ef931e7..774a87ed2c 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -79,6 +79,7 @@
#include "tsearch/ts_cache.h"
#include "utils/builtins.h"
#include "utils/bytea.h"
+#include "utils/catcache.h"
#include "utils/guc_tables.h"
#include "utils/memutils.h"
#include "utils/pg_locale.h"
@@ -2105,6 +2106,38 @@ static struct config_int ConfigureNamesInt[] =
NULL, NULL, NULL
},
+ {
+ {"cache_memory_target", PGC_USERSET, RESOURCES_MEM,
+ gettext_noop("Sets the minimum syscache size to keep."),
+ gettext_noop("Cache is not pruned before exceeding this size."),
+ GUC_UNIT_KB
+ },
+ &cache_memory_target,
+ 0, 0, MAX_KILOBYTES,
+ NULL, NULL, NULL
+ },
+
+ {
+ {"cache_prune_min_age", PGC_USERSET, RESOURCES_MEM,
+ gettext_noop("Sets the minimum unused duration of cache entries before removal."),
+ gettext_noop("Cache entries that live unused for longer than this seconds are considered to be removed."),
+ GUC_UNIT_S
+ },
+ &cache_prune_min_age,
+ 600, -1, INT_MAX,
+ NULL, NULL, NULL
+ },
+
+ {
+ {"min_cached_plans", PGC_USERSET, RESOURCES_MEM,
+ gettext_noop("Sets the minimum number of cached plans kept on memory."),
+ gettext_noop("Timeout invalidation of plancache is not activated until the number of plancaches reaches this value. -1 means timeout invalidation is always active.")
+ },
+ &min_cached_plans,
+ 1000, -1, INT_MAX,
+ NULL, NULL, NULL
+ },
+
/*
* We use the hopefully-safely-small value of 100kB as the compiled-in
* default for max_stack_depth. InitializeGUCOptions will increase it if
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 9e39baf466..3f2760ef9d 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -126,6 +126,8 @@
#work_mem = 4MB # min 64kB
#maintenance_work_mem = 64MB # min 1MB
#autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem
+#cache_memory_target = 0kB # in kB
+#cache_prune_min_age = 600s # -1 disables pruning
#max_stack_depth = 2MB # min 100kB
#dynamic_shared_memory_type = posix # the default is the first option
# supported by the operating system:
diff --git a/src/include/utils/catcache.h b/src/include/utils/catcache.h
index 7b22f9c7bc..599303be56 100644
--- a/src/include/utils/catcache.h
+++ b/src/include/utils/catcache.h
@@ -22,6 +22,7 @@
#include "access/htup.h"
#include "access/skey.h"
+#include "datatype/timestamp.h"
#include "lib/ilist.h"
#include "utils/relcache.h"
@@ -119,6 +120,8 @@ typedef struct catctup
bool dead; /* dead but not yet removed? */
bool negative; /* negative cache entry? */
HeapTupleData tuple; /* tuple management header */
+ int naccess; /* # of access to this entry, up to 2 */
+ TimestampTz lastaccess; /* approx. timestamp of the last usage */
/*
* The tuple may also be a member of at most one CatCList. (If a single
@@ -189,6 +192,22 @@ typedef struct catcacheheader
/* this extern duplicates utils/memutils.h... */
extern PGDLLIMPORT MemoryContext CacheMemoryContext;
+/* for guc.c, not PGDLLPMPORT'ed */
+extern int cache_prune_min_age;
+extern int cache_memory_target;
+
+/* to use as access timestamp of catcache entries */
+extern TimestampTz catcacheclock;
+
+/*
+ * SetCatCacheClock - set timestamp for catcache access record
+ */
+static inline void
+SetCatCacheClock(TimestampTz ts)
+{
+ catcacheclock = ts;
+}
+
extern void CreateCacheMemoryContext(void);
extern CatCache *InitCatCache(int id, Oid reloid, Oid indexoid,
diff --git a/src/include/utils/plancache.h b/src/include/utils/plancache.h
index ab20aa04b0..f3c5b2010d 100644
--- a/src/include/utils/plancache.h
+++ b/src/include/utils/plancache.h
@@ -110,11 +110,13 @@ typedef struct CachedPlanSource
bool is_valid; /* is the query_list currently valid? */
int generation; /* increments each time we create a plan */
/* If CachedPlanSource has been saved, it is a member of a global list */
- struct CachedPlanSource *next_saved; /* list link, if so */
+ struct CachedPlanSource *prev_saved; /* list prev link, if so */
+ struct CachedPlanSource *next_saved; /* list next link, if so */
/* State kept to help decide whether to use custom or generic plans: */
double generic_cost; /* cost of generic plan, or -1 if not known */
double total_custom_cost; /* total cost of custom plans so far */
int num_custom_plans; /* number of plans included in total */
+ TimestampTz last_access; /* timestamp of the last usage */
} CachedPlanSource;
/*
@@ -143,6 +145,9 @@ typedef struct CachedPlan
MemoryContext context; /* context containing this CachedPlan */
} CachedPlan;
+/* GUC variables */
+extern int min_cached_plans;
+extern int plancache_prune_min_age;
extern void InitPlanCache(void);
extern void ResetPlanCache(void);
--
2.16.3
----Next_Part(Tue_Jun_26_18_00_03_2018_442)----
^ permalink raw reply [nested|flat] 6+ messages in thread
* [PATCH 1/3] Remove entries that haven't been used for a certain time
@ 2018-10-16 04:04 Kyotaro Horiguchi <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Kyotaro Horiguchi @ 2018-10-16 04:04 UTC (permalink / raw)
Catcache entries can be left alone for several reasons. It is not
desirable that they eat up memory. With this patch, This adds
consideration of removal of entries that haven't been used for a
certain time before enlarging the hash array.
---
doc/src/sgml/config.sgml | 38 ++++++
src/backend/access/transam/xact.c | 5 +
src/backend/utils/cache/catcache.c | 166 ++++++++++++++++++++++++--
src/backend/utils/misc/guc.c | 23 ++++
src/backend/utils/misc/postgresql.conf.sample | 2 +
src/include/utils/catcache.h | 28 ++++-
6 files changed, 254 insertions(+), 8 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 7554cba3f9..c3133d742b 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1618,6 +1618,44 @@ include_dir 'conf.d'
</listitem>
</varlistentry>
+ <varlistentry id="guc-syscache-memory-target" xreflabel="syscache_memory_target">
+ <term><varname>syscache_memory_target</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>syscache_memory_target</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the maximum amount of memory to which syscache is expanded
+ without pruning. The value defaults to 0, indicating that pruning is
+ always considered. After exceeding this size, syscache pruning is
+ considered according to
+ <xref linkend="guc-syscache-prune-min-age"/>. If you need to keep
+ certain amount of syscache entries with intermittent usage, try
+ increase this setting.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-syscache-prune-min-age" xreflabel="syscache_prune_min_age">
+ <term><varname>syscache_prune_min_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>syscache_prune_min_age</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the minimum amount of unused time in seconds at which a
+ syscache entry is considered to be removed. -1 indicates that syscache
+ pruning is disabled at all. The value defaults to 600 seconds
+ (<literal>10 minutes</literal>). The syscache entries that are not
+ used for the duration can be removed to prevent syscache bloat. This
+ behavior is suppressed until the size of syscache exceeds
+ <xref linkend="guc-syscache-memory-target"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-max-stack-depth" xreflabel="max_stack_depth">
<term><varname>max_stack_depth</varname> (<type>integer</type>)
<indexterm>
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index 8c1621d949..083b6dc7aa 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -733,7 +733,12 @@ void
SetCurrentStatementStartTimestamp(void)
{
if (!IsParallelWorker())
+ {
stmtStartTimestamp = GetCurrentTimestamp();
+
+ /* Set this timestamp as aproximated current time */
+ SetCatCacheClock(stmtStartTimestamp);
+ }
else
Assert(stmtStartTimestamp != 0);
}
diff --git a/src/backend/utils/cache/catcache.c b/src/backend/utils/cache/catcache.c
index 5ddbf6eab1..9be463311d 100644
--- a/src/backend/utils/cache/catcache.c
+++ b/src/backend/utils/cache/catcache.c
@@ -71,9 +71,24 @@
#define CACHE6_elog(a,b,c,d,e,f,g)
#endif
+/*
+ * GUC variable to define the minimum size of hash to cosider entry eviction.
+ * This variable is shared among various cache mechanisms.
+ */
+int cache_memory_target = 0;
+
+/* GUC variable to define the minimum age of entries that will be cosidered to
+ * be evicted in seconds. This variable is shared among various cache
+ * mechanisms.
+ */
+int cache_prune_min_age = 600;
+
/* Cache management header --- pointer is NULL until created */
static CatCacheHeader *CacheHdr = NULL;
+/* Timestamp used for any operation on caches. */
+TimestampTz catcacheclock = 0;
+
static inline HeapTuple SearchCatCacheInternal(CatCache *cache,
int nkeys,
Datum v1, Datum v2,
@@ -498,6 +513,7 @@ CatCacheRemoveCTup(CatCache *cache, CatCTup *ct)
CatCacheFreeKeys(cache->cc_tupdesc, cache->cc_nkeys,
cache->cc_keyno, ct->keys);
+ cache->cc_tupsize -= ct->size;
pfree(ct);
--cache->cc_ntup;
@@ -849,6 +865,7 @@ InitCatCache(int id,
cp->cc_nkeys = nkeys;
for (i = 0; i < nkeys; ++i)
cp->cc_keyno[i] = key[i];
+ cp->cc_tupsize = 0;
/*
* new cache is initialized as far as we can go for now. print some
@@ -866,9 +883,129 @@ InitCatCache(int id,
*/
MemoryContextSwitchTo(oldcxt);
+ /* initilize catcache reference clock if haven't done yet */
+ if (catcacheclock == 0)
+ catcacheclock = GetCurrentTimestamp();
+
return cp;
}
+/*
+ * CatCacheCleanupOldEntries - Remove infrequently-used entries
+ *
+ * Catcache entries can be left alone for several reasons. We remove them if
+ * they are not accessed for a certain time to prevent catcache from
+ * bloating. The eviction is performed with the similar algorithm with buffer
+ * eviction using access counter. Entries that are accessed several times can
+ * live longer than those that have had no access in the same duration.
+ */
+static bool
+CatCacheCleanupOldEntries(CatCache *cp)
+{
+ int i;
+ int nremoved = 0;
+ size_t hash_size;
+#ifdef CATCACHE_STATS
+ /* These variables are only for debugging purpose */
+ int ntotal = 0;
+ /*
+ * nth element in nentries stores the number of cache entries that have
+ * lived unaccessed for corresponding multiple in ageclass of
+ * cache_prune_min_age. The index of nremoved_entry is the value of the
+ * clock-sweep counter, which takes from 0 up to 2.
+ */
+ double ageclass[] = {0.05, 0.1, 1.0, 2.0, 3.0, 0.0};
+ int nentries[] = {0, 0, 0, 0, 0, 0};
+ int nremoved_entry[3] = {0, 0, 0};
+ int j;
+#endif
+
+ /* Return immediately if no pruning is wanted */
+ if (cache_prune_min_age < 0)
+ return false;
+
+ /*
+ * Return without pruning if the size of the hash is below the target.
+ */
+ hash_size = cp->cc_nbuckets * sizeof(dlist_head);
+ if (hash_size + cp->cc_tupsize < (Size) cache_memory_target * 1024L)
+ return false;
+
+ /* Search the whole hash for entries to remove */
+ for (i = 0; i < cp->cc_nbuckets; i++)
+ {
+ dlist_mutable_iter iter;
+
+ dlist_foreach_modify(iter, &cp->cc_bucket[i])
+ {
+ CatCTup *ct = dlist_container(CatCTup, cache_elem, iter.cur);
+ long entry_age;
+ int us;
+
+
+ /*
+ * Calculate the duration from the time of the last access to the
+ * "current" time. Since catcacheclock is not advanced within a
+ * transaction, the entries that are accessed within the current
+ * transaction won't be pruned.
+ */
+ TimestampDifference(ct->lastaccess, catcacheclock, &entry_age, &us);
+
+#ifdef CATCACHE_STATS
+ /* count catcache entries for each age class */
+ ntotal++;
+ for (j = 0 ;
+ ageclass[j] != 0.0 &&
+ entry_age > cache_prune_min_age * ageclass[j] ;
+ j++);
+ if (ageclass[j] == 0.0) j--;
+ nentries[j]++;
+#endif
+
+ /*
+ * Try to remove entries older than cache_prune_min_age seconds.
+ * Entries that are not accessed after last pruning are removed in
+ * that seconds, and that has been accessed several times are
+ * removed after leaving alone for up to three times of the
+ * duration. We don't try shrink buckets since pruning effectively
+ * caps catcache expansion in the long term.
+ */
+ if (entry_age > cache_prune_min_age)
+ {
+#ifdef CATCACHE_STATS
+ Assert (ct->naccess >= 0 && ct->naccess <= 2);
+ nremoved_entry[ct->naccess]++;
+#endif
+ if (ct->naccess > 0)
+ ct->naccess--;
+ else
+ {
+ if (!ct->c_list || ct->c_list->refcount == 0)
+ {
+ CatCacheRemoveCTup(cp, ct);
+ nremoved++;
+ }
+ }
+ }
+ }
+ }
+
+#ifdef CATCACHE_STATS
+ ereport(DEBUG1,
+ (errmsg ("removed %d/%d, age(-%.0fs:%d, -%.0fs:%d, *-%.0fs:%d, -%.0fs:%d, -%.0fs:%d) naccessed(0:%d, 1:%d, 2:%d)",
+ nremoved, ntotal,
+ ageclass[0] * cache_prune_min_age, nentries[0],
+ ageclass[1] * cache_prune_min_age, nentries[1],
+ ageclass[2] * cache_prune_min_age, nentries[2],
+ ageclass[3] * cache_prune_min_age, nentries[3],
+ ageclass[4] * cache_prune_min_age, nentries[4],
+ nremoved_entry[0], nremoved_entry[1], nremoved_entry[2]),
+ errhidestmt(true)));
+#endif
+
+ return nremoved > 0;
+}
+
/*
* Enlarge a catcache, doubling the number of buckets.
*/
@@ -1282,6 +1419,11 @@ SearchCatCacheInternal(CatCache *cache,
*/
dlist_move_head(bucket, &ct->cache_elem);
+ /* Update access information for pruning */
+ if (ct->naccess < 2)
+ ct->naccess++;
+ ct->lastaccess = catcacheclock;
+
/*
* If it's a positive entry, bump its refcount and return it. If it's
* negative, we can report failure to the caller.
@@ -1827,11 +1969,13 @@ CatalogCacheCreateEntry(CatCache *cache, HeapTuple ntp, Datum *arguments,
CatCTup *ct;
HeapTuple dtp;
MemoryContext oldcxt;
+ int tupsize = 0;
/* negative entries have no tuple associated */
if (ntp)
{
int i;
+ int tupsize;
Assert(!negative);
@@ -1850,13 +1994,14 @@ CatalogCacheCreateEntry(CatCache *cache, HeapTuple ntp, Datum *arguments,
/* Allocate memory for CatCTup and the cached tuple in one go */
oldcxt = MemoryContextSwitchTo(CacheMemoryContext);
- ct = (CatCTup *) palloc(sizeof(CatCTup) +
- MAXIMUM_ALIGNOF + dtp->t_len);
+ tupsize = sizeof(CatCTup) + MAXIMUM_ALIGNOF + dtp->t_len;
+ ct = (CatCTup *) palloc(tupsize);
ct->tuple.t_len = dtp->t_len;
ct->tuple.t_self = dtp->t_self;
ct->tuple.t_tableOid = dtp->t_tableOid;
ct->tuple.t_data = (HeapTupleHeader)
MAXALIGN(((char *) ct) + sizeof(CatCTup));
+ ct->size = tupsize;
/* copy tuple contents */
memcpy((char *) ct->tuple.t_data,
(const char *) dtp->t_data,
@@ -1884,8 +2029,8 @@ CatalogCacheCreateEntry(CatCache *cache, HeapTuple ntp, Datum *arguments,
{
Assert(negative);
oldcxt = MemoryContextSwitchTo(CacheMemoryContext);
- ct = (CatCTup *) palloc(sizeof(CatCTup));
-
+ tupsize = sizeof(CatCTup);
+ ct = (CatCTup *) palloc(tupsize);
/*
* Store keys - they'll point into separately allocated memory if not
* by-value.
@@ -1906,17 +2051,24 @@ CatalogCacheCreateEntry(CatCache *cache, HeapTuple ntp, Datum *arguments,
ct->dead = false;
ct->negative = negative;
ct->hash_value = hashValue;
+ ct->naccess = 0;
+ ct->lastaccess = catcacheclock;
+ ct->size = tupsize;
dlist_push_head(&cache->cc_bucket[hashIndex], &ct->cache_elem);
cache->cc_ntup++;
CacheHdr->ch_ntup++;
+ cache->cc_tupsize += tupsize;
/*
- * If the hash table has become too full, enlarge the buckets array. Quite
- * arbitrarily, we enlarge when fill factor > 2.
+ * If the hash table has become too full, try cleanup by removing
+ * infrequently used entries to make a room for the new entry. If it
+ * failed, enlarge the bucket array instead. Quite arbitrarily, we try
+ * this when fill factor > 2.
*/
- if (cache->cc_ntup > cache->cc_nbuckets * 2)
+ if (cache->cc_ntup > cache->cc_nbuckets * 2 &&
+ !CatCacheCleanupOldEntries(cache))
RehashCatCache(cache);
return ct;
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 2317e8be6b..1a49d576fa 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -80,6 +80,7 @@
#include "tsearch/ts_cache.h"
#include "utils/builtins.h"
#include "utils/bytea.h"
+#include "utils/catcache.h"
#include "utils/guc_tables.h"
#include "utils/float.h"
#include "utils/memutils.h"
@@ -2113,6 +2114,28 @@ static struct config_int ConfigureNamesInt[] =
NULL, NULL, NULL
},
+ {
+ {"cache_memory_target", PGC_USERSET, RESOURCES_MEM,
+ gettext_noop("Sets the minimum syscache size to keep."),
+ gettext_noop("Cache is not pruned before exceeding this size."),
+ GUC_UNIT_KB
+ },
+ &cache_memory_target,
+ 0, 0, MAX_KILOBYTES,
+ NULL, NULL, NULL
+ },
+
+ {
+ {"cache_prune_min_age", PGC_USERSET, RESOURCES_MEM,
+ gettext_noop("Sets the minimum unused duration of cache entries before removal."),
+ gettext_noop("Cache entries that live unused for longer than this seconds are considered to be removed."),
+ GUC_UNIT_S
+ },
+ &cache_prune_min_age,
+ 600, -1, INT_MAX,
+ NULL, NULL, NULL
+ },
+
/*
* We use the hopefully-safely-small value of 100kB as the compiled-in
* default for max_stack_depth. InitializeGUCOptions will increase it if
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 4e61bc6521..c59dd898ac 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -126,6 +126,8 @@
#work_mem = 4MB # min 64kB
#maintenance_work_mem = 64MB # min 1MB
#autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem
+#cache_memory_target = 0kB # in kB
+#cache_prune_min_age = 600s # -1 disables pruning
#max_stack_depth = 2MB # min 100kB
#dynamic_shared_memory_type = posix # the default is the first option
# supported by the operating system:
diff --git a/src/include/utils/catcache.h b/src/include/utils/catcache.h
index 7b22f9c7bc..ace4178619 100644
--- a/src/include/utils/catcache.h
+++ b/src/include/utils/catcache.h
@@ -22,6 +22,7 @@
#include "access/htup.h"
#include "access/skey.h"
+#include "datatype/timestamp.h"
#include "lib/ilist.h"
#include "utils/relcache.h"
@@ -61,6 +62,7 @@ typedef struct catcache
slist_node cc_next; /* list link */
ScanKeyData cc_skey[CATCACHE_MAXKEYS]; /* precomputed key info for heap
* scans */
+ int cc_tupsize; /* total amount of catcache tuples */
/*
* Keep these at the end, so that compiling catcache.c with CATCACHE_STATS
@@ -119,7 +121,9 @@ typedef struct catctup
bool dead; /* dead but not yet removed? */
bool negative; /* negative cache entry? */
HeapTupleData tuple; /* tuple management header */
-
+ int naccess; /* # of access to this entry, up to 2 */
+ TimestampTz lastaccess; /* approx. timestamp of the last usage */
+ int size; /* palloc'ed size off this tuple */
/*
* The tuple may also be a member of at most one CatCList. (If a single
* catcache is list-searched with varying numbers of keys, we may have to
@@ -189,6 +193,28 @@ typedef struct catcacheheader
/* this extern duplicates utils/memutils.h... */
extern PGDLLIMPORT MemoryContext CacheMemoryContext;
+/* for guc.c, not PGDLLPMPORT'ed */
+extern int cache_prune_min_age;
+extern int cache_memory_target;
+
+/* to use as access timestamp of catcache entries */
+extern TimestampTz catcacheclock;
+
+/*
+ * SetCatCacheClock - set timestamp for catcache access record
+ */
+static inline void
+SetCatCacheClock(TimestampTz ts)
+{
+ catcacheclock = ts;
+}
+
+static inline TimestampTz
+GetCatCacheClock(void)
+{
+ return catcacheclock;
+}
+
extern void CreateCacheMemoryContext(void);
extern CatCache *InitCatCache(int id, Oid reloid, Oid indexoid,
--
2.16.3
----Next_Part(Fri_Oct_19_17_34_57_2018_852)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="0002-Syscache-usage-tracking-feature.patch"
^ permalink raw reply [nested|flat] 6+ messages in thread
* [PATCH v20 3/5] rename archive-modules.sgml to archive-and-restore-modules.sgml
@ 2023-02-15 22:45 Nathan Bossart <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Nathan Bossart @ 2023-02-15 22:45 UTC (permalink / raw)
---
.../{archive-modules.sgml => archive-and-restore-modules.sgml} | 0
doc/src/sgml/filelist.sgml | 2 +-
doc/src/sgml/postgres.sgml | 2 +-
3 files changed, 2 insertions(+), 2 deletions(-)
rename doc/src/sgml/{archive-modules.sgml => archive-and-restore-modules.sgml} (100%)
diff --git a/doc/src/sgml/archive-modules.sgml b/doc/src/sgml/archive-and-restore-modules.sgml
similarity index 100%
rename from doc/src/sgml/archive-modules.sgml
rename to doc/src/sgml/archive-and-restore-modules.sgml
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index e0dca81cb2..31a57943f7 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -101,7 +101,7 @@
<!ENTITY custom-scan SYSTEM "custom-scan.sgml">
<!ENTITY logicaldecoding SYSTEM "logicaldecoding.sgml">
<!ENTITY replication-origins SYSTEM "replication-origins.sgml">
-<!ENTITY archive-modules SYSTEM "archive-modules.sgml">
+<!ENTITY archive-and-restore-modules SYSTEM "archive-and-restore-modules.sgml">
<!ENTITY protocol SYSTEM "protocol.sgml">
<!ENTITY sources SYSTEM "sources.sgml">
<!ENTITY storage SYSTEM "storage.sgml">
diff --git a/doc/src/sgml/postgres.sgml b/doc/src/sgml/postgres.sgml
index 2c107199d3..23522ccaf3 100644
--- a/doc/src/sgml/postgres.sgml
+++ b/doc/src/sgml/postgres.sgml
@@ -228,7 +228,7 @@ break is not needed in a wider output rendering.
&bgworker;
&logicaldecoding;
&replication-origins;
- &archive-modules;
+ &archive-and-restore-modules;
</part>
--
2.25.1
--3V7upXqbjpZ4EhLz
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v20-0004-restructure-archive-modules-docs-in-preparation-.patch"
^ permalink raw reply [nested|flat] 6+ messages in thread
* Proposal for Resumable Vacuum (again ...)
@ 2024-03-24 04:27 Jay <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Jay @ 2024-03-24 04:27 UTC (permalink / raw)
To: pgsql-hackers
Hi,
I am aware of few previous attempts and discussions on this topic
(eventually shelved or didn't materialize):
- https://www.postgresql.org/message-id/[email protected]
-
https://www.postgresql.org/message-id/CA%2BTgmoZgapzekbTqdBrcH8O8Yifi10_nB7uWLB8ajAhGL21M6A%40mail.g...
-
https://www.postgresql.org/message-id/flat/CAD21AoBqfMVWdk7Odh4A4OpF-m5GytRjXME5E8cEGXvhSJb8zw@mail....
And still I want to revise this topic for the obvious benefits.
I do not have any patch or code changes ready. The changes could be tricky
and might need efforts, possibly some re-factoring. Hence, before starting
the effort, I would like to get the proposal reviewed and consensus built
to avoid redundancy of efforts.
*Why do we need it? *
Since more and more large businesses are on-boarding PostgreSQL, it is only
fair that we make the essential utilities like vacuum more manageable and
scalable. The data sizes are definitely going to increase and maintenance
windows will reduce with businesses operating across the time zones and
24x7. Making the database more manageable with the least overhead is going
to be definitely a pressing need.
To avoid the repetition and duplicate efforts, I have picked up the snippet
below from the previous email conversation on the community (Ref:
https://www.postgresql.org/message-id/[email protected])
<Quote>
*For a large table, although it can be vacuumed by enabling vacuum
cost-based delay, the processing may last for several days (maybe hours).
It definitely has a negative effect on system performance. So if systems
which have maintenance time, it is preferred to vacuum in the maintenance
window. Vacuum tasks can be split into small subtasks, and they can be
scheduled into maintenance window time slots. This can reduce the impact of
vacuum to system service.*
*But currently vacuum tasks can not be split: if an interrupt or error
occurs during vacuum processing, vacuum totally forgets what it has done
and terminates itself. Following vacuum on the same table has to scan from
the beginning of the heap block. This proposal enable vacuum has capability
to stop and resume.*
</Quote>
*External Interface*
This feature is especially useful when the size of table/s is quite large
and their bloat is quite high and it is expected vacuum runs will take long
time.
Ref: https://www.postgresql.org/docs/current/sql-vacuum.html
vacuum [ ( *option* [, ...], *[{ for time = hh:mm}| {resume [for time =
hh:mm]}] *) ] [ *table_and_columns* [, ...] ]
The additional options give flexibility to run the vacuum for a limited
time and stop or resume the vacuum from the last time when it was stopped
for a given time.
When vacuum is invoked with ‘for time ...’ option it will store the
intermediate state of the dead tuples accumulated periodically on the disk
as it progresses. It will run for a specified time and stop after that
duration.
When vacuum is invoked with ‘for time ...’ option and is stopped
automatically after the specified time or interrupted manually and if it is
invoked next time with ‘resume’ option, it will try to check the stored
state of the last run and try to start as closely as possible from where it
left last time and avoid repetition of work.
When resumed, it can either run for a specified time again (if the duration
is specified) or run till completion if the duration is not specified.
When vacuum is invoked with ‘resume for’ option when there was no earlier
incomplete run or an earlier run with ‘for time’ option, the ‘for resume’
option will be ignored with a message in the errorlog.
When vacuum is invoked without ‘for time’ or ‘resume for’ options after
preceding incomplete runs with those options , then the persisted data from
the previous runs is discarded and deleted. This is important because
successive runs with ‘for time’ or ‘resume for’ assume the persisted data
is valid and there’s no run in between to invalidate it and the state of
heap pages in terms of vacuum is the same for the given saved vacuum
horizon.
In further discussion in the rest of this proposal, we will refer to vacuum
invoked with ‘for time’ or ‘resume for’ option as listed above as
‘resumable vacuum’.
Internal Changes (High level)For each table, vacuum progresses in the
following steps or phases (taken from the documentation)
https://www.postgresql.org/docs/current/progress-reporting.html#VACUUM-PHASES
<https://www.postgresql.org/docs/current/progress-reporting.html; :
1. Initializing - VACUUM is preparing to begin scanning the heap. This
phase is expected to be very brief.
2. Scanning heap - VACUUM is currently scanning the heap. It will prune and
defragment each page if required, and possibly perform freezing activity.
The heap_blks_scanned column can be used to monitor the progress of the
scan.
3. Vacuuming Indexes - VACUUM is currently vacuuming the indexes. If a
table has any indexes, this will happen at least once per vacuum, after the
heap has been completely scanned. It may happen multiple times per vacuum
if maintenance_work_mem
<https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM;
(or,
in the case of autovacuum, autovacuum_work_mem
<https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-AUTOVACUUM-WORK-MEM;
if
set) is insufficient to store the number of dead tuples found.
4. Vacuuming Heap - VACUUM is currently vacuuming the heap. Vacuuming the
heap is distinct from scanning the heap, and occurs after each instance of
vacuuming indexes. If heap_blks_scanned is less than heap_blks_total, the
system will return to scanning the heap after this phase is completed;
otherwise, it will begin cleaning up indexes after this phase is completed.
5. Cleaning up indexes - VACUUM is currently vacuuming the heap. Vacuuming
the heap is distinct from scanning the heap, and occurs after each instance
of vacuuming indexes. If heap_blks_scanned is less than heap_blks_total,
the system will return to scanning the heap after this phase is completed;
otherwise, it will begin cleaning up indexes after this phase is completed.
6. Truncating heap - VACUUM is currently truncating the heap so as to
return empty pages at the end of the relation to the operating system. This
occurs after cleaning up indexes.
7. Performing final cleanup - VACUUM is performing final cleanup. During
this phase, VACUUM will vacuum the free space map, update statistics in
pg_class, and report statistics to the cumulative statistics system. When
this phase is completed, VACUUM will end.
The resumable vacuum will store the following information during its run on
the disk:
1. Database Name/Id, Table Name/Oid
2. Phase of its run (from the ones listed in the table above)
3. Vacuum horizon (created during ‘initializing’ phase)
4. Array of dead tuple-ids accumulated in the ‘heap scan’ phase.
5. Number of times the dead tuple ids array was spilled over due to
memory limitation.
Out of the above information, #2 and #3 will be updated as it progresses.
When it is resumed, vacuum will first check the persisted information from
the last run. It will
1. Retrieve all the stored information on the disk from the last run
(listed above)
2. Check what phase the vacuum was during the last run when it stopped
3. Based on the phase, it will adapt the further actions based on that.
1. If the previous run was stopped in the initialization phase, then
the new run will start from scratch.
2. If the previous run was stopped during the heap scan phase, it
will use the persisted array of dead tids and it will start the scan from
the last accumulated dead tuple.
3. If the previous run was stopped during the ‘vacuuming index’
phase, it will start this phase all over, but from the beginning of the
stored dead tuple ids.
4. If the previous run was stopped during the ’vacuuming heap’ phase,
and without previous spillovers, it will continue vacuuming heap for the
rest of the dead tuple array and proceed for further phases. If
it was with
previous spillovers, then it will continue vacuuming the heap
for the rest
of the dead tuple array and go again for phase 2 if the heap
scan is still
incomplete and follow the loop which is already there.
5. If the previous run was stopped during any of the remaining
phases, it will just complete the remaining work and exit.
Indexes can change across the runs. Please note that in this proposal any
re-run above does not depend on the last state of the indices. Any actions
in this whole proposal does not depend on the last state of indices nor
does it store it.
This approach does not add any overhead in the DML code path. The changes
are limited only to vacuum operation and just enough to make it resumable.
There are no drastic changes to the regular flow.
This approach doesn’t change the core functions apart from conditionally
persisting the vacuum progress information to the disk. Thus, any future
enhancements to the core functions can be easily accommodated.
Please let me know or comment on this so that we can conclude if this does
look like a reasonable enhancement.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Proposal for Resumable Vacuum (again ...)
@ 2024-03-25 09:04 Jay <[email protected]>
parent: Jay <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Jay @ 2024-03-25 09:04 UTC (permalink / raw)
To: pgsql-hackers
Hi All,
A revised proposal with few minor corrections (thanks to
[email protected] for pointing the error):
<Start>
I am aware of few previous attempts and discussions on this topic
(eventually shelved or didn't materialize):
- https://www.postgresql.org/message-id/[email protected]
-
https://www.postgresql.org/message-id/CA%2BTgmoZgapzekbTqdBrcH8O8Yifi10_nB7uWLB8ajAhGL21M6A%40mail.g...
-
https://www.postgresql.org/message-id/flat/CAD21AoBqfMVWdk7Odh4A4OpF-m5GytRjXME5E8cEGXvhSJb8zw@mail....
And still I want to revise this topic for the obvious benefits.
I do not have any patch or code changes ready. The changes could be tricky
and might need efforts, possibly some re-factoring. Hence, before starting
the effort, I would like to get the proposal reviewed and consensus built
to avoid redundancy of efforts.
*Why do we need it? *
Since more and more large businesses are on-boarding PostgreSQL, it is only
fair that we make the essential utilities like vacuum more manageable and
scalable. The data sizes are definitely going to increase and maintenance
windows will reduce with businesses operating across the time zones and
24x7. Making the database more manageable with the least overhead is going
to be definitely a pressing need.
To avoid the repetition and duplicate efforts, I have picked up the snippet
below from the previous email conversation on the community (Ref:
https://www.postgresql.org/message-id/[email protected])
<Quote>
*For a large table, although it can be vacuumed by enabling vacuum
cost-based delay, the processing may last for several days (maybe hours).
It definitely has a negative effect on system performance. So if systems
which have maintenance time, it is preferred to vacuum in the maintenance
window. Vacuum tasks can be split into small subtasks, and they can be
scheduled into maintenance window time slots. This can reduce the impact of
vacuum to system service.*
*But currently vacuum tasks can not be split: if an interrupt or error
occurs during vacuum processing, vacuum totally forgets what it has done
and terminates itself. Following vacuum on the same table has to scan from
the beginning of the heap block. This proposal enable vacuum has capability
to stop and resume.*
</Quote>
*External Interface*
This feature is especially useful when the size of table/s is quite large
and their bloat is quite high and it is expected vacuum runs will take long
time.
Ref: https://www.postgresql.org/docs/current/sql-vacuum.html
vacuum [ ( *option* [, ...], *[{ for time = hh:mm}| {resume [for time =
hh:mm]}] *) ] [ *table_and_columns* [, ...] ]
The additional options give flexibility to run the vacuum for a limited
time and stop or resume the vacuum from the last time when it was stopped
for a given time.
When vacuum is invoked with ‘for time ...’ option it will store the
intermediate state of the dead tuples accumulated periodically on the disk
as it progresses. It will run for a specified time and stop after that
duration.
When vacuum is invoked with ‘for time ...’ option and is stopped
automatically after the specified time or interrupted manually and if it is
invoked next time with ‘resume’ option, it will try to check the stored
state of the last run and try to start as closely as possible from where it
left last time and avoid repetition of work.
When resumed, it can either run for a specified time again (if the duration
is specified) or run till completion if the duration is not specified.
When vacuum is invoked with ‘resume for’ option when there was no earlier
incomplete run or an earlier run with ‘for time’ option, the ‘for resume’
option will be ignored with a message in the errorlog.
When vacuum is invoked without ‘for time’ or ‘resume for’ options after
preceding incomplete runs with those options , then the persisted data from
the previous runs is discarded and deleted. This is important because
successive runs with ‘for time’ or ‘resume for’ assume the persisted data
is valid and there’s no run in between to invalidate it and the state of
heap pages in terms of vacuum is the same for the given saved vacuum
horizon.
In further discussion in the rest of this proposal, we will refer to vacuum
invoked with ‘for time’ or ‘resume for’ option as listed above as
‘resumable vacuum’.
Internal Changes (High level)For each table, vacuum progresses in the
following steps or phases (taken from the documentation)
https://www.postgresql.org/docs/current/progress-reporting.html#VACUUM-PHASES
<https://www.postgresql.org/docs/current/progress-reporting.html; :
1. Initializing - VACUUM is preparing to begin scanning the heap. This
phase is expected to be very brief.
2. Scanning heap - VACUUM is currently scanning the heap. It will prune and
defragment each page if required, and possibly perform freezing activity.
The heap_blks_scanned column can be used to monitor the progress of the
scan.
3. Vacuuming Indexes - VACUUM is currently vacuuming the indexes. If a
table has any indexes, this will happen at least once per vacuum, after the
heap has been completely scanned. It may happen multiple times per vacuum
if maintenance_work_mem
<https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM;
(or,
in the case of autovacuum, autovacuum_work_mem
<https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-AUTOVACUUM-WORK-MEM;
if
set) is insufficient to store the number of dead tuples found.
4. Vacuuming Heap - VACUUM is currently vacuuming the heap. Vacuuming the
heap is distinct from scanning the heap, and occurs after each instance of
vacuuming indexes. If heap_blks_scanned is less than heap_blks_total, the
system will return to scanning the heap after this phase is completed;
otherwise, it will begin cleaning up indexes after this phase is completed.
5. Cleaning up indexes - VACUUM is currently cleaning up indexes. This
occurs after the heap has been completely scanned and all vacuuming of the
indexes and the heap has been completed.
6. Truncating heap - VACUUM is currently truncating the heap so as to
return empty pages at the end of the relation to the operating system. This
occurs after cleaning up indexes.
7. Performing final cleanup - VACUUM is performing final cleanup. During
this phase, VACUUM will vacuum the free space map, update statistics in
pg_class, and report statistics to the cumulative statistics system. When
this phase is completed, VACUUM will end.
The resumable vacuum will store the following information during its run on
the disk:
1. Database Name/Id, Table Name/Oid
2. Vacuum horizon (created during ‘initializing’ phase)
3. Phase of its run (from the ones listed in the table above)
4. Array of dead tuple ids accumulated in ‘heap scan’ phase.
5. In case the phase is #4 or later above, then the progress of vacuum
in dead tuple id array (the index of tuple id up to which the processing is
done, which can be refreshed with some frequency e.g. per heap page)
6. Number of times the dead tuple ids array was spilled over due to
memory limitation.
Out of the above information, #3 to #6 will be updated as it progresses.
When it is resumed, vacuum will first check the persisted information from
the last run. It will
1. Retrieve all the stored information on the disk from the last run
(listed above)
2. Check what phase the vacuum was during the last run when it stopped
3. Based on the phase, it will adapt the further actions based on that.
1. If the previous run was stopped in the initialization phase, then
the new run will start from scratch.
2. If the previous run was stopped during the heap scan phase, it
will use the persisted array of dead tids and it will start the scan from
the last accumulated dead tuple.
3. If the previous run was stopped during the ‘vacuuming index’
phase, it will start this phase all over, but from the beginning of the
stored dead tuple ids in the array.
4. If the previous run was stopped during the ’vacuuming heap’ phase,
and without previous spillovers, it will continue vacuuming heap for the
rest of the dead tuple array and proceed for further phases. If
it was with
previous spillovers, then it will continue vacuuming the heap
for the rest
of the dead tuple array and go again for phase 2 if the heap
scan is still
incomplete and follow the loop which is already there.
5. If the previous run was stopped during any of the remaining
phases, it will just complete the remaining work and exit.
Highlights of the proposal
- Indexes can change across the runs. Please note that in this proposal
any re-run above does not depend on the last state of the indices. Any
actions in this whole proposal does not depend on the last state of indices
nor does it store it.
- This approach does not add any overhead in the DML code path. The
changes are limited only to vacuum operation and just enough to make it
resumable. There are no drastic changes to the regular flow.
- This approach doesn’t change the core functions apart from
conditionally persisting the vacuum progress information to the disk. Thus,
any future enhancements to the core functions can be easily accommodated.
Please let me know or comment on this so that we can conclude if this does
look like a reasonable enhancement.
<End>
On Sun, Mar 24, 2024 at 9:57 AM Jay <[email protected]> wrote:
> Hi,
>
> I am aware of few previous attempts and discussions on this topic
> (eventually shelved or didn't materialize):
>
> - https://www.postgresql.org/message-id/[email protected]
> -
> https://www.postgresql.org/message-id/CA%2BTgmoZgapzekbTqdBrcH8O8Yifi10_nB7uWLB8ajAhGL21M6A%40mail.g...
>
> -
> https://www.postgresql.org/message-id/flat/CAD21AoBqfMVWdk7Odh4A4OpF-m5GytRjXME5E8cEGXvhSJb8zw@mail....
>
>
> And still I want to revise this topic for the obvious benefits.
>
> I do not have any patch or code changes ready. The changes could be tricky
> and might need efforts, possibly some re-factoring. Hence, before starting
> the effort, I would like to get the proposal reviewed and consensus built
> to avoid redundancy of efforts.
>
> *Why do we need it? *
>
> Since more and more large businesses are on-boarding PostgreSQL, it is
> only fair that we make the essential utilities like vacuum more manageable
> and scalable. The data sizes are definitely going to increase and
> maintenance windows will reduce with businesses operating across the time
> zones and 24x7. Making the database more manageable with the least overhead
> is going to be definitely a pressing need.
>
> To avoid the repetition and duplicate efforts, I have picked up the
> snippet below from the previous email conversation on the community (Ref:
> https://www.postgresql.org/message-id/[email protected])
>
> <Quote>
> *For a large table, although it can be vacuumed by enabling vacuum
> cost-based delay, the processing may last for several days (maybe hours).
> It definitely has a negative effect on system performance. So if systems
> which have maintenance time, it is preferred to vacuum in the maintenance
> window. Vacuum tasks can be split into small subtasks, and they can be
> scheduled into maintenance window time slots. This can reduce the impact of
> vacuum to system service.*
>
> *But currently vacuum tasks can not be split: if an interrupt or error
> occurs during vacuum processing, vacuum totally forgets what it has done
> and terminates itself. Following vacuum on the same table has to scan from
> the beginning of the heap block. This proposal enable vacuum has capability
> to stop and resume.*
> </Quote>
>
> *External Interface*
>
> This feature is especially useful when the size of table/s is quite large
> and their bloat is quite high and it is expected vacuum runs will take long
> time.
>
> Ref: https://www.postgresql.org/docs/current/sql-vacuum.html
> vacuum [ ( *option* [, ...], *[{ for time = hh:mm}| {resume [for time =
> hh:mm]}] *) ] [ *table_and_columns* [, ...] ]
>
> The additional options give flexibility to run the vacuum for a limited
> time and stop or resume the vacuum from the last time when it was stopped
> for a given time.
>
> When vacuum is invoked with ‘for time ...’ option it will store the
> intermediate state of the dead tuples accumulated periodically on the disk
> as it progresses. It will run for a specified time and stop after that
> duration.
>
> When vacuum is invoked with ‘for time ...’ option and is stopped
> automatically after the specified time or interrupted manually and if it is
> invoked next time with ‘resume’ option, it will try to check the stored
> state of the last run and try to start as closely as possible from where it
> left last time and avoid repetition of work.
>
> When resumed, it can either run for a specified time again (if the
> duration is specified) or run till completion if the duration is not
> specified.
>
> When vacuum is invoked with ‘resume for’ option when there was no earlier
> incomplete run or an earlier run with ‘for time’ option, the ‘for resume’
> option will be ignored with a message in the errorlog.
>
> When vacuum is invoked without ‘for time’ or ‘resume for’ options after
> preceding incomplete runs with those options , then the persisted data from
> the previous runs is discarded and deleted. This is important because
> successive runs with ‘for time’ or ‘resume for’ assume the persisted data
> is valid and there’s no run in between to invalidate it and the state of
> heap pages in terms of vacuum is the same for the given saved vacuum
> horizon.
>
> In further discussion in the rest of this proposal, we will refer to
> vacuum invoked with ‘for time’ or ‘resume for’ option as listed above as
> ‘resumable vacuum’.
>
> Internal Changes (High level)For each table, vacuum progresses in the
> following steps or phases (taken from the documentation)
>
> https://www.postgresql.org/docs/current/progress-reporting.html#VACUUM-PHASES
> <https://www.postgresql.org/docs/current/progress-reporting.html; :
>
> 1. Initializing - VACUUM is preparing to begin scanning the heap. This
> phase is expected to be very brief.
>
> 2. Scanning heap - VACUUM is currently scanning the heap. It will prune
> and defragment each page if required, and possibly perform freezing
> activity. The heap_blks_scanned column can be used to monitor the
> progress of the scan.
>
> 3. Vacuuming Indexes - VACUUM is currently vacuuming the indexes. If a
> table has any indexes, this will happen at least once per vacuum, after the
> heap has been completely scanned. It may happen multiple times per vacuum
> if maintenance_work_mem
> <https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM; (or,
> in the case of autovacuum, autovacuum_work_mem
> <https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-AUTOVACUUM-WORK-MEM; if
> set) is insufficient to store the number of dead tuples found.
>
> 4. Vacuuming Heap - VACUUM is currently vacuuming the heap. Vacuuming the
> heap is distinct from scanning the heap, and occurs after each instance of
> vacuuming indexes. If heap_blks_scanned is less than heap_blks_total, the
> system will return to scanning the heap after this phase is completed;
> otherwise, it will begin cleaning up indexes after this phase is completed.
>
> 5. Cleaning up indexes - VACUUM is currently vacuuming the heap.
> Vacuuming the heap is distinct from scanning the heap, and occurs after
> each instance of vacuuming indexes. If heap_blks_scanned is less than
> heap_blks_total, the system will return to scanning the heap after this
> phase is completed; otherwise, it will begin cleaning up indexes after this
> phase is completed.
>
> 6. Truncating heap - VACUUM is currently truncating the heap so as to
> return empty pages at the end of the relation to the operating system. This
> occurs after cleaning up indexes.
>
> 7. Performing final cleanup - VACUUM is performing final cleanup. During
> this phase, VACUUM will vacuum the free space map, update statistics in
> pg_class, and report statistics to the cumulative statistics system. When
> this phase is completed, VACUUM will end.
>
> The resumable vacuum will store the following information during its run
> on the disk:
>
> 1. Database Name/Id, Table Name/Oid
> 2. Phase of its run (from the ones listed in the table above)
> 3. Vacuum horizon (created during ‘initializing’ phase)
> 4. Array of dead tuple-ids accumulated in the ‘heap scan’ phase.
> 5. Number of times the dead tuple ids array was spilled over due to
> memory limitation.
>
> Out of the above information, #2 and #3 will be updated as it progresses.
>
> When it is resumed, vacuum will first check the persisted information from
> the last run. It will
>
> 1. Retrieve all the stored information on the disk from the last run
> (listed above)
> 2. Check what phase the vacuum was during the last run when it stopped
> 3. Based on the phase, it will adapt the further actions based on that.
> 1. If the previous run was stopped in the initialization phase,
> then the new run will start from scratch.
> 2. If the previous run was stopped during the heap scan phase, it
> will use the persisted array of dead tids and it will start the scan from
> the last accumulated dead tuple.
> 3. If the previous run was stopped during the ‘vacuuming index’
> phase, it will start this phase all over, but from the beginning of the
> stored dead tuple ids.
> 4. If the previous run was stopped during the ’vacuuming heap’
> phase, and without previous spillovers, it will continue vacuuming heap for
> the rest of the dead tuple array and proceed for further phases. If it was
> with previous spillovers, then it will continue vacuuming the heap for the
> rest of the dead tuple array and go again for phase 2 if the heap scan is
> still incomplete and follow the loop which is already there.
> 5. If the previous run was stopped during any of the remaining
> phases, it will just complete the remaining work and exit.
>
> Indexes can change across the runs. Please note that in this proposal any
> re-run above does not depend on the last state of the indices. Any actions
> in this whole proposal does not depend on the last state of indices nor
> does it store it.
>
> This approach does not add any overhead in the DML code path. The changes
> are limited only to vacuum operation and just enough to make it resumable.
> There are no drastic changes to the regular flow.
>
> This approach doesn’t change the core functions apart from conditionally
> persisting the vacuum progress information to the disk. Thus, any future
> enhancements to the core functions can be easily accommodated.
>
> Please let me know or comment on this so that we can conclude if this does
> look like a reasonable enhancement.
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-03-25 09:04 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-12-26 08:43 [PATCH 1/4] Remove entries that haven't been used for a certain time Kyotaro Horiguchi <[email protected]>
2017-12-26 08:43 [PATCH 1/3] Remove entries that haven't been used for a certain time Kyotaro Horiguchi <[email protected]>
2018-10-16 04:04 [PATCH 1/3] Remove entries that haven't been used for a certain time Kyotaro Horiguchi <[email protected]>
2023-02-15 22:45 [PATCH v20 3/5] rename archive-modules.sgml to archive-and-restore-modules.sgml Nathan Bossart <[email protected]>
2024-03-24 04:27 Proposal for Resumable Vacuum (again ...) Jay <[email protected]>
2024-03-25 09:04 ` Re: Proposal for Resumable Vacuum (again ...) Jay <[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