At Mon, 01 Apr 2019 11:05:32 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI
<horiguchi.kyot...@lab.ntt.co.jp> wrote in
<20190401.110532.102998353.horiguchi.kyot...@lab.ntt.co.jp>
> At Fri, 29 Mar 2019 17:24:40 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI
> <horiguchi.kyot...@lab.ntt.co.jp> wrote in
> <20190329.172440.199616830.horiguchi.kyot...@lab.ntt.co.jp>
> > I ran three artificial test cases. The database is created by
> > gen_tbl.pl. Numbers are the average of the fastest five runs in
> > successive 15 runs.
> >
> > Test cases are listed below.
> >
> > 1_0. About 3,000,000 negative entries are created in pg_statstic
> > cache by scanning that many distinct columns. It is 3000 tables
> > * 1001 columns. Pruning scans happen several times while a run
> > but no entries are removed. This emulates the bloating phase of
> > cache. catalog_cache_prune_min_age is default (300s).
> > (access_tbl1.pl)
> >
> > 1_1. Same to 1_0 except that catalog_cache_prune_min_age is 0,
> > which means turning off.
> >
> > 2_0. Repeatedly access 1001 of the 3,000,000 entries 6000
> > times. This emulates the stable cache case without having
> > pruning. catalog_cache_prune_min_age is default (300s).
> > (access_tbl2.pl)
> >
> > 2_1. Same to 2_0 except that catalog_cache_prune_min_age is 0,
> > which means turning off.
> >
> > 3_0. Scan over the 3,000,000 entries twice with setting prune_age
> > to 10s. A run takes about 18 seconds on my box so fair amount
> > of old entries are removed. This emulates the stable case with
> > continuous pruning. (access_tbl3.pl)
> >
> > 3_1. Same to 3_0 except that catalog_cache_prune_min_age is 0,
> > which means turning off.
..
> I had another.. unstable.. result.
dlist_move_head is used every time an entry is accessed. It moves
the accessed element to the top of bucket expecting that
subsequent access become faster - a kind of LRU maintenance. But
the mean length of a bucket is 2 so dlist_move_head is too
complex than following one step of link. So I removed it in
pruning patch.
I understand I cannot get rid of noise a far as I'm poking the
feature from client via communication and SQL layer.
The attached extension surgically exercises
SearchSysCache3(STATRELATTINH) in the almost pattern with the
benchmarks taken last week. I believe that that gives far
reliable numbers. But still the number fluctuates by up to about
10% every trial, and the difference among the methods is under
the fluctulation. I'm tired.. But this still looks somewhat wrong.
ratio in the following table is the percentage to the master for
the same test. master2 is a version that removed the
dlink_move_head from master.
binary | test | count | avg | stddev | ratio
---------+------+-------+---------+--------+--------
master | 1_0 | 5 | 7841.42 | 6.91
master | 2_0 | 5 | 3810.10 | 8.51
master | 3_0 | 5 | 7826.17 | 11.98
master | 1_1 | 5 | 7905.73 | 5.69
master | 2_1 | 5 | 3827.15 | 5.55
master | 3_1 | 5 | 7822.67 | 13.75
---------+------+-------+---------+--------+--------
master2 | 1_0 | 5 | 7538.05 | 16.65 | 96.13
master2 | 2_0 | 5 | 3927.05 | 11.58 | 103.07
master2 | 3_0 | 5 | 7455.47 | 12.03 | 95.26
master2 | 1_1 | 5 | 7485.60 | 9.38 | 94.69
master2 | 2_1 | 5 | 3870.81 | 5.54 | 101.14
master2 | 3_1 | 5 | 7437.35 | 9.91 | 95.74
---------+------+-------+---------+--------+--------
LRU | 1_0 | 5 | 7633.57 | 9.00 | 97.35
LRU | 2_0 | 5 | 4062.43 | 5.90 | 106.62
LRU | 3_0 | 5 | 8340.51 | 6.12 | 106.57
LRU | 1_1 | 5 | 7645.87 | 13.29 | 96.71
LRU | 2_1 | 5 | 4026.60 | 7.56 | 105.21
LRU | 3_1 | 5 | 8400.10 | 19.07 | 107.38
---------+------+-------+---------+--------+--------
Full | 1_0 | 5 | 7481.61 | 6.70 | 95.41
Full | 2_0 | 5 | 4084.46 | 14.50 | 107.20
Full | 3_0 | 5 | 8166.23 | 14.80 | 104.35
Full | 1_1 | 5 | 7447.20 | 10.93 | 94.20
Full | 2_1 | 5 | 4016.88 | 8.53 | 104.96
Full | 3_1 | 5 | 8258.80 | 7.91 | 105.58
---------+------+-------+---------+--------+--------
FullMod | 1_0 | 5 | 7291.80 | 14.03 | 92.99
FullMod | 2_0 | 5 | 4006.36 | 7.64 | 105.15
FullMod | 3_0 | 5 | 8143.60 | 9.26 | 104.06
FullMod | 1_1 | 5 | 7270.66 | 6.24 | 91.97
FullMod | 2_1 | 5 | 3996.20 | 13.00 | 104.42
FullMod | 3_1 | 5 | 8012.55 | 7.09 | 102 43
So "Full (scan) Mod" wins again, or the diffence is under error.
I don't think this level of difference can be a reason to reject
this kind of resource saving mechanism. LRU version doesn't seem
particularly slow but also doesn't seem particularly fast for the
complexity. FullMod version doesn't look differently.
So it seems to me that the simplest "Full" version wins. The
attached is rebsaed version. dlist_move_head(entry) is removed as
mentioned above in that patch.
The third and fourth attached are a set of script I used.
$ perl gen_tbl.pl | psql postgres
$ run.sh > log.txt
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
>From 57c9dab7fff7b81890657594711bbfb47a3e0f0d Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi <horiguchi.kyot...@lab.ntt.co.jp>
Date: Fri, 1 Mar 2019 13:32:51 +0900
Subject: [PATCH 1/2] Remove entries that haven't been used for a certain time
Catcache entries happen to be left alone for several reasons. It is
not desirable that such useless entries eat up memory. Catcache
pruning feature removes entries that haven't been accessed for a
certain time before enlarging hash array.
---
doc/src/sgml/config.sgml | 19 ++++
src/backend/tcop/postgres.c | 2 +
src/backend/utils/cache/catcache.c | 124 +++++++++++++++++++++++++-
src/backend/utils/misc/guc.c | 12 +++
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/include/utils/catcache.h | 18 ++++
6 files changed, 172 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index bc1d0f7bfa..819b252029 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1677,6 +1677,25 @@ include_dir 'conf.d'
</listitem>
</varlistentry>
+ <varlistentry id="guc-catalog-cache-prune-min-age" xreflabel="catalog_cache_prune_min_age">
+ <term><varname>catalog_cache_prune_min_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>catalog_cache_prune_min_age</varname> configuration
+ parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the minimum amount of unused time in seconds at which a
+ system catalog cache entry is removed. -1 indicates that this feature
+ is disabled at all. The value defaults to 300 seconds (<literal>5
+ minutes</literal>). The entries that are not used for the duration
+ can be removed to prevent catalog cache from bloating with useless
+ entries.
+ </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/tcop/postgres.c b/src/backend/tcop/postgres.c
index 44a59e1d4f..a0efac86bc 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -71,6 +71,7 @@
#include "tcop/pquery.h"
#include "tcop/tcopprot.h"
#include "tcop/utility.h"
+#include "utils/catcache.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/ps_status.h"
@@ -2577,6 +2578,7 @@ start_xact_command(void)
* not desired, the timeout has to be disabled explicitly.
*/
enable_statement_timeout();
+ SetCatCacheClock(GetCurrentStatementStartTimestamp());
}
static void
diff --git a/src/backend/utils/cache/catcache.c b/src/backend/utils/cache/catcache.c
index d05930bc4c..e85f2b038c 100644
--- a/src/backend/utils/cache/catcache.c
+++ b/src/backend/utils/cache/catcache.c
@@ -38,6 +38,7 @@
#include "utils/rel.h"
#include "utils/resowner_private.h"
#include "utils/syscache.h"
+#include "utils/timeout.h"
/* #define CACHEDEBUG */ /* turns DEBUG elogs on */
@@ -60,9 +61,24 @@
#define CACHE_elog(...)
#endif
+/*
+ * GUC variable to define the minimum age of entries that will be considered
+ * to be evicted in seconds. -1 to disable the feature.
+ */
+int catalog_cache_prune_min_age = 300;
+
+/*
+ * Minimum interval between two successive moves of a cache entry in LRU list,
+ * in microseconds.
+ */
+#define MIN_LRU_UPDATE_INTERVAL 100000 /* 100ms */
+
/* Cache management header --- pointer is NULL until created */
static CatCacheHeader *CacheHdr = NULL;
+/* Clock for the last accessed time of a catcache entry. */
+TimestampTz catcacheclock = 0;
+
static inline HeapTuple SearchCatCacheInternal(CatCache *cache,
int nkeys,
Datum v1, Datum v2,
@@ -473,6 +489,7 @@ CatCacheRemoveCTup(CatCache *cache, CatCTup *ct)
/* delink from linked list */
dlist_delete(&ct->cache_elem);
+ dlist_delete(&ct->lru_node);
/*
* Free keys when we're dealing with a negative entry, normal entries just
@@ -833,6 +850,7 @@ InitCatCache(int id,
cp->cc_nkeys = nkeys;
for (i = 0; i < nkeys; ++i)
cp->cc_keyno[i] = key[i];
+ dlist_init(&cp->cc_lru_list);
/*
* new cache is initialized as far as we can go for now. print some
@@ -850,9 +868,83 @@ InitCatCache(int id,
*/
MemoryContextSwitchTo(oldcxt);
+ /* initialize catcache reference clock if haven't done yet */
+ if (catcacheclock == 0)
+ catcacheclock = GetCurrentTimestamp();
+
return cp;
}
+/*
+ * CatCacheCleanupOldEntries - Remove infrequently-used entries
+ *
+ * Catcache entries happen to be left unused for a long time for several
+ * reasons. Remove such entries to prevent catcache from bloating. It is based
+ * on the similar algorithm with buffer eviction. Entries that are accessed
+ * several times in a certain period live longer than those that have had less
+ * access in the same duration.
+ */
+static bool
+CatCacheCleanupOldEntries(CatCache *cp)
+{
+ int nremoved = 0;
+ dlist_mutable_iter iter;
+
+ /* Return immediately if disabled */
+ if (catalog_cache_prune_min_age == 0)
+ return false;
+
+ /* Scan over LRU to find entries to remove */
+ dlist_foreach_modify(iter, &cp->cc_lru_list)
+ {
+ CatCTup *ct = dlist_container(CatCTup, lru_node, iter.cur);
+ long entry_age;
+ int us;
+
+ /* Don't remove referenced entries */
+ if (ct->refcount != 0 ||
+ (ct->c_list && ct->c_list->refcount != 0))
+ continue;
+
+ /*
+ * Calculate the duration from the time from the last access to
+ * the "current" time. catcacheclock is updated per-statement
+ * basis.
+ */
+ TimestampDifference(ct->lastaccess, catcacheclock, &entry_age, &us);
+
+ if (entry_age < catalog_cache_prune_min_age)
+ {
+ /*
+ * We don't have older entries, exit. At least one removal
+ * prevents rehashing this time.
+ */
+ break;
+ }
+
+ /*
+ * Entries that are not accessed after the last pruning are removed in
+ * that seconds, and their lives are prolonged according to how many
+ * times they are accessed up to three times of the duration. We don't
+ * try shrink buckets since pruning effectively caps catcache
+ * expansion in the long term.
+ */
+ if (ct->naccess > 0)
+ ct->naccess--;
+ else
+ {
+ CatCacheRemoveCTup(cp, ct);
+ nremoved++;
+ }
+ }
+
+ if (nremoved > 0)
+ elog(DEBUG1, "pruning catalog cache id=%d for %s: removed %d / %d",
+ cp->id, cp->cc_relname, nremoved, cp->cc_ntup + nremoved);
+
+ return nremoved > 0;
+}
+
/*
* Enlarge a catcache, doubling the number of buckets.
*/
@@ -1262,7 +1354,21 @@ SearchCatCacheInternal(CatCache *cache,
* most frequently accessed elements in any hashbucket will tend to be
* near the front of the hashbucket's list.)
*/
- dlist_move_head(bucket, &ct->cache_elem);
+ /* dlist_move_head(bucket, &ct->cache_elem);*/
+
+ /* prolong life of this entry */
+ if (ct->naccess < 2)
+ ct->naccess++;
+
+ /*
+ * Don't update LRU too frequently. We need to maintain the LRU even
+ * if pruning is inactive since it can be turned on on-session.
+ */
+ if (catcacheclock - ct->lastaccess > MIN_LRU_UPDATE_INTERVAL)
+ {
+ ct->lastaccess = catcacheclock;
+ dlist_move_tail(&cache->cc_lru_list, &ct->lru_node);
+ }
/*
* If it's a positive entry, bump its refcount and return it. If it's
@@ -1888,19 +1994,29 @@ 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_tail(&cache->cc_lru_list, &ct->lru_node);
dlist_push_head(&cache->cc_bucket[hashIndex], &ct->cache_elem);
cache->cc_ntup++;
CacheHdr->ch_ntup++;
+ /* increase refcount so that the new entry survives pruning */
+ ct->refcount++;
+
/*
- * 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 removing infrequently used
+ * entries to make a room for the new entry. If 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);
+ ct->refcount--;
+
return ct;
}
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 1766e46037..e671d4428e 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -82,6 +82,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"
@@ -2249,6 +2250,17 @@ static struct config_int ConfigureNamesInt[] =
NULL, NULL, NULL
},
+ {
+ {"catalog_cache_prune_min_age", PGC_USERSET, RESOURCES_MEM,
+ gettext_noop("System catalog cache entries that live unused for longer than this seconds are considered for removal."),
+ gettext_noop("The value of -1 turns off pruning."),
+ GUC_UNIT_S
+ },
+ &catalog_cache_prune_min_age,
+ 300, -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 bbbeb4bb15..d88ec57382 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -128,6 +128,7 @@
#work_mem = 4MB # min 64kB
#maintenance_work_mem = 64MB # min 1MB
#autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem
+#catalog_cache_prune_min_age = 300s # -1 disables pruning
#max_stack_depth = 2MB # min 100kB
#shared_memory_type = mmap # 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 65d816a583..a21c53644a 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 */
+ dlist_head cc_lru_list;
/*
* Keep these at the end, so that compiling catcache.c with CATCACHE_STATS
@@ -119,6 +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; /* timestamp of the last usage */
+ dlist_node lru_node; /* LRU node */
/*
* The tuple may also be a member of at most one CatCList. (If a single
@@ -189,6 +194,19 @@ typedef struct catcacheheader
/* this extern duplicates utils/memutils.h... */
extern PGDLLIMPORT MemoryContext CacheMemoryContext;
+/* for guc.c, not PGDLLPMPORT'ed */
+extern int catalog_cache_prune_min_age;
+
+/* source clock for access timestamp of catcache entries */
+extern TimestampTz catcacheclock;
+
+/* SetCatCacheClock - set catcache timestamp source clodk */
+static inline void
+SetCatCacheClock(TimestampTz ts)
+{
+ catcacheclock = ts;
+}
+
extern void CreateCacheMemoryContext(void);
extern CatCache *InitCatCache(int id, Oid reloid, Oid indexoid,
--
2.16.3
>From ac4a9dc1bb822f9df36d453354b953d2b383545d Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi <horiguchi.kyot...@lab.ntt.co.jp>
Date: Thu, 4 Apr 2019 21:16:17 +0900
Subject: [PATCH 2/2] Benchmark extension for catcache pruning feature.
This extension surgically exercises CatCacheSearch() on STATRELATTINH
and returns the duration in milliseconds.
---
contrib/catcachebench/Makefile | 17 ++
contrib/catcachebench/catcachebench--0.0.sql | 9 ++
contrib/catcachebench/catcachebench.c | 229 +++++++++++++++++++++++++++
contrib/catcachebench/catcachebench.control | 6 +
4 files changed, 261 insertions(+)
create mode 100644 contrib/catcachebench/Makefile
create mode 100644 contrib/catcachebench/catcachebench--0.0.sql
create mode 100644 contrib/catcachebench/catcachebench.c
create mode 100644 contrib/catcachebench/catcachebench.control
diff --git a/contrib/catcachebench/Makefile b/contrib/catcachebench/Makefile
new file mode 100644
index 0000000000..0478818b25
--- /dev/null
+++ b/contrib/catcachebench/Makefile
@@ -0,0 +1,17 @@
+MODULE_big = catcachebench
+OBJS = catcachebench.o
+
+EXTENSION = catcachebench
+DATA = catcachebench--0.0.sql
+PGFILEDESC = "catcachebench - benchmark for catcache pruning feature"
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/catcachebench
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/catcachebench/catcachebench--0.0.sql b/contrib/catcachebench/catcachebench--0.0.sql
new file mode 100644
index 0000000000..e091baaaa7
--- /dev/null
+++ b/contrib/catcachebench/catcachebench--0.0.sql
@@ -0,0 +1,9 @@
+/* contrib/catcachebench/catcachebench--0.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION catcachebench" to load this file. \quit
+
+CREATE FUNCTION catcachebench(IN type int)
+RETURNS double precision
+AS 'MODULE_PATHNAME', 'catcachebench'
+LANGUAGE C STRICT VOLATILE;
diff --git a/contrib/catcachebench/catcachebench.c b/contrib/catcachebench/catcachebench.c
new file mode 100644
index 0000000000..36d21d13c1
--- /dev/null
+++ b/contrib/catcachebench/catcachebench.c
@@ -0,0 +1,229 @@
+/*
+ * catcachebench: test code for cache pruning feature
+ */
+#include "postgres.h"
+#include "catalog/pg_type.h"
+#include "catalog/pg_statistic.h"
+#include "executor/spi.h"
+#include "utils/catcache.h"
+#include "utils/syscache.h"
+
+Oid tableoids[10000];
+int ntables = 0;
+int16 attnums[1000];
+int natts = 0;
+
+PG_MODULE_MAGIC;
+
+double catcachebench1(void);
+double catcachebench2(void);
+double catcachebench3(void);
+void collectinfo(void);
+void catcachewarmup(void);
+
+PG_FUNCTION_INFO_V1(catcachebench);
+
+Datum
+catcachebench(PG_FUNCTION_ARGS)
+{
+ int testtype = PG_GETARG_INT32(0);
+ double ms;
+
+ collectinfo();
+
+ /* flush the catalog -- safe? don't mind. */
+ CatalogCacheFlushCatalog(StatisticRelationId);
+
+ switch (testtype)
+ {
+ case 0:
+ catcachewarmup(); /* prewarm of syscatalog */
+ PG_RETURN_NULL();
+ case 1:
+ ms = catcachebench1(); break;
+ case 2:
+ ms = catcachebench2(); break;
+ case 3:
+ ms = catcachebench3(); break;
+ default:
+ elog(ERROR, "Invalid test type: %d", testtype);
+ }
+
+ PG_RETURN_DATUM(Float8GetDatum(ms));
+}
+
+double
+catcachebench1(void)
+{
+ int t, a;
+ instr_time start,
+ duration;
+
+ INSTR_TIME_SET_CURRENT(start);
+ for (t = 0 ; t < ntables ; t++)
+ {
+ for (a = 0 ; a < natts ; a++)
+ {
+ HeapTuple tup;
+
+ tup = SearchSysCache3(STATRELATTINH,
+ ObjectIdGetDatum(tableoids[t]),
+ Int16GetDatum(attnums[a]),
+ BoolGetDatum(false));
+ /* should be null, but.. */
+ if (HeapTupleIsValid(tup))
+ ReleaseSysCache(tup);
+ }
+ }
+ INSTR_TIME_SET_CURRENT(duration);
+ INSTR_TIME_SUBTRACT(duration, start);
+
+ return INSTR_TIME_GET_MILLISEC(duration);
+};
+
+double
+catcachebench2(void)
+{
+ int t, a;
+ instr_time start,
+ duration;
+
+ INSTR_TIME_SET_CURRENT(start);
+ for (t = 0 ; t < 60000 ; t++)
+ {
+ for (a = 0 ; a < natts ; a++)
+ {
+ HeapTuple tup;
+
+ tup = SearchSysCache3(STATRELATTINH,
+ ObjectIdGetDatum(tableoids[0]),
+ Int16GetDatum(attnums[a]),
+ BoolGetDatum(false));
+ /* should be null, but.. */
+ if (HeapTupleIsValid(tup))
+ ReleaseSysCache(tup);
+ }
+ }
+ INSTR_TIME_SET_CURRENT(duration);
+ INSTR_TIME_SUBTRACT(duration, start);
+
+ return INSTR_TIME_GET_MILLISEC(duration);
+};
+
+double
+catcachebench3(void)
+{
+ int i, t, a;
+ instr_time start,
+ duration;
+
+ INSTR_TIME_SET_CURRENT(start);
+ for (i = 0 ; i < 2 ; i++)
+ {
+ for (t = 0 ; t < ntables ; t++)
+ {
+ for (a = 0 ; a < natts ; a++)
+ {
+ HeapTuple tup;
+
+ tup = SearchSysCache3(STATRELATTINH,
+ ObjectIdGetDatum(tableoids[t]),
+ Int16GetDatum(attnums[a]),
+ BoolGetDatum(false));
+ /* should be null, but.. */
+ if (HeapTupleIsValid(tup))
+ ReleaseSysCache(tup);
+ }
+ }
+ }
+ INSTR_TIME_SET_CURRENT(duration);
+ INSTR_TIME_SUBTRACT(duration, start);
+
+ return INSTR_TIME_GET_MILLISEC(duration);
+};
+
+void
+catcachewarmup(void)
+{
+ int t, a;
+
+ /* load up catalog tables */
+ for (t = 0 ; t < ntables ; t++)
+ {
+ for (a = 0 ; a < natts ; a++)
+ {
+ HeapTuple tup;
+
+ tup = SearchSysCache3(STATRELATTINH,
+ ObjectIdGetDatum(tableoids[t]),
+ Int16GetDatum(attnums[a]),
+ BoolGetDatum(false));
+ /* should be null, but.. */
+ if (HeapTupleIsValid(tup))
+ ReleaseSysCache(tup);
+ }
+ }
+}
+
+void
+collectinfo(void)
+{
+ int ret;
+ Datum values[10000];
+ bool nulls[10000];
+ Oid types0[] = {OIDOID};
+ int i;
+
+ ntables = 0;
+ natts = 0;
+
+ SPI_connect();
+ /* collect target tables */
+ ret = SPI_execute("select oid from pg_class where relnamespace = (select oid from pg_namespace where nspname = \'test\')",
+ true, 0);
+ if (ret != SPI_OK_SELECT)
+ elog(ERROR, "Failed 1");
+ if (SPI_processed == 0)
+ elog(ERROR, "no relation found in schema \"test\"");
+ if (SPI_processed > 10000)
+ elog(ERROR, "too many relation found in schema \"test\"");
+
+ for (i = 0 ; i < SPI_processed ; i++)
+ {
+ heap_deform_tuple(SPI_tuptable->vals[i], SPI_tuptable->tupdesc,
+ values, nulls);
+ if (nulls[0])
+ elog(ERROR, "Failed 2");
+
+ tableoids[ntables++] = DatumGetObjectId(values[0]);
+ }
+ SPI_finish();
+ elog(DEBUG1, "%d tables found", ntables);
+
+ values[0] = ObjectIdGetDatum(tableoids[0]);
+ nulls[0] = false;
+ SPI_connect();
+ ret = SPI_execute_with_args("select attnum from pg_attribute where attrelid = (select oid from pg_class where oid = $1)",
+ 1, types0, values, NULL, true, 0);
+ if (SPI_processed == 0)
+ elog(ERROR, "no attribute found in table %d", tableoids[0]);
+ if (SPI_processed > 10000)
+ elog(ERROR, "too many relation found in table %d", tableoids[0]);
+
+ /* collect target attributes. assuming all tables have the same attnums */
+ for (i = 0 ; i < SPI_processed ; i++)
+ {
+ int16 attnum;
+
+ heap_deform_tuple(SPI_tuptable->vals[i], SPI_tuptable->tupdesc,
+ values, nulls);
+ if (nulls[0])
+ elog(ERROR, "Failed 3");
+ attnum = DatumGetInt16(values[0]);
+
+ if (attnum > 0)
+ attnums[natts++] = attnum;
+ }
+ SPI_finish();
+ elog(DEBUG1, "%d attributes found", natts);
+}
diff --git a/contrib/catcachebench/catcachebench.control b/contrib/catcachebench/catcachebench.control
new file mode 100644
index 0000000000..3fc9d2e420
--- /dev/null
+++ b/contrib/catcachebench/catcachebench.control
@@ -0,0 +1,6 @@
+# catcachebench
+
+comment = 'benchmark for catcache pruning'
+default_version = '0.0'
+module_pathname = '$libdir/catcachebench'
+relocatable = true
--
2.16.3
#! /usr/bin/perl
$collist = "";
foreach $i (0..1000) {
$collist .= sprintf(", c%05d int", $i);
}
$collist = substr($collist, 2);
printf "drop schema if exists test cascade;\n";
printf "create schema test;\n";
foreach $i (0..2999) {
printf "create table test.t%04d ($collist);\n", $i;
}
#!/bin/bash
LOOPS=5
BINROOT=/home/horiguti/bin
DATADIR=/home/horiguti/data/data_work_o2
PREC="numeric(10,2)"
killall postgres
sleep 3
run() {
local BINARY=$1
local PGCTL=$2/bin/pg_ctl
if [ "$3" != "" ]; then
local SETTING1="set catalog_cache_prune_min_age to \"$3\";"
local SETTING2="set catalog_cache_prune_min_age to \"$4\";"
local SETTING3="set catalog_cache_prune_min_age to \"$5\";"
fi
$PGCTL --pgdata=$DATADIR start
psql postgres -e <<EOF
create extension if not exists catcachebench;
select catcachebench(0);
$SETTING1
select '${BINARY}' as binary, '1_0' as test, count(a), avg(a)::${PREC},
stddev(a)::${PREC} from (select catcachebench(1) from generate_series(1,
${LOOPS})) as a(a)
UNION ALL select '${BINARY}', '2_0' , count(a), avg(a)::${PREC},
stddev(a)::${PREC} from (select catcachebench(2) from generate_series(1,
${LOOPS})) as a(a);
$SETTING2
select '${BINARY}' as binary, '3_0' as test, count(a), avg(a)::${PREC},
stddev(a)::${PREC} from (select catcachebench(3) from generate_series(1,
${LOOPS})) as a(a);
$SETTING3
select '${BINARY}' as binary, '1_1' as test, count(a), avg(a)::${PREC},
stddev(a)::${PREC} from (select catcachebench(1) from generate_series(1,
${LOOPS})) as a(a)
UNION ALL select '${BINARY}', '2_1' , count(a), avg(a)::${PREC},
stddev(a)::${PREC} from (select catcachebench(2) from generate_series(1,
${LOOPS})) as a(a)
UNION ALL select '${BINARY}', '3_1' , count(a), avg(a)::${PREC},
stddev(a)::${PREC} from (select catcachebench(3) from generate_series(1,
${LOOPS})) as a(a);
EOF
$PGCTL --pgdata=$DATADIR stop
}
run "master" $BINROOT/pgsql_work_o2 "" "" ""
run "master2" $BINROOT/pgsql_mater_o2m "" "" ""
run "LRU" $BINROOT/pgsql_catexp8_1 "300s" "1s" "0"
run "Full" $BINROOT/pgsql_catexp8_2 "300s" "1s" "0"
run "FullMod" $BINROOT/pgsql_catexp8_3 "300s" "1s" "0"
