From: Kyotaro Horiguchi Date: Tue, 12 Feb 2019 20:31:16 +0900 Subject: [PATCH 3/3] Syscache usage tracking feature Collects syscache usage statictics and show it using the view pg_stat_syscache. The feature is controlled by the GUC variable track_syscache_usage_interval. --- doc/src/sgml/config.sgml | 16 ++ src/backend/catalog/system_views.sql | 17 +++ src/backend/postmaster/pgstat.c | 201 ++++++++++++++++++++++++-- src/backend/tcop/postgres.c | 23 +++ src/backend/utils/adt/pgstatfuncs.c | 134 +++++++++++++++++ src/backend/utils/cache/catcache.c | 93 +++++++++--- src/backend/utils/cache/syscache.c | 24 +++ src/backend/utils/init/globals.c | 1 + src/backend/utils/init/postinit.c | 11 ++ src/backend/utils/misc/guc.c | 10 ++ src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/catalog/pg_proc.dat | 9 ++ src/include/miscadmin.h | 1 + src/include/pgstat.h | 6 +- src/include/utils/catcache.h | 9 +- src/include/utils/syscache.h | 19 +++ src/include/utils/timeout.h | 1 + src/test/regress/expected/rules.out | 24 ++- 18 files changed, 564 insertions(+), 36 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 71d784b6fe..2eceec1d94 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -6703,6 +6703,22 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; + + track_catalog_cache_usage_interval (integer) + + track_catlog_cache_usage_interval + configuration parameter + + + + + Specifies the interval to collect catalog cache usage statistics on + the session in milliseconds. This parameter is 0 by default, which + means disabled. Only superusers can change this setting. + + + + track_io_timing (boolean) diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 3e229c693c..f5d1aaf96f 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -906,6 +906,22 @@ CREATE VIEW pg_stat_progress_vacuum AS FROM pg_stat_get_progress_info('VACUUM') AS S LEFT JOIN pg_database D ON S.datid = D.oid; +CREATE VIEW pg_stat_syscache AS + SELECT + S.pid AS pid, + S.relid::regclass AS relname, + S.indid::regclass AS cache_name, + S.size AS size, + S.ntup AS ntuples, + S.searches AS searches, + S.hits AS hits, + S.neg_hits AS neg_hits, + S.ageclass AS ageclass, + S.last_update AS last_update + FROM pg_stat_activity A + JOIN LATERAL (SELECT A.pid, * FROM pg_get_syscache_stats(A.pid)) S + ON (A.pid = S.pid); + CREATE VIEW pg_user_mappings AS SELECT U.oid AS umid, @@ -1185,6 +1201,7 @@ GRANT EXECUTE ON FUNCTION pg_ls_waldir() TO pg_monitor; GRANT EXECUTE ON FUNCTION pg_ls_archive_statusdir() TO pg_monitor; GRANT EXECUTE ON FUNCTION pg_ls_tmpdir() TO pg_monitor; GRANT EXECUTE ON FUNCTION pg_ls_tmpdir(oid) TO pg_monitor; +GRANT EXECUTE ON FUNCTION pg_get_syscache_stats(int) TO pg_monitor; GRANT pg_read_all_settings TO pg_monitor; GRANT pg_read_all_stats TO pg_monitor; diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c index 81c6499251..8c4ab0aef9 100644 --- a/src/backend/postmaster/pgstat.c +++ b/src/backend/postmaster/pgstat.c @@ -66,6 +66,7 @@ #include "utils/ps_status.h" #include "utils/rel.h" #include "utils/snapmgr.h" +#include "utils/syscache.h" #include "utils/timestamp.h" @@ -124,6 +125,7 @@ bool pgstat_track_activities = false; bool pgstat_track_counts = false; int pgstat_track_functions = TRACK_FUNC_OFF; +int pgstat_track_syscache_usage_interval = 0; int pgstat_track_activity_query_size = 1024; /* ---------- @@ -236,6 +238,11 @@ typedef struct TwoPhasePgStatRecord bool t_truncated; /* was the relation truncated? */ } TwoPhasePgStatRecord; +/* bitmap symbols to specify target file types remove */ +#define PGSTAT_REMFILE_DBSTAT 1 /* remove only database stats files */ +#define PGSTAT_REMFILE_SYSCACHE 2 /* remove only syscache stats files */ +#define PGSTAT_REMFILE_ALL 3 /* remove both type of files */ + /* * Info about current "snapshot" of stats file */ @@ -335,6 +342,7 @@ static void pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len); static void pgstat_recv_recoveryconflict(PgStat_MsgRecoveryConflict *msg, int len); static void pgstat_recv_deadlock(PgStat_MsgDeadlock *msg, int len); static void pgstat_recv_tempfile(PgStat_MsgTempFile *msg, int len); +static void pgstat_remove_syscache_statsfile(void); /* ------------------------------------------------------------ * Public functions called from postmaster follow @@ -630,10 +638,13 @@ startup_failed: } /* - * subroutine for pgstat_reset_all + * remove stats files + * + * clean up stats files in specified directory. target is one of + * PGSTAT_REFILE_DBSTAT/SYSCACHE/ALL and restricts files to remove. */ static void -pgstat_reset_remove_files(const char *directory) +pgstat_reset_remove_files(const char *directory, int target) { DIR *dir; struct dirent *entry; @@ -644,25 +655,39 @@ pgstat_reset_remove_files(const char *directory) { int nchars; Oid tmp_oid; + int filetype = 0; /* * Skip directory entries that don't match the file names we write. * See get_dbstat_filename for the database-specific pattern. */ if (strncmp(entry->d_name, "global.", 7) == 0) + { + filetype = PGSTAT_REMFILE_DBSTAT; nchars = 7; + } else { + char head[2]; + nchars = 0; - (void) sscanf(entry->d_name, "db_%u.%n", - &tmp_oid, &nchars); - if (nchars <= 0) - continue; + (void) sscanf(entry->d_name, "%c%c_%u.%n", + head, head + 1, &tmp_oid, &nchars); + /* %u allows leading whitespace, so reject that */ - if (strchr("0123456789", entry->d_name[3]) == NULL) + if (nchars < 3 || !isdigit(entry->d_name[3])) continue; + + if (strncmp(head, "db", 2) == 0) + filetype = PGSTAT_REMFILE_DBSTAT; + else if (strncmp(head, "cc", 2) == 0) + filetype = PGSTAT_REMFILE_SYSCACHE; } + /* skip if this is not a target */ + if ((filetype & target) == 0) + continue; + if (strcmp(entry->d_name + nchars, "tmp") != 0 && strcmp(entry->d_name + nchars, "stat") != 0) continue; @@ -683,8 +708,9 @@ pgstat_reset_remove_files(const char *directory) void pgstat_reset_all(void) { - pgstat_reset_remove_files(pgstat_stat_directory); - pgstat_reset_remove_files(PGSTAT_STAT_PERMANENT_DIRECTORY); + pgstat_reset_remove_files(pgstat_stat_directory, PGSTAT_REMFILE_ALL); + pgstat_reset_remove_files(PGSTAT_STAT_PERMANENT_DIRECTORY, + PGSTAT_REMFILE_ALL); } #ifdef EXEC_BACKEND @@ -2963,6 +2989,10 @@ pgstat_beshutdown_hook(int code, Datum arg) if (OidIsValid(MyDatabaseId)) pgstat_report_stat(true); + /* clear syscache statistics files and temporary settings */ + if (MyBackendId != InvalidBackendId) + pgstat_remove_syscache_statsfile(); + /* * Clear my status entry, following the protocol of bumping st_changecount * before and after. We use a volatile pointer here to ensure the @@ -4287,6 +4317,9 @@ PgstatCollectorMain(int argc, char *argv[]) pgStatRunningInCollector = true; pgStatDBHash = pgstat_read_statsfiles(InvalidOid, true, true); + /* Remove left-over syscache stats files */ + pgstat_reset_remove_files(pgstat_stat_directory, PGSTAT_REMFILE_SYSCACHE); + /* * Loop to process messages until we get SIGQUIT or detect ungraceful * death of our parent postmaster. @@ -6377,3 +6410,153 @@ pgstat_clip_activity(const char *raw_activity) return activity; } + +/* + * return the filename for a syscache stat file; filename is the output + * buffer, of length len. + */ +void +pgstat_get_syscachestat_filename(bool permanent, bool tempname, int backendid, + char *filename, int len) +{ + int printed; + + /* NB -- pgstat_reset_remove_files knows about the pattern this uses */ + printed = snprintf(filename, len, "%s/cc_%u.%s", + permanent ? PGSTAT_STAT_PERMANENT_DIRECTORY : + pgstat_stat_directory, + backendid, + tempname ? "tmp" : "stat"); + if (printed >= len) + elog(ERROR, "overlength pgstat path"); +} + +/* removes syscache stats files of this backend */ +static void +pgstat_remove_syscache_statsfile(void) +{ + char fname[MAXPGPATH]; + + pgstat_get_syscachestat_filename(false, false, MyBackendId, + fname, MAXPGPATH); + unlink(fname); /* don't care of the result */ +} + +/* + * pgstat_write_syscache_stats() - + * Write the syscache statistics files. + * + * If 'force' is false, this function skips writing a file and returns the + * time remaining in the current interval in milliseconds. If 'force' is true, + * writes a file regardless of the remaining time and reset the interval. + */ +long +pgstat_write_syscache_stats(bool force) +{ + static TimestampTz last_report = 0; + TimestampTz now; + long elapsed; + long secs; + int usecs; + int cacheId; + FILE *fpout; + char statfile[MAXPGPATH]; + char tmpfile[MAXPGPATH]; + + /* Return if we don't want it */ + if (!force && pgstat_track_syscache_usage_interval <= 0) + { + /* disabled. remove the statistics file if any */ + if (last_report > 0) + { + last_report = 0; + pgstat_remove_syscache_statsfile(); + } + return 0; + } + + /* Check against the interval */ + now = GetCurrentTransactionStopTimestamp(); + TimestampDifference(last_report, now, &secs, &usecs); + elapsed = secs * 1000 + usecs / 1000; + + if (!force && elapsed < pgstat_track_syscache_usage_interval) + { + /* not yet the time, inform the remaining time to the caller */ + return pgstat_track_syscache_usage_interval - elapsed; + } + + /* now update the stats */ + last_report = now; + + pgstat_get_syscachestat_filename(false, true, + MyBackendId, tmpfile, MAXPGPATH); + pgstat_get_syscachestat_filename(false, false, + MyBackendId, statfile, MAXPGPATH); + + /* + * This function can be called from ProcessInterrupts(). Inhibit recursive + * interrupts to avoid recursive entry. + */ + HOLD_INTERRUPTS(); + + fpout = AllocateFile(tmpfile, PG_BINARY_W); + if (fpout == NULL) + { + ereport(LOG, + (errcode_for_file_access(), + errmsg("could not open temporary statistics file \"%s\": %m", + tmpfile))); + /* + * Failure writing this file is not critical. Just skip this time and + * tell caller to wait for the next interval. + */ + RESUME_INTERRUPTS(); + return pgstat_track_syscache_usage_interval; + } + + /* write out every catcache stats */ + for (cacheId = 0 ; cacheId < SysCacheSize ; cacheId++) + { + SysCacheStats *stats; + + stats = SysCacheGetStats(cacheId); + Assert (stats); + + /* write error is checked later using ferror() */ + fputc('T', fpout); + (void)fwrite(&cacheId, sizeof(int), 1, fpout); + (void)fwrite(&last_report, sizeof(TimestampTz), 1, fpout); + (void)fwrite(stats, sizeof(*stats), 1, fpout); + } + fputc('E', fpout); + + if (ferror(fpout)) + { + ereport(LOG, + (errcode_for_file_access(), + errmsg("could not write syscache statistics file \"%s\": %m", + tmpfile))); + FreeFile(fpout); + unlink(tmpfile); + } + else if (FreeFile(fpout) < 0) + { + ereport(LOG, + (errcode_for_file_access(), + errmsg("could not close syscache statistics file \"%s\": %m", + tmpfile))); + unlink(tmpfile); + } + else if (rename(tmpfile, statfile) < 0) + { + ereport(LOG, + (errcode_for_file_access(), + errmsg("could not rename syscache statistics file \"%s\" to \"%s\": %m", + tmpfile, statfile))); + unlink(tmpfile); + } + + RESUME_INTERRUPTS(); + return 0; +} diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index f192ee2ca6..d0afee189f 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -3159,6 +3159,12 @@ ProcessInterrupts(void) } + if (IdleSyscacheStatsUpdateTimeoutPending) + { + IdleSyscacheStatsUpdateTimeoutPending = false; + pgstat_write_syscache_stats(true); + } + if (ParallelMessagePending) HandleParallelMessages(); @@ -3743,6 +3749,7 @@ PostgresMain(int argc, char *argv[], sigjmp_buf local_sigjmp_buf; volatile bool send_ready_for_query = true; bool disable_idle_in_transaction_timeout = false; + bool disable_idle_syscache_update_timeout = false; /* Initialize startup process environment if necessary. */ if (!IsUnderPostmaster) @@ -4186,9 +4193,19 @@ PostgresMain(int argc, char *argv[], } else { + long timeout; + ProcessCompletedNotifies(); pgstat_report_stat(false); + timeout = pgstat_write_syscache_stats(false); + + if (timeout > 0) + { + disable_idle_syscache_update_timeout = true; + enable_timeout_after(IDLE_SYSCACHE_STATS_UPDATE_TIMEOUT, + timeout); + } set_ps_display("idle", false); pgstat_report_activity(STATE_IDLE, NULL); } @@ -4231,6 +4248,12 @@ PostgresMain(int argc, char *argv[], disable_idle_in_transaction_timeout = false; } + if (disable_idle_syscache_update_timeout) + { + disable_timeout(IDLE_SYSCACHE_STATS_UPDATE_TIMEOUT, false); + disable_idle_syscache_update_timeout = false; + } + /* * (6) check for any other interesting events that happened while we * slept. diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index b6ba856ebe..a314f431c6 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -14,6 +14,8 @@ */ #include "postgres.h" +#include + #include "access/htup_details.h" #include "catalog/pg_authid.h" #include "catalog/pg_type.h" @@ -28,6 +30,7 @@ #include "utils/acl.h" #include "utils/builtins.h" #include "utils/inet.h" +#include "utils/syscache.h" #include "utils/timestamp.h" #define UINT32_ACCESS_ONCE(var) ((uint32)(*((volatile uint32 *)&(var)))) @@ -1899,3 +1902,134 @@ pg_stat_get_archiver(PG_FUNCTION_ARGS) PG_RETURN_DATUM(HeapTupleGetDatum( heap_form_tuple(tupdesc, values, nulls))); } + +Datum +pgstat_get_syscache_stats(PG_FUNCTION_ARGS) +{ +#define PG_GET_SYSCACHE_SIZE 9 + int pid = PG_GETARG_INT32(0); + ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; + TupleDesc tupdesc; + Tuplestorestate *tupstore; + MemoryContext per_query_ctx; + MemoryContext oldcontext; + PgBackendStatus *beentry; + int beid; + char fname[MAXPGPATH]; + FILE *fpin; + char c; + + if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("set-valued function called in context that cannot accept a set"))); + if (!(rsinfo->allowedModes & SFRM_Materialize)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("materialize mode required, but it is not " \ + "allowed in this context"))); + + /* Build a tuple descriptor for our result type */ + if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + + + per_query_ctx = rsinfo->econtext->ecxt_per_query_memory; + + oldcontext = MemoryContextSwitchTo(per_query_ctx); + tupstore = tuplestore_begin_heap(true, false, work_mem); + rsinfo->returnMode = SFRM_Materialize; + rsinfo->setResult = tupstore; + rsinfo->setDesc = tupdesc; + + MemoryContextSwitchTo(oldcontext); + + /* find beentry for given pid*/ + beentry = NULL; + for (beid = 1; + (beentry = pgstat_fetch_stat_beentry(beid)) && + beentry->st_procpid != pid ; + beid++); + + /* + * we silently return empty result on failure or insufficient privileges + */ + if (!beentry || + (!has_privs_of_role(GetUserId(), beentry->st_userid) && + !is_member_of_role(GetUserId(), DEFAULT_ROLE_READ_ALL_STATS))) + goto no_data; + + pgstat_get_syscachestat_filename(false, false, beid, fname, MAXPGPATH); + + if ((fpin = AllocateFile(fname, PG_BINARY_R)) == NULL) + { + if (errno != ENOENT) + ereport(WARNING, + (errcode_for_file_access(), + errmsg("could not open statistics file \"%s\": %m", + fname))); + /* also return empty on no statistics file */ + goto no_data; + } + + /* read the statistics file into tuplestore */ + while ((c = fgetc(fpin)) == 'T') + { + TimestampTz last_update; + SysCacheStats stats; + int cacheid; + Datum values[PG_GET_SYSCACHE_SIZE]; + bool nulls[PG_GET_SYSCACHE_SIZE] = {0}; + Datum datums[SYSCACHE_STATS_NAGECLASSES * 2]; + bool arrnulls[SYSCACHE_STATS_NAGECLASSES * 2] = {0}; + int dims[] = {SYSCACHE_STATS_NAGECLASSES, 2}; + int lbs[] = {1, 1}; + ArrayType *arr; + int i, j; + + if (fread(&cacheid, sizeof(int), 1, fpin) != 1 || + fread(&last_update, sizeof(TimestampTz), 1, fpin) != 1 || + fread(&stats, 1, sizeof(stats), fpin) != sizeof(stats)) + { + ereport(WARNING, + (errmsg("corrupted syscache statistics file \"%s\"", + fname))); + goto no_data; + } + + i = 0; + values[i++] = ObjectIdGetDatum(stats.reloid); + values[i++] = ObjectIdGetDatum(stats.indoid); + values[i++] = Int64GetDatum(stats.size); + values[i++] = Int64GetDatum(stats.ntuples); + values[i++] = Int64GetDatum(stats.nsearches); + values[i++] = Int64GetDatum(stats.nhits); + values[i++] = Int64GetDatum(stats.nneg_hits); + + for (j = 0 ; j < SYSCACHE_STATS_NAGECLASSES ; j++) + { + datums[j * 2] = Int32GetDatum((int32) stats.ageclasses[j]); + datums[j * 2 + 1] = Int32GetDatum((int32) stats.nclass_entries[j]); + } + + arr = construct_md_array(datums, arrnulls, 2, dims, lbs, + INT4OID, sizeof(int32), true, 'i'); + values[i++] = PointerGetDatum(arr); + + values[i++] = TimestampTzGetDatum(last_update); + + Assert (i == PG_GET_SYSCACHE_SIZE); + + tuplestore_putvalues(tupstore, tupdesc, values, nulls); + } + + /* check for the end of file. abandon the result if file is broken */ + if (c != 'E' || fgetc(fpin) != EOF) + tuplestore_clear(tupstore); + + FreeFile(fpin); + +no_data: + tuplestore_donestoring(tupstore); + return (Datum) 0; +} diff --git a/src/backend/utils/cache/catcache.c b/src/backend/utils/cache/catcache.c index 0195e19976..fd84e35a6a 100644 --- a/src/backend/utils/cache/catcache.c +++ b/src/backend/utils/cache/catcache.c @@ -109,6 +109,10 @@ static CatCacheHeader *CacheHdr = NULL; /* Clock used to record the last accessed time of a catcache record. */ TimestampTz catcacheclock = 0; +/* age classes for pruning */ +static double ageclass[SYSCACHE_STATS_NAGECLASSES] + = {0.05, 0.1, 1.0, 2.0, 3.0, 0.0}; + static inline HeapTuple SearchCatCacheInternal(CatCache *cache, int nkeys, Datum v1, Datum v2, @@ -640,9 +644,7 @@ CatCacheInvalidate(CatCache *cache, uint32 hashValue) else CatCacheRemoveCTup(cache, ct); CACHE1_elog(DEBUG2, "CatCacheInvalidate: invalidated"); -#ifdef CATCACHE_STATS cache->cc_invals++; -#endif /* could be multiple matches, so keep looking! */ } } @@ -718,9 +720,7 @@ ResetCatalogCache(CatCache *cache) } else CatCacheRemoveCTup(cache, ct); -#ifdef CATCACHE_STATS cache->cc_invals++; -#endif } } } @@ -1032,10 +1032,10 @@ CatCacheCleanupOldEntries(CatCache *cp) 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. + * Calculate the duration from the time from the last access to + * the "current" time. Since catcacheclock is not advanced within + * a transaction, the entries that are accessed within the current + * transaction always get 0 as the result. */ TimestampDifference(ct->lastaccess, catcacheclock, &entry_age, &us); @@ -1463,9 +1463,7 @@ SearchCatCacheInternal(CatCache *cache, if (unlikely(cache->cc_tupdesc == NULL)) CatalogCacheInitializeCache(cache); -#ifdef CATCACHE_STATS cache->cc_searches++; -#endif /* Initialize local parameter array */ arguments[0] = v1; @@ -1535,9 +1533,7 @@ SearchCatCacheInternal(CatCache *cache, CACHE3_elog(DEBUG2, "SearchCatCache(%s): found in bucket %d", cache->cc_relname, hashIndex); -#ifdef CATCACHE_STATS cache->cc_hits++; -#endif return &ct->tuple; } @@ -1546,9 +1542,7 @@ SearchCatCacheInternal(CatCache *cache, CACHE3_elog(DEBUG2, "SearchCatCache(%s): found neg entry in bucket %d", cache->cc_relname, hashIndex); -#ifdef CATCACHE_STATS cache->cc_neg_hits++; -#endif return NULL; } @@ -1676,9 +1670,7 @@ SearchCatCacheMiss(CatCache *cache, CACHE3_elog(DEBUG2, "SearchCatCache(%s): put in bucket %d", cache->cc_relname, hashIndex); -#ifdef CATCACHE_STATS cache->cc_newloads++; -#endif return &ct->tuple; } @@ -1789,9 +1781,7 @@ SearchCatCacheList(CatCache *cache, Assert(nkeys > 0 && nkeys < cache->cc_nkeys); -#ifdef CATCACHE_STATS cache->cc_lsearches++; -#endif /* Initialize local parameter array */ arguments[0] = v1; @@ -1848,9 +1838,7 @@ SearchCatCacheList(CatCache *cache, CACHE2_elog(DEBUG2, "SearchCatCacheList(%s): found list", cache->cc_relname); -#ifdef CATCACHE_STATS cache->cc_lhits++; -#endif return cl; } @@ -2373,3 +2361,68 @@ PrintCatCacheListLeakWarning(CatCList *list) list->my_cache->cc_relname, list->my_cache->id, list, list->refcount); } + +/* + * CatCacheGetStats - fill in SysCacheStats struct. + * + * This is a support routine for SysCacheGetStats, substantially fills in the + * result. The classification here is based on the same criteria to + * CatCacheCleanupOldEntries(). + */ +void +CatCacheGetStats(CatCache *cache, SysCacheStats *stats) +{ + int i, j; + + Assert(ageclass[SYSCACHE_STATS_NAGECLASSES - 1] == 0.0); + + /* fill in the stats struct */ + stats->size = cache->cc_memusage; + stats->ntuples = cache->cc_ntup; + stats->nsearches = cache->cc_searches; + stats->nhits = cache->cc_hits; + stats->nneg_hits = cache->cc_neg_hits; + + /* + * catalog_cache_prune_min_age can be changed on-session, fill it every + * time + */ + for (i = 0 ; i < SYSCACHE_STATS_NAGECLASSES ; i++) + stats->ageclasses[i] = + (int) (catalog_cache_prune_min_age * ageclass[i]); + + /* + * nth element in nclass_entries stores the number of cache entries that + * have lived unaccessed for corresponding multiple in ageclass of + * catalog_cache_prune_min_age. + */ + memset(stats->nclass_entries, 0, sizeof(int) * SYSCACHE_STATS_NAGECLASSES); + + /* Scan the whole hash */ + for (i = 0; i < cache->cc_nbuckets; i++) + { + dlist_mutable_iter iter; + + dlist_foreach_modify(iter, &cache->cc_bucket[i]) + { + CatCTup *ct = dlist_container(CatCTup, cache_elem, iter.cur); + long entry_age; + int us; + + /* + * Calculate the duration from the time from 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); + + j = 0; + while (j < SYSCACHE_STATS_NAGECLASSES - 1 && + entry_age > stats->ageclasses[j]) + j++; + + stats->nclass_entries[j]++; + } + } +} diff --git a/src/backend/utils/cache/syscache.c b/src/backend/utils/cache/syscache.c index ac98c19155..7b38a06708 100644 --- a/src/backend/utils/cache/syscache.c +++ b/src/backend/utils/cache/syscache.c @@ -20,6 +20,9 @@ */ #include "postgres.h" +#include +#include + #include "access/htup_details.h" #include "access/sysattr.h" #include "catalog/indexing.h" @@ -1534,6 +1537,27 @@ RelationSupportsSysCache(Oid relid) return false; } +/* + * SysCacheGetStats - returns stats of specified syscache + * + * This routine returns the address of its local static memory. + */ +SysCacheStats * +SysCacheGetStats(int cacheId) +{ + static SysCacheStats stats; + + Assert(cacheId >=0 && cacheId < SysCacheSize); + + memset(&stats, 0, sizeof(stats)); + + stats.reloid = cacheinfo[cacheId].reloid; + stats.indoid = cacheinfo[cacheId].indoid; + + CatCacheGetStats(SysCache[cacheId], &stats); + + return &stats; +} /* * OID comparator for pg_qsort diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c index 0e8b972a29..b7c647b5e0 100644 --- a/src/backend/utils/init/globals.c +++ b/src/backend/utils/init/globals.c @@ -33,6 +33,7 @@ volatile sig_atomic_t ProcDiePending = false; volatile sig_atomic_t ClientConnectionLost = false; volatile sig_atomic_t IdleInTransactionSessionTimeoutPending = false; volatile sig_atomic_t CatcacheClockTimeoutPending = false; +volatile sig_atomic_t IdleSyscacheStatsUpdateTimeoutPending = false; volatile sig_atomic_t ConfigReloadPending = false; volatile uint32 InterruptHoldoffCount = 0; volatile uint32 QueryCancelHoldoffCount = 0; diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c index 9eb50e9676..2f3251e8d5 100644 --- a/src/backend/utils/init/postinit.c +++ b/src/backend/utils/init/postinit.c @@ -73,6 +73,7 @@ static void StatementTimeoutHandler(void); static void LockTimeoutHandler(void); static void IdleInTransactionSessionTimeoutHandler(void); static void CatcacheClockTimeoutHandler(void); +static void IdleSyscacheStatsUpdateTimeoutHandler(void); static bool ThereIsAtLeastOneRole(void); static void process_startup_options(Port *port, bool am_superuser); static void process_settings(Oid databaseid, Oid roleid); @@ -631,6 +632,8 @@ InitPostgres(const char *in_dbname, Oid dboid, const char *username, IdleInTransactionSessionTimeoutHandler); RegisterTimeout(CATCACHE_CLOCK_TIMEOUT, CatcacheClockTimeoutHandler); + RegisterTimeout(IDLE_SYSCACHE_STATS_UPDATE_TIMEOUT, + IdleSyscacheStatsUpdateTimeoutHandler); } /* @@ -1249,6 +1252,14 @@ CatcacheClockTimeoutHandler(void) SetLatch(MyLatch); } +static void +IdleSyscacheStatsUpdateTimeoutHandler(void) +{ + IdleSyscacheStatsUpdateTimeoutPending = true; + InterruptPending = true; + SetLatch(MyLatch); +} + /* * Returns true if at least one role is defined in this database cluster. */ diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index c62d5ad8b8..7f1670fa5b 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -3178,6 +3178,16 @@ static struct config_int ConfigureNamesInt[] = NULL, NULL, NULL }, + { + {"track_catalog_cache_usage_interval", PGC_SUSET, STATS_COLLECTOR, + gettext_noop("Sets the interval between syscache usage collection, in milliseconds. Zero disables syscache usage tracking."), + NULL + }, + &pgstat_track_syscache_usage_interval, + 0, 0, INT_MAX / 2, + NULL, NULL, NULL + }, + { {"gin_pending_list_limit", PGC_USERSET, CLIENT_CONN_STATEMENT, gettext_noop("Sets the maximum size of the pending list for GIN index."), diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index aeb5968e75..797f52fa2a 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -556,6 +556,7 @@ #track_io_timing = off #track_functions = none # none, pl, all #track_activity_query_size = 1024 # (change requires restart) +#track_catlog_cache_usage_interval = 0 # zero disables tracking #stats_temp_directory = 'pg_stat_tmp' diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 24f99f7fc4..fc35b6be47 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -9689,6 +9689,15 @@ proargmodes => '{o,o,o,o,o,o,o,o,o,o,o}', proargnames => '{slot_name,plugin,slot_type,datoid,temporary,active,active_pid,xmin,catalog_xmin,restart_lsn,confirmed_flush_lsn}', prosrc => 'pg_get_replication_slots' }, +{ oid => '3425', + descr => 'syscache statistics', + proname => 'pg_get_syscache_stats', prorows => '100', proisstrict => 'f', + proretset => 't', provolatile => 'v', prorettype => 'record', + proargtypes => 'int4', + proallargtypes => '{int4,oid,oid,int8,int8,int8,int8,int8,_int4,timestamptz}', + proargmodes => '{i,o,o,o,o,o,o,o,o,o}', + proargnames => '{pid,relid,indid,size,ntup,searches,hits,neg_hits,ageclass,last_update}', + prosrc => 'pgstat_get_syscache_stats' }, { oid => '3786', descr => 'set up a logical replication slot', proname => 'pg_create_logical_replication_slot', provolatile => 'v', proparallel => 'u', prorettype => 'record', proargtypes => 'name name bool', diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h index 33b800e80f..767c94a63c 100644 --- a/src/include/miscadmin.h +++ b/src/include/miscadmin.h @@ -83,6 +83,7 @@ extern PGDLLIMPORT volatile sig_atomic_t QueryCancelPending; extern PGDLLIMPORT volatile sig_atomic_t ProcDiePending; extern PGDLLIMPORT volatile sig_atomic_t IdleInTransactionSessionTimeoutPending; extern PGDLLIMPORT volatile sig_atomic_t CatcacheClockTimeoutPending; +extern PGDLLIMPORT volatile sig_atomic_t IdleSyscacheStatsUpdateTimeoutPending; extern PGDLLIMPORT volatile sig_atomic_t ConfigReloadPending; extern PGDLLIMPORT volatile sig_atomic_t ClientConnectionLost; diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 88a75fb798..b6bfd7d644 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -1144,6 +1144,7 @@ extern bool pgstat_track_activities; extern bool pgstat_track_counts; extern int pgstat_track_functions; extern PGDLLIMPORT int pgstat_track_activity_query_size; +extern int pgstat_track_syscache_usage_interval; extern char *pgstat_stat_directory; extern char *pgstat_stat_tmpname; extern char *pgstat_stat_filename; @@ -1228,7 +1229,8 @@ extern PgStat_BackendFunctionEntry *find_funcstat_entry(Oid func_id); extern void pgstat_initstats(Relation rel); extern char *pgstat_clip_activity(const char *raw_activity); - +extern void pgstat_get_syscachestat_filename(bool permanent, + bool tempname, int backendid, char *filename, int len); /* ---------- * pgstat_report_wait_start() - * @@ -1363,5 +1365,5 @@ extern PgStat_StatFuncEntry *pgstat_fetch_stat_funcentry(Oid funcid); extern int pgstat_fetch_stat_numbackends(void); extern PgStat_ArchiverStats *pgstat_fetch_stat_archiver(void); extern PgStat_GlobalStats *pgstat_fetch_global(void); - +extern long pgstat_write_syscache_stats(bool force); #endif /* PGSTAT_H */ diff --git a/src/include/utils/catcache.h b/src/include/utils/catcache.h index 0425fc0786..8e477090e2 100644 --- a/src/include/utils/catcache.h +++ b/src/include/utils/catcache.h @@ -68,10 +68,8 @@ typedef struct catcache int cc_nfreeent; /* # of entries currently not referenced */ /* - * Keep these at the end, so that compiling catcache.c with CATCACHE_STATS - * doesn't break ABI for other modules + * Statistics entries */ -#ifdef CATCACHE_STATS long cc_searches; /* total # searches against this cache */ long cc_hits; /* # of matches against existing entry */ long cc_neg_hits; /* # of matches against negative entry */ @@ -84,7 +82,6 @@ typedef struct catcache long cc_invals; /* # of entries invalidated from cache */ long cc_lsearches; /* total # list-searches */ long cc_lhits; /* # of matches against existing lists */ -#endif } CatCache; @@ -275,4 +272,8 @@ extern void PrepareToInvalidateCacheTuple(Relation relation, extern void PrintCatCacheLeakWarning(HeapTuple tuple); extern void PrintCatCacheListLeakWarning(CatCList *list); +/* defined in syscache.h */ +typedef struct syscachestats SysCacheStats; +extern void CatCacheGetStats(CatCache *cache, SysCacheStats *syscachestats); + #endif /* CATCACHE_H */ diff --git a/src/include/utils/syscache.h b/src/include/utils/syscache.h index 95ee48954e..71b399c902 100644 --- a/src/include/utils/syscache.h +++ b/src/include/utils/syscache.h @@ -112,6 +112,24 @@ enum SysCacheIdentifier #define SysCacheSize (USERMAPPINGUSERSERVER + 1) }; +#define SYSCACHE_STATS_NAGECLASSES 6 +/* Struct for catcache tracking information */ +typedef struct syscachestats +{ + Oid reloid; /* target relation */ + Oid indoid; /* index */ + size_t size; /* size of the catcache */ + int ntuples; /* number of tuples resides in the catcache */ + int nsearches; /* number of searches */ + int nhits; /* number of cache hits */ + int nneg_hits; /* number of negative cache hits */ + /* age classes in seconds */ + int ageclasses[SYSCACHE_STATS_NAGECLASSES]; + /* number of tuples fall into the corresponding age class */ + int nclass_entries[SYSCACHE_STATS_NAGECLASSES]; +} SysCacheStats; + + extern void InitCatalogCache(void); extern void InitCatalogCachePhase2(void); @@ -164,6 +182,7 @@ extern void SysCacheInvalidate(int cacheId, uint32 hashValue); extern bool RelationInvalidatesSnapshotsOnly(Oid relid); extern bool RelationHasSysCache(Oid relid); extern bool RelationSupportsSysCache(Oid relid); +extern SysCacheStats *SysCacheGetStats(int cacheId); /* * The use of the macros below rather than direct calls to the corresponding diff --git a/src/include/utils/timeout.h b/src/include/utils/timeout.h index b2d97b4f7b..0677978923 100644 --- a/src/include/utils/timeout.h +++ b/src/include/utils/timeout.h @@ -32,6 +32,7 @@ typedef enum TimeoutId STANDBY_LOCK_TIMEOUT, IDLE_IN_TRANSACTION_SESSION_TIMEOUT, CATCACHE_CLOCK_TIMEOUT, + IDLE_SYSCACHE_STATS_UPDATE_TIMEOUT, /* First user-definable timeout reason */ USER_TIMEOUT, /* Maximum number of timeout reasons */ diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 2c8e21baa7..7bd77e9972 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1921,6 +1921,28 @@ pg_stat_sys_tables| SELECT pg_stat_all_tables.relid, pg_stat_all_tables.autoanalyze_count FROM pg_stat_all_tables WHERE ((pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text)); +pg_stat_syscache| SELECT s.pid, + (s.relid)::regclass AS relname, + (s.indid)::regclass AS cache_name, + s.size, + s.ntup AS ntuples, + s.searches, + s.hits, + s.neg_hits, + s.ageclass, + s.last_update + FROM (pg_stat_activity a + JOIN LATERAL ( SELECT a.pid, + pg_get_syscache_stats.relid, + pg_get_syscache_stats.indid, + pg_get_syscache_stats.size, + pg_get_syscache_stats.ntup, + pg_get_syscache_stats.searches, + pg_get_syscache_stats.hits, + pg_get_syscache_stats.neg_hits, + pg_get_syscache_stats.ageclass, + pg_get_syscache_stats.last_update + FROM pg_get_syscache_stats(a.pid) pg_get_syscache_stats(relid, indid, size, ntup, searches, hits, neg_hits, ageclass, last_update)) s ON ((a.pid = s.pid))); pg_stat_user_functions| SELECT p.oid AS funcid, n.nspname AS schemaname, p.proname AS funcname, @@ -2352,7 +2374,7 @@ pg_settings|pg_settings_n|CREATE RULE pg_settings_n AS ON UPDATE TO pg_catalog.pg_settings DO INSTEAD NOTHING; pg_settings|pg_settings_u|CREATE RULE pg_settings_u AS ON UPDATE TO pg_catalog.pg_settings - WHERE (new.name = old.name) DO SELECT set_config(old.name, new.setting, false) AS set_config; + WHERE (new.name = old.name) DO SELECT set_config(old.name, new.setting, false, false) AS set_config; rtest_emp|rtest_emp_del|CREATE RULE rtest_emp_del AS ON DELETE TO public.rtest_emp DO INSERT INTO rtest_emplog (ename, who, action, newsal, oldsal) VALUES (old.ename, CURRENT_USER, 'fired'::bpchar, '$0.00'::money, old.salary); -- 2.16.3 ----Next_Part(Tue_Feb_12_20_36_28_2019_578)----