Hello -hackers!
Please have a look at the attached patch, which implements some
statistics for TOAST.
The idea (and patch) have been lurking here for quite a while now, so I
decided to dust it off, rebase it to HEAD and send it out for review today.
A big shoutout to Georgios Kokolatos, who gave me a crash course in PG
hacking, some very useful hints and valueable feedback early this year.
I'd like to get some feedback about the general idea, approach, naming
etc. before refining this further.
I'm not a C person and I s**k at git, so please be kind with me! ;-)
Also, I'm not subscribed here, so a CC would be much appreciated!
Why gather TOAST statistics?
============================
TOAST is transparent and opaque at the same time.
Whilst we know that it's there and we know _that_ it works, we cannot
generally tell _how well_ it works.
What we can't answer (easily) are questions like e.g.
- how many datums have been externalized?
- how many datums have been compressed?
- how often has a compression failed (resulted in no space saving)?
- how effective is the compression algorithm used on a column?
- how much time did the DB spend compressing/decompressing TOAST values?
The patch adds some functionality that will eventually be able to answer
these (and probably more) questions.
Currently, #1 - #4 can be answered based on the view contained in
"pg_stats_toast.sql":
postgres=# CREATE TABLE test (i int, lz4 text COMPRESSION lz4, std text);
postgres=# INSERT INTO test SELECT
i,repeat(md5(i::text),100),repeat(md5(i::text),100) FROM
generate_series(0,100000) x(i);
postgres=# SELECT * FROM pg_stat_toast WHERE schemaname = 'public';
-[ RECORD 1 ]--------+----------
schemaname | public
reloid | 16829
attnum | 2
relname | test
attname | lz4
externalizations | 0
compressions | 100001
compressionsuccesses | 100001
compressionsizesum | 6299710
originalsizesum | 320403204
-[ RECORD 2 ]--------+----------
schemaname | public
reloid | 16829
attnum | 3
relname | test
attname | std
externalizations | 0
compressions | 100001
compressionsuccesses | 100001
compressionsizesum | 8198819
originalsizesum | 320403204
Implementation
==============
I added some callbacks in backend/access/table/toast_helper.c to
"pgstat_report_toast_activity" in backend/postmaster/pgstat.c.
The latter (and the other additions there) are essentially 1:1 copies of
the function statistics.
Those were the perfect template, as IMHO the TOAST activities (well,
what we're interested in at least) are very much comparable to function
calls:
a) It doesn't really matter if the TOASTed data was committed, as "the
damage is done" (i.e. CPU cycles were used) anyway
b) The information can (thus/best) be stored on DB level, no need to
touch the relation or attribute statistics
I didn't find anything that could have been used as a hash key, so the
PgStat_StatToastEntry
uses the shiny new
PgStat_BackendAttrIdentifier
(containing relid Oid, attr int).
For persisting in the statsfile, I chose the identifier 'O' (as 'T' was
taken).
What's working?
===============
- Gathering of TOAST externalization and compression events
- collecting the sizes before and after compression
- persisting in statsfile
- not breaking "make check"
- not crashing anything (afaict)
What's missing (yet)?
===============
- proper definition of the "pgstat_track_toast" GUC
- Gathering of times (for compression [and decompression?])
- improve "pg_stat_toast" view and include it in the catalog
- documentation (obviously)
- proper naming (of e.g. the hash key type, functions, view columns etc.)
- would it be necessary to implement overflow protection for the size &
time sums?
Thanks in advance & best regards,
--
Gunnar "Nick" Bluth
Eimermacherweg 106
D-48159 Münster
Mobil +49 172 8853339
Email: gunnar.bl...@pro-open.de
__________________________________________________________________________
"Ceterum censeo SystemD esse delendam" - Cato
From 05f81229fd2e81b8674649c8fd1e3857e2406fcd Mon Sep 17 00:00:00 2001
From: "Gunnar \"Nick\" Bluth" <gunnar.bl...@pro-open.de>
Date: Sun, 12 Dec 2021 15:35:35 +0100
Subject: [PATCH] initial patch of pg_stat_toast for -hackers
---
pg_stat_toast.sql | 19 ++
src/backend/access/table/toast_helper.c | 19 ++
src/backend/postmaster/pgstat.c | 311 +++++++++++++++++++++++-
src/backend/utils/adt/pgstatfuncs.c | 60 +++++
src/include/catalog/pg_proc.dat | 21 ++
src/include/pgstat.h | 109 +++++++++
6 files changed, 533 insertions(+), 6 deletions(-)
create mode 100644 pg_stat_toast.sql
diff --git a/pg_stat_toast.sql b/pg_stat_toast.sql
new file mode 100644
index 0000000000..1c653254ab
--- /dev/null
+++ b/pg_stat_toast.sql
@@ -0,0 +1,19 @@
+-- This creates a useable view, but the offset of 1 is annoying.
+-- That "-1" is probably better done in the helper functions...
+
+CREATE OR REPLACE VIEW pg_stat_toast AS
+ SELECT
+ n.nspname AS schemaname,
+ a.attrelid AS reloid,
+ a.attnum AS attnum,
+ c.relname AS relname,
+ a.attname AS attname,
+ pg_stat_get_toast_externalizations(a.attrelid,a.attnum -1) AS externalizations,
+ pg_stat_get_toast_compressions(a.attrelid,a.attnum -1) AS compressions,
+ pg_stat_get_toast_compressionsuccesses(a.attrelid,a.attnum -1) AS compressionsuccesses,
+ pg_stat_get_toast_compressedsizesum(a.attrelid,a.attnum -1) AS compressionsizesum,
+ pg_stat_get_toast_originalsizesum(a.attrelid,a.attnum -1) AS originalsizesum
+ FROM pg_attribute a
+ JOIN pg_class c ON c.oid = a.attrelid
+ LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
+ WHERE pg_stat_get_toast_externalizations(a.attrelid,a.attnum -1) IS NOT NULL;
diff --git a/src/backend/access/table/toast_helper.c b/src/backend/access/table/toast_helper.c
index 013236b73d..49545885d5 100644
--- a/src/backend/access/table/toast_helper.c
+++ b/src/backend/access/table/toast_helper.c
@@ -19,6 +19,7 @@
#include "access/toast_helper.h"
#include "access/toast_internals.h"
#include "catalog/pg_type_d.h"
+#include "pgstat.h"
/*
@@ -239,6 +240,12 @@ toast_tuple_try_compression(ToastTupleContext *ttc, int attribute)
pfree(DatumGetPointer(*value));
*value = new_value;
attr->tai_colflags |= TOASTCOL_NEEDS_FREE;
+ pgstat_report_toast_activity(ttc->ttc_rel->rd_rel->oid, attribute,
+ false,
+ true,
+ attr->tai_size,
+ VARSIZE(DatumGetPointer(*value)),
+ 0);
attr->tai_size = VARSIZE(DatumGetPointer(*value));
ttc->ttc_flags |= (TOAST_NEEDS_CHANGE | TOAST_NEEDS_FREE);
}
@@ -246,6 +253,12 @@ toast_tuple_try_compression(ToastTupleContext *ttc, int attribute)
{
/* incompressible, ignore on subsequent compression passes */
attr->tai_colflags |= TOASTCOL_INCOMPRESSIBLE;
+ pgstat_report_toast_activity(ttc->ttc_rel->rd_rel->oid, attribute,
+ false,
+ true,
+ 0,
+ 0,
+ 0);
}
}
@@ -266,6 +279,12 @@ toast_tuple_externalize(ToastTupleContext *ttc, int attribute, int options)
pfree(DatumGetPointer(old_value));
attr->tai_colflags |= TOASTCOL_NEEDS_FREE;
ttc->ttc_flags |= (TOAST_NEEDS_CHANGE | TOAST_NEEDS_FREE);
+ pgstat_report_toast_activity(ttc->ttc_rel->rd_rel->oid, attribute,
+ true,
+ false,
+ 0,
+ 0,
+ 0);
}
/*
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 7264d2c727..bf29e748e2 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -106,6 +106,7 @@
#define PGSTAT_DB_HASH_SIZE 16
#define PGSTAT_TAB_HASH_SIZE 512
#define PGSTAT_FUNCTION_HASH_SIZE 512
+#define PGSTAT_TOAST_HASH_SIZE 512
#define PGSTAT_SUBWORKER_HASH_SIZE 32
#define PGSTAT_REPLSLOT_HASH_SIZE 32
@@ -116,6 +117,7 @@
*/
bool pgstat_track_counts = false;
int pgstat_track_functions = TRACK_FUNC_OFF;
+bool pgstat_track_toast = true;
/* ----------
* Built from GUC parameter
@@ -228,6 +230,19 @@ static HTAB *pgStatFunctions = NULL;
*/
static bool have_function_stats = false;
+/*
+ * Backends store per-toast-column info that's waiting to be sent to the collector
+ * in this hash table (indexed by column's PgStat_BackendAttrIdentifier).
+ */
+static HTAB *pgStatToastActions = NULL;
+
+
+/*
+ * Indicates if backend has some toast stats that it hasn't yet
+ * sent to the collector.
+ */
+static bool have_toast_stats = false;
+
/*
* Tuple insertion/deletion counts for an open transaction can't be propagated
* into PgStat_TableStatus counters until we know if it is going to commit
@@ -328,7 +343,7 @@ static PgStat_StatSubWorkerEntry *pgstat_get_subworker_entry(PgStat_StatDBEntry
static void pgstat_write_statsfiles(bool permanent, bool allDbs);
static void pgstat_write_db_statsfile(PgStat_StatDBEntry *dbentry, bool permanent);
static HTAB *pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep);
-static void pgstat_read_db_statsfile(Oid databaseid, HTAB *tabhash, HTAB *funchash,
+static void pgstat_read_db_statsfile(Oid databaseid, HTAB *tabhash, HTAB *funchash, HTAB *toasthash,
HTAB *subworkerhash, bool permanent);
static void backend_read_statsfile(void);
@@ -340,6 +355,7 @@ static void pgstat_reset_replslot(PgStat_StatReplSlotEntry *slotstats, Timestamp
static void pgstat_send_tabstat(PgStat_MsgTabstat *tsmsg, TimestampTz now);
static void pgstat_send_funcstats(void);
+static void pgstat_send_toaststats(void);
static void pgstat_send_slru(void);
static void pgstat_send_subscription_purge(PgStat_MsgSubscriptionPurge *msg);
static HTAB *pgstat_collect_oids(Oid catalogid, AttrNumber anum_oid);
@@ -373,6 +389,7 @@ static void pgstat_recv_wal(PgStat_MsgWal *msg, int len);
static void pgstat_recv_slru(PgStat_MsgSLRU *msg, int len);
static void pgstat_recv_funcstat(PgStat_MsgFuncstat *msg, int len);
static void pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len);
+static void pgstat_recv_toaststat(PgStat_MsgToaststat *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_checksum_failure(PgStat_MsgChecksumFailure *msg, int len);
@@ -891,7 +908,7 @@ pgstat_report_stat(bool disconnect)
pgStatXactCommit == 0 && pgStatXactRollback == 0 &&
pgWalUsage.wal_records == prevWalUsage.wal_records &&
WalStats.m_wal_write == 0 && WalStats.m_wal_sync == 0 &&
- !have_function_stats && !disconnect)
+ !have_function_stats && !have_toast_stats && !disconnect)
return;
/*
@@ -983,6 +1000,9 @@ pgstat_report_stat(bool disconnect)
/* Now, send function statistics */
pgstat_send_funcstats();
+ /* Now, send TOAST statistics */
+ pgstat_send_toaststats();
+
/* Send WAL statistics */
pgstat_send_wal(true);
@@ -1116,6 +1136,64 @@ pgstat_send_funcstats(void)
have_function_stats = false;
}
+/*
+ * Subroutine for pgstat_report_stat: populate and send a toast stat message
+ */
+static void
+pgstat_send_toaststats(void)
+{
+ /* we assume this inits to all zeroes: */
+ static const PgStat_ToastCounts all_zeroes;
+
+ PgStat_MsgToaststat msg;
+ PgStat_BackendToastEntry *entry;
+ HASH_SEQ_STATUS tstat;
+
+ if (pgStatToastActions == NULL)
+ return;
+
+ pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_TOASTSTAT);
+ msg.m_databaseid = MyDatabaseId;
+ msg.m_nentries = 0;
+
+ hash_seq_init(&tstat, pgStatToastActions);
+ while ((entry = (PgStat_BackendToastEntry *) hash_seq_search(&tstat)) != NULL)
+ {
+ PgStat_ToastEntry *m_ent;
+
+ /* Skip it if no counts accumulated since last time */
+ if (memcmp(&entry->t_counts, &all_zeroes,
+ sizeof(PgStat_ToastCounts)) == 0)
+ continue;
+
+ /* need to convert format of time accumulators */
+ m_ent = &msg.m_entry[msg.m_nentries];
+ m_ent->attr = entry->attr;
+ m_ent->t_numexternalized = entry->t_counts.t_numexternalized;
+ m_ent->t_numcompressed = entry->t_counts.t_numcompressed;
+ m_ent->t_numcompressionsuccess = entry->t_counts.t_numcompressionsuccess;
+ m_ent->t_size_orig = entry->t_counts.t_size_orig;
+ m_ent->t_size_compressed = entry->t_counts.t_size_compressed;
+ m_ent->t_comp_time = INSTR_TIME_GET_MICROSEC(entry->t_counts.t_comp_time);
+
+ if (++msg.m_nentries >= PGSTAT_NUM_TOASTENTRIES)
+ {
+ pgstat_send(&msg, offsetof(PgStat_MsgToaststat, m_entry[0]) +
+ msg.m_nentries * sizeof(PgStat_ToastEntry));
+ msg.m_nentries = 0;
+ }
+
+ /* reset the entry's counts */
+ MemSet(&entry->t_counts, 0, sizeof(PgStat_ToastCounts));
+ }
+
+ if (msg.m_nentries > 0)
+ pgstat_send(&msg, offsetof(PgStat_MsgToaststat, m_entry[0]) +
+ msg.m_nentries * sizeof(PgStat_ToastEntry));
+
+ have_toast_stats = false;
+}
+
/* ----------
* pgstat_vacuum_stat() -
@@ -2151,6 +2229,76 @@ pgstat_end_function_usage(PgStat_FunctionCallUsage *fcu, bool finalize)
have_function_stats = true;
}
+/*
+ * Report TOAST activity
+ * Called by toast_helper functions.
+ */
+void
+pgstat_report_toast_activity(Oid relid, int attr,
+ bool externalized,
+ bool compressed,
+ int32 old_size,
+ int32 new_size,
+ int32 time_spent)
+{
+ PgStat_BackendAttrIdentifier toastattr = { relid, attr };
+ PgStat_BackendToastEntry *htabent;
+ bool found;
+
+ if (pgStatSock == PGINVALID_SOCKET || !pgstat_track_toast)
+ return;
+
+ if (!pgStatToastActions)
+ {
+ /* First time through - initialize toast stat table */
+ HASHCTL hash_ctl;
+
+ hash_ctl.keysize = sizeof(PgStat_BackendAttrIdentifier);
+ hash_ctl.entrysize = sizeof(PgStat_BackendToastEntry);
+ pgStatToastActions = hash_create("TOAST stat entries",
+ PGSTAT_TOAST_HASH_SIZE,
+ &hash_ctl,
+ HASH_ELEM | HASH_BLOBS);
+ }
+
+ /* Get the stats entry for this TOAST attribute, create if necessary */
+ htabent = hash_search(pgStatToastActions, &toastattr,
+ HASH_ENTER, &found);
+ if (!found)
+ {
+ elog(DEBUG2, "No toast entry found for attr %u of relation %u", attr, relid);
+ MemSet(&htabent->t_counts, 0, sizeof(PgStat_ToastCounts));
+ }
+
+ /* update counters */
+ if (externalized)
+ {
+ htabent->t_counts.t_numexternalized++;
+ elog(DEBUG2, "Externalized counter raised for OID %u, attr %u, now %li", relid,attr, htabent->t_counts.t_numexternalized);
+ }
+ if (compressed)
+ {
+ htabent->t_counts.t_numcompressed++;
+ elog(DEBUG2, "Compressed counter raised for OID %u, attr %u, now %li", relid,attr, htabent->t_counts.t_numcompressed);
+ if (new_size)
+ {
+ htabent->t_counts.t_size_orig+=old_size;
+ elog(DEBUG2, "Old size %u added for OID %u, attr %u, now %li",old_size,relid,attr, htabent->t_counts.t_size_orig);
+ if (new_size)
+ {
+ htabent->t_counts.t_numcompressionsuccess++;
+ elog(DEBUG2, "Compressed success counter raised for OID %u, attr %u, now %li",relid,attr, htabent->t_counts.t_numcompressionsuccess);
+ htabent->t_counts.t_size_compressed+=new_size;
+ elog(DEBUG2, "New size %u added for OID %u, attr %u, now %li",new_size,relid,attr, htabent->t_counts.t_size_compressed);
+ }
+ }
+ /* TODO: record times */
+ }
+
+ /* indicate that we have something to send */
+ have_toast_stats = true;
+}
+
/* ----------
* pgstat_initstats() -
@@ -3028,6 +3176,35 @@ pgstat_fetch_stat_subworker_entry(Oid subid, Oid subrelid)
return wentry;
}
+/* ----------
+ * pgstat_fetch_stat_toastentry() -
+ *
+ * Support function for the SQL-callable pgstat* functions. Returns
+ * the collected statistics for one TOAST attribute or NULL.
+ * ----------
+ */
+PgStat_StatToastEntry *
+pgstat_fetch_stat_toastentry(Oid rel_id, int attr)
+{
+ PgStat_StatDBEntry *dbentry;
+ PgStat_BackendAttrIdentifier toast_id = { rel_id, attr };
+ PgStat_StatToastEntry *toastentry = NULL;
+
+ /* load the stats file if needed */
+ backend_read_statsfile();
+
+ /* Lookup our database, then find the requested TOAST activity stats. */
+ dbentry = pgstat_fetch_stat_dbentry(MyDatabaseId);
+ if (dbentry != NULL && dbentry->toastactivity != NULL)
+ {
+ toastentry = (PgStat_StatToastEntry *) hash_search(dbentry->toastactivity,
+ (void *) &toast_id,
+ HASH_FIND, NULL);
+ }
+
+ return toastentry;
+}
+
/*
* ---------
* pgstat_fetch_stat_archiver() -
@@ -3708,6 +3885,10 @@ PgstatCollectorMain(int argc, char *argv[])
pgstat_recv_funcpurge(&msg.msg_funcpurge, len);
break;
+ case PGSTAT_MTYPE_TOASTSTAT:
+ pgstat_recv_toaststat(&msg.msg_toaststat, len);
+ break;
+
case PGSTAT_MTYPE_RECOVERYCONFLICT:
pgstat_recv_recoveryconflict(&msg.msg_recoveryconflict,
len);
@@ -3852,6 +4033,14 @@ reset_dbentry_counters(PgStat_StatDBEntry *dbentry)
PGSTAT_SUBWORKER_HASH_SIZE,
&hash_ctl,
HASH_ELEM | HASH_BLOBS);
+
+ hash_ctl.keysize = sizeof(PgStat_BackendAttrIdentifier);
+ hash_ctl.entrysize = sizeof(PgStat_StatToastEntry);
+ dbentry->toastactivity = hash_create("Per-database TOAST",
+ PGSTAT_TOAST_HASH_SIZE,
+ &hash_ctl,
+ HASH_ELEM | HASH_BLOBS);
+
}
/*
@@ -4059,8 +4248,8 @@ pgstat_write_statsfiles(bool permanent, bool allDbs)
while ((dbentry = (PgStat_StatDBEntry *) hash_seq_search(&hstat)) != NULL)
{
/*
- * Write out the table, function, and subscription-worker stats for
- * this DB into the appropriate per-DB stat file, if required.
+ * Write out the table, function, TOAST and subscription-worker stats for this DB into the
+ * appropriate per-DB stat file, if required.
*/
if (allDbs || pgstat_db_requested(dbentry->databaseid))
{
@@ -4175,9 +4364,11 @@ pgstat_write_db_statsfile(PgStat_StatDBEntry *dbentry, bool permanent)
HASH_SEQ_STATUS tstat;
HASH_SEQ_STATUS fstat;
HASH_SEQ_STATUS sstat;
+ HASH_SEQ_STATUS ostat;
PgStat_StatTabEntry *tabentry;
PgStat_StatFuncEntry *funcentry;
PgStat_StatSubWorkerEntry *subwentry;
+ PgStat_StatToastEntry *toastentry;
FILE *fpout;
int32 format_id;
Oid dbid = dbentry->databaseid;
@@ -4243,6 +4434,17 @@ pgstat_write_db_statsfile(PgStat_StatDBEntry *dbentry, bool permanent)
(void) rc; /* we'll check for error with ferror */
}
+ /*
+ * Walk through the database's TOAST stats table.
+ */
+ hash_seq_init(&ostat, dbentry->toastactivity);
+ while ((toastentry = (PgStat_StatToastEntry *) hash_seq_search(&ostat)) != NULL)
+ {
+ fputc('O', fpout);
+ rc = fwrite(toastentry, sizeof(PgStat_StatToastEntry), 1, fpout);
+ (void) rc; /* we'll check for error with ferror */
+ }
+
/*
* No more output to be done. Close the temp file and replace the old
* pgstat.stat with it. The ferror() check replaces testing for error
@@ -4483,6 +4685,7 @@ pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
dbentry->tables = NULL;
dbentry->functions = NULL;
dbentry->subworkers = NULL;
+ dbentry->toastactivity = NULL;
/*
* In the collector, disregard the timestamp we read from the
@@ -4528,6 +4731,14 @@ pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
&hash_ctl,
HASH_ELEM | HASH_BLOBS | HASH_CONTEXT);
+ hash_ctl.keysize = sizeof(PgStat_BackendAttrIdentifier);
+ hash_ctl.entrysize = sizeof(PgStat_StatToastEntry);
+ hash_ctl.hcxt = pgStatLocalContext;
+ dbentry->toastactivity = hash_create("Per-database toast information",
+ PGSTAT_TOAST_HASH_SIZE,
+ &hash_ctl,
+ HASH_ELEM | HASH_BLOBS | HASH_CONTEXT);
+
/*
* If requested, read the data from the database-specific
* file. Otherwise we just leave the hashtables empty.
@@ -4536,6 +4747,7 @@ pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
pgstat_read_db_statsfile(dbentry->databaseid,
dbentry->tables,
dbentry->functions,
+ dbentry->toastactivity,
dbentry->subworkers,
permanent);
@@ -4620,7 +4832,7 @@ done:
* ----------
*/
static void
-pgstat_read_db_statsfile(Oid databaseid, HTAB *tabhash, HTAB *funchash,
+pgstat_read_db_statsfile(Oid databaseid, HTAB *tabhash, HTAB *funchash, HTAB *toasthash,
HTAB *subworkerhash, bool permanent)
{
PgStat_StatTabEntry *tabentry;
@@ -4629,6 +4841,8 @@ pgstat_read_db_statsfile(Oid databaseid, HTAB *tabhash, HTAB *funchash,
PgStat_StatFuncEntry *funcentry;
PgStat_StatSubWorkerEntry subwbuf;
PgStat_StatSubWorkerEntry *subwentry;
+ PgStat_StatToastEntry toastbuf;
+ PgStat_StatToastEntry *toastentry;
FILE *fpin;
int32 format_id;
bool found;
@@ -4777,6 +4991,32 @@ pgstat_read_db_statsfile(Oid databaseid, HTAB *tabhash, HTAB *funchash,
memcpy(subwentry, &subwbuf, sizeof(subwbuf));
break;
+
+ /*
+ * 'O' A PgStat_StatToastEntry follows (tOast)
+ */
+ case 'O':
+ if (fread(&toastbuf, 1, sizeof(PgStat_StatToastEntry),
+ fpin) != sizeof(PgStat_StatToastEntry))
+ {
+ ereport(pgStatRunningInCollector ? LOG : WARNING,
+ (errmsg("corrupted statistics file \"%s\"",
+ statfile)));
+ goto done;
+ }
+
+ /*
+ * Skip if TOAST data not wanted.
+ */
+ if (toasthash == NULL)
+ break;
+
+ toastentry = (PgStat_StatToastEntry *) hash_search(toasthash,
+ (void *) &toastbuf.t_id,
+ HASH_ENTER, &found);
+ memcpy(toastentry, &toastbuf, sizeof(toastbuf));
+ break;
+
/*
* 'E' The EOF marker of a complete stats file.
*/
@@ -5452,6 +5692,8 @@ pgstat_recv_dropdb(PgStat_MsgDropdb *msg, int len)
hash_destroy(dbentry->functions);
if (dbentry->subworkers != NULL)
hash_destroy(dbentry->subworkers);
+ if (dbentry->toastactivity != NULL)
+ hash_destroy(dbentry->toastactivity);
if (hash_search(pgStatDBHash,
(void *) &dbid,
@@ -5491,10 +5733,12 @@ pgstat_recv_resetcounter(PgStat_MsgResetcounter *msg, int len)
hash_destroy(dbentry->functions);
if (dbentry->subworkers != NULL)
hash_destroy(dbentry->subworkers);
-
+ if (dbentry->toastactivity != NULL)
+ hash_destroy(dbentry->toastactivity);
dbentry->tables = NULL;
dbentry->functions = NULL;
dbentry->subworkers = NULL;
+ dbentry->toastactivity = NULL;
/*
* Reset database-level stats, too. This creates empty hash tables for
@@ -6152,6 +6396,61 @@ pgstat_recv_subscription_purge(PgStat_MsgSubscriptionPurge *msg, int len)
}
}
+/* ----------
+ * pgstat_recv_toaststat() -
+ *
+ * Count what the backend has done.
+ * ----------
+ */
+static void
+pgstat_recv_toaststat(PgStat_MsgToaststat *msg, int len)
+{
+ PgStat_ToastEntry *toastmsg = &(msg->m_entry[0]);
+ PgStat_StatDBEntry *dbentry;
+ PgStat_StatToastEntry *toastentry;
+ int i;
+ bool found;
+
+ elog(DEBUG2, "Received TOAST statistics...");
+ dbentry = pgstat_get_db_entry(msg->m_databaseid, true);
+
+ /*
+ * Process all TOAST entries in the message.
+ */
+ for (i = 0; i < msg->m_nentries; i++, toastmsg++)
+ {
+ toastentry = (PgStat_StatToastEntry *) hash_search(dbentry->toastactivity,
+ (void *) &(toastmsg->attr),
+ HASH_ENTER, &found);
+
+ if (!found)
+ {
+ /*
+ * If it's a new entry, initialize counters to the values
+ * we just got.
+ */
+ elog(DEBUG2, "First time I see this toastentry");
+ toastentry->t_numexternalized = toastmsg->t_numexternalized;
+ toastentry->t_numcompressed = toastmsg->t_numcompressed;
+ toastentry->t_numcompressionsuccess = toastmsg->t_numcompressionsuccess;
+ toastentry->t_size_compressed = toastmsg->t_size_compressed;
+ toastentry->t_size_orig = toastmsg->t_size_orig;
+ }
+ else
+ {
+ /*
+ * Otherwise add the values to the existing entry.
+ */
+ elog(DEBUG2, "Found this toastentry, updating");
+ toastentry->t_numexternalized += toastmsg->t_numexternalized;
+ toastentry->t_numcompressed += toastmsg->t_numcompressed;
+ toastentry->t_numcompressionsuccess += toastmsg->t_numcompressionsuccess;
+ toastentry->t_size_compressed += toastmsg->t_size_compressed;
+ toastentry->t_size_orig += toastmsg->t_size_orig;
+ }
+ }
+}
+
/* ----------
* pgstat_recv_subworker_error() -
*
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index f529c1561a..bbdcbe14ee 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -410,6 +410,66 @@ pg_stat_get_function_self_time(PG_FUNCTION_ARGS)
PG_RETURN_FLOAT8(((double) funcentry->f_self_time) / 1000.0);
}
+Datum
+pg_stat_get_toast_externalizations(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ int attr = PG_GETARG_INT16(1);
+ PgStat_StatToastEntry *toastentry;
+
+ if ((toastentry = pgstat_fetch_stat_toastentry(relid,attr)) == NULL)
+ PG_RETURN_NULL();
+ PG_RETURN_INT64(toastentry->t_numexternalized);
+}
+
+Datum
+pg_stat_get_toast_compressions(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ int attr = PG_GETARG_INT16(1);
+ PgStat_StatToastEntry *toastentry;
+
+ if ((toastentry = pgstat_fetch_stat_toastentry(relid,attr)) == NULL)
+ PG_RETURN_NULL();
+ PG_RETURN_INT64(toastentry->t_numcompressed);
+}
+
+Datum
+pg_stat_get_toast_compressionsuccesses(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ int attr = PG_GETARG_INT16(1);
+ PgStat_StatToastEntry *toastentry;
+
+ if ((toastentry = pgstat_fetch_stat_toastentry(relid,attr)) == NULL)
+ PG_RETURN_NULL();
+ PG_RETURN_INT64(toastentry->t_numcompressionsuccess);
+}
+
+Datum
+pg_stat_get_toast_originalsizesum(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ int attr = PG_GETARG_INT16(1);
+ PgStat_StatToastEntry *toastentry;
+
+ if ((toastentry = pgstat_fetch_stat_toastentry(relid,attr)) == NULL)
+ PG_RETURN_NULL();
+ PG_RETURN_INT64(toastentry->t_size_orig);
+}
+
+Datum
+pg_stat_get_toast_compressedsizesum(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ int attr = PG_GETARG_INT16(1);
+ PgStat_StatToastEntry *toastentry;
+
+ if ((toastentry = pgstat_fetch_stat_toastentry(relid,attr)) == NULL)
+ PG_RETURN_NULL();
+ PG_RETURN_INT64(toastentry->t_size_compressed);
+}
+
Datum
pg_stat_get_backend_idset(PG_FUNCTION_ARGS)
{
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 79d787cd26..16ea25f433 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5686,6 +5686,27 @@
proparallel => 'r', prorettype => 'float8', proargtypes => 'oid',
prosrc => 'pg_stat_get_function_self_time' },
+{ oid => '9700', descr => 'statistics: number of TOAST externalizations',
+ proname => 'pg_stat_get_toast_externalizations', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid int4',
+ prosrc => 'pg_stat_get_toast_externalizations' },
+{ oid => '9701', descr => 'statistics: number of TOAST compressions',
+ proname => 'pg_stat_get_toast_compressions', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid int4',
+ prosrc => 'pg_stat_get_toast_compressions' },
+ { oid => '9702', descr => 'statistics: number of successful TOAST compressions',
+ proname => 'pg_stat_get_toast_compressionsuccesses', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid int4',
+ prosrc => 'pg_stat_get_toast_compressionsuccesses' },
+{ oid => '9703', descr => 'statistics: total original size of compressed TOAST data',
+ proname => 'pg_stat_get_toast_originalsizesum', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid int4',
+ prosrc => 'pg_stat_get_toast_originalsizesum' },
+{ oid => '9704', descr => 'statistics: total compressed size of compressed TOAST data',
+ proname => 'pg_stat_get_toast_compressedsizesum', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid int4',
+ prosrc => 'pg_stat_get_toast_compressedsizesum' },
+
{ oid => '3037',
descr => 'statistics: number of scans done for table/index in current transaction',
proname => 'pg_stat_get_xact_numscans', provolatile => 'v',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 5b51b58e5a..81b410e612 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -82,10 +82,12 @@ typedef enum StatMsgType
PGSTAT_MTYPE_DEADLOCK,
PGSTAT_MTYPE_CHECKSUMFAILURE,
PGSTAT_MTYPE_REPLSLOT,
+ PGSTAT_MTYPE_CONNECTION,
PGSTAT_MTYPE_CONNECT,
PGSTAT_MTYPE_DISCONNECT,
PGSTAT_MTYPE_SUBSCRIPTIONPURGE,
PGSTAT_MTYPE_SUBWORKERERROR,
+ PGSTAT_MTYPE_TOASTSTAT,
} StatMsgType;
/* ----------
@@ -733,6 +735,80 @@ typedef struct PgStat_MsgDisconnect
SessionEndType m_cause;
} PgStat_MsgDisconnect;
+/* ----------
+ * PgStat_BackendAttrIdentifier Identifier for a single attribute/column (OID + attr)
+ * Used as a hashable identifier for (e.g.) TOAST columns
+ * ----------
+ */
+typedef struct PgStat_BackendAttrIdentifier
+{
+ Oid relid;
+ int attr;
+} PgStat_BackendAttrIdentifier;
+
+/* ----------
+ * PgStat_ToastCounts The actual per-TOAST counts kept by a backend
+ *
+ * This struct should contain only actual event counters, because we memcmp
+ * it against zeroes to detect whether there are any counts to transmit.
+ *
+ * Note that the time counters are in instr_time format here. We convert to
+ * microseconds in PgStat_Counter format when transmitting to the collector.
+ * ----------
+ */
+typedef struct PgStat_ToastCounts
+{
+ PgStat_Counter t_numexternalized;
+ PgStat_Counter t_numcompressed;
+ PgStat_Counter t_numcompressionsuccess;
+ uint64 t_size_orig;
+ uint64 t_size_compressed;
+ instr_time t_comp_time;
+} PgStat_ToastCounts;
+
+/* ----------
+ * PgStat_BackendToastEntry Entry in backend's per-toast-attr hash table
+ * ----------
+ */
+typedef struct PgStat_BackendToastEntry
+{
+ PgStat_BackendAttrIdentifier attr;
+ PgStat_ToastCounts t_counts;
+} PgStat_BackendToastEntry;
+
+/* ----------
+ * PgStat_ToastEntry Per-TOAST-column info in a MsgFuncstat
+ * ----------
+ */
+typedef struct PgStat_ToastEntry
+{
+ PgStat_BackendAttrIdentifier attr;
+ PgStat_Counter t_numexternalized;
+ PgStat_Counter t_numcompressed;
+ PgStat_Counter t_numcompressionsuccess;
+ uint64 t_size_orig;
+ uint64 t_size_compressed;
+ PgStat_Counter t_comp_time; /* time in microseconds */
+} PgStat_ToastEntry;
+
+/* ----------
+ * PgStat_MsgToaststat Sent by the backend to report function
+ * usage statistics.
+ * ----------
+ */
+#define PGSTAT_NUM_TOASTENTRIES \
+ ((PGSTAT_MSG_PAYLOAD - sizeof(Oid) - sizeof(int)) \
+ / sizeof(PgStat_ToastEntry))
+
+typedef struct PgStat_MsgToaststat
+{
+ PgStat_MsgHdr m_hdr;
+ Oid m_databaseid;
+ int m_nentries;
+ PgStat_ToastEntry m_entry[PGSTAT_NUM_TOASTENTRIES];
+} PgStat_MsgToaststat;
+
+
/* ----------
* PgStat_Msg Union over all possible messages.
* ----------
@@ -760,6 +836,7 @@ typedef union PgStat_Msg
PgStat_MsgSLRU msg_slru;
PgStat_MsgFuncstat msg_funcstat;
PgStat_MsgFuncpurge msg_funcpurge;
+ PgStat_MsgToaststat msg_toaststat;
PgStat_MsgRecoveryConflict msg_recoveryconflict;
PgStat_MsgDeadlock msg_deadlock;
PgStat_MsgTempFile msg_tempfile;
@@ -833,6 +910,7 @@ typedef struct PgStat_StatDBEntry
HTAB *tables;
HTAB *functions;
HTAB *subworkers;
+ HTAB *toastactivity;
} PgStat_StatDBEntry;
@@ -1022,6 +1100,23 @@ typedef struct PgStat_StatSubWorkerEntry
char last_error_message[PGSTAT_SUBWORKERERROR_MSGLEN];
} PgStat_StatSubWorkerEntry;
+/* ----------
+ * PgStat_StatToastEntry The collector's data per TOAST attribute
+ * ----------
+ */
+typedef struct PgStat_StatToastEntry
+{
+ PgStat_BackendAttrIdentifier t_id;
+ PgStat_Counter t_numexternalized;
+ PgStat_Counter t_numcompressed;
+ PgStat_Counter t_numcompressionsuccess;
+ uint64 t_size_orig;
+ uint64 t_size_compressed;
+
+ PgStat_Counter t_comp_time; /* time in microseconds */
+} PgStat_StatToastEntry;
+
+
/*
* Working state needed to accumulate per-function-call timing statistics.
*/
@@ -1045,6 +1140,7 @@ typedef struct PgStat_FunctionCallUsage
*/
extern PGDLLIMPORT bool pgstat_track_counts;
extern PGDLLIMPORT int pgstat_track_functions;
+extern PGDLLIMPORT bool pgstat_track_toast;
extern char *pgstat_stat_directory;
extern char *pgstat_stat_tmpname;
extern char *pgstat_stat_filename;
@@ -1196,12 +1292,22 @@ extern void pgstat_count_heap_delete(Relation rel);
extern void pgstat_count_truncate(Relation rel);
extern void pgstat_update_heap_dead_tuples(Relation rel, int delta);
+extern void pgstat_count_toast_insert(Relation rel, PgStat_Counter n);
+
struct FunctionCallInfoBaseData;
extern void pgstat_init_function_usage(struct FunctionCallInfoBaseData *fcinfo,
PgStat_FunctionCallUsage *fcu);
extern void pgstat_end_function_usage(PgStat_FunctionCallUsage *fcu,
bool finalize);
+extern void
+pgstat_report_toast_activity(Oid relid, int attr,
+ bool externalized,
+ bool compressed,
+ int32 old_size,
+ int32 new_size,
+ int32 time_spent);
+
extern void AtEOXact_PgStat(bool isCommit, bool parallel);
extern void AtEOSubXact_PgStat(bool isCommit, int nestDepth);
@@ -1228,9 +1334,12 @@ extern PgStat_StatTabEntry *pgstat_fetch_stat_tabentry(Oid relid);
extern PgStat_StatFuncEntry *pgstat_fetch_stat_funcentry(Oid funcid);
extern PgStat_StatSubWorkerEntry *pgstat_fetch_stat_subworker_entry(Oid subid,
Oid subrelid);
+extern PgStat_StatToastEntry *pgstat_fetch_stat_toastentry(Oid rel_id, int attr);
+extern PgStat_ArchiverStats *pgstat_fetch_stat_archiver(void);
extern PgStat_ArchiverStats *pgstat_fetch_stat_archiver(void);
extern PgStat_BgWriterStats *pgstat_fetch_stat_bgwriter(void);
extern PgStat_CheckpointerStats *pgstat_fetch_stat_checkpointer(void);
+
extern PgStat_GlobalStats *pgstat_fetch_global(void);
extern PgStat_WalStats *pgstat_fetch_stat_wal(void);
extern PgStat_SLRUStats *pgstat_fetch_slru(void);
--
2.32.0
