Am 03.01.22 um 22:23 schrieb Alvaro Herrera:
Overall I think this is a good feature to have; assessing the need for
compression is important for tuning, so +1 for the goal of the patch.
Much appreciated!
I didn't look into the patch carefully, but here are some minor
comments:
On 2022-Jan-03, Gunnar "Nick" Bluth wrote:
@@ -229,7 +230,9 @@ toast_tuple_try_compression(ToastTupleContext *ttc, int
attribute)
Datum *value = &ttc->ttc_values[attribute];
Datum new_value;
ToastAttrInfo *attr = &ttc->ttc_attr[attribute];
+ instr_time start_time;
+ INSTR_TIME_SET_CURRENT(start_time);
new_value = toast_compress_datum(*value, attr->tai_compression);
if (DatumGetPointer(new_value) != NULL)
Don't INSTR_TIME_SET_CURRENT unconditionally; in some systems it's an
expensive syscall. Find a way to only do it if the feature is enabled.
Yeah, I was worried about that (and asking if it would be required) already.
Adding the check was easier than I expected, though I'm absolutely
clueless if I did it right!
#include "pgstat.h"
extern PGDLLIMPORT bool pgstat_track_toast;
This also suggests that perhaps it'd be a good idea to allow this to be
enabled for specific tables only, rather than system-wide. (Maybe in
order for stats to be collected, the user should have to both set the
GUC option *and* set a per-table option? Not sure.)
That would of course be nice, but I seriously doubt the required
additional logic would be justified. The patch currently tampers with as
few internal structures as possible, and for good reason... ;-)
@@ -82,10 +82,12 @@ typedef enum StatMsgType
PGSTAT_MTYPE_DEADLOCK,
PGSTAT_MTYPE_CHECKSUMFAILURE,
PGSTAT_MTYPE_REPLSLOT,
+ PGSTAT_MTYPE_CONNECTION,
I think this new enum value doesn't belong in this patch.
Yeah, did I mention I'm struggling with rebasing? ;-|
+/* ----------
+ * PgStat_ToastEntry Per-TOAST-column info in a MsgFuncstat
+ * ----------
Not in "a MsgFuncstat", right?
Obviously... fixed!
+-- function to wait for counters to advance
+create function wait_for_stats() returns void as $$
I don't think we want a separate copy of wait_for_stats; see commit
fe60b67250a3 and the discussion leading to it. Maybe you'll want to
move the test to stats.sql. I'm not sure what to say about relying on
Did so.
LZ4; maybe you'll want to leave that part out, and just verify in an
LZ4-enabled build that some 'l' entry exists. BTW, don't we have any
decent way to turn that 'l' into a more reasonable, descriptive string?
Also, perhaps make the view-defining query turn an empty compression
method into whatever the default is.
I'm not even sure that having it in there is useful at all. It's simply
JOINed in from pg_attribute.
Which is where I'd see that "make it look nicer" change happening, tbth. ;-)
Or even better, stats collection
should store the real compression method used rather than empty string,
to avoid confusing things when some stats are collected, then the
default is changed, then some more stats are collected.
I was thinking about that already, but came to the conclusion that it a)
would blow up the size of these statistics by quite a bit and b) would
be quite tricky to display in a useful way.
I mean, the use case of track_toast is pretty limited anyway; you'll
probably turn this feature on with a specific column in mind, of which
you'll probably know which compression method is used at the time.
Thanks for the feedback!
v7 attached.
--
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 f07213d68c646ba64757e551e3587aab0ff221df Mon Sep 17 00:00:00 2001
From: "Gunnar \"Nick\" Bluth" <gunnar.bl...@pro-open.de>
Date: Tue, 4 Jan 2022 12:08:32 +0100
Subject: [PATCH v7] pg_stat_toast v7
---
doc/src/sgml/config.sgml | 26 ++
doc/src/sgml/monitoring.sgml | 163 ++++++++++
doc/src/sgml/storage.sgml | 12 +-
src/backend/access/table/toast_helper.c | 44 ++-
src/backend/catalog/system_views.sql | 20 ++
src/backend/postmaster/pgstat.c | 305 +++++++++++++++++-
src/backend/utils/adt/pgstatfuncs.c | 72 +++++
src/backend/utils/misc/guc.c | 9 +
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/include/catalog/pg_proc.dat | 25 ++
src/include/pgstat.h | 107 ++++++
src/test/regress/expected/rules.out | 17 +
src/test/regress/expected/stats.out | 62 ++++
src/test/regress/sql/stats.sql | 28 ++
14 files changed, 882 insertions(+), 9 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index afbb6c35e3..b2617cc941 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -7668,6 +7668,32 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
</listitem>
</varlistentry>
+ <varlistentry id="guc-track-toast" xreflabel="track_toast">
+ <term><varname>track_toast</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>track_toast</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Enables tracking of <link linkend="storage-toast">TOAST</link> activities.
+ Compressions and externalizations are tracked.
+ The default is <literal>off</literal>.
+ Only superusers can change this setting.
+ </para>
+
+ <note>
+ <para>
+ Be aware that this feature, depending on the amount of TOASTable columns in
+ your databases, may significantly increase the size of the statistics files
+ and the workload of the statistics collector. It is recommended to only
+ temporarily activate this to assess the right compression and storage method
+ for a column.
+ </para>
+ </note>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-stats-temp-directory" xreflabel="stats_temp_directory">
<term><varname>stats_temp_directory</varname> (<type>string</type>)
<indexterm>
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 62f2a3332b..d2ed4e8e7d 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -610,6 +610,17 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
yet included in <structname>pg_stat_user_functions</structname>).</entry>
</row>
+ <row>
+ <entry><structname>pg_stat_toast</structname><indexterm><primary>pg_stat_toast</primary></indexterm></entry>
+ <entry>
+ One row for each column that has ever been TOASTed (compressed and/or externalized).
+ Showing the number of externalizations, compression attempts / successes, compressed and
+ uncompressed sizes etc.
+ <link linkend="monitoring-pg-stat-toast-view">
+ <structname>pg_stat_toast</structname></link> for details.
+ </entry>
+ </row>
+
<row>
<entry><structname>pg_stat_slru</structname><indexterm><primary>pg_stat_slru</primary></indexterm></entry>
<entry>One row per SLRU, showing statistics of operations. See
@@ -4969,6 +4980,158 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
</sect2>
+ <sect2 id="monitoring-pg-stat-toast-view">
+ <title><structname>pg_stat_toast</structname></title>
+
+ <indexterm>
+ <primary>pg_stat_toast</primary>
+ </indexterm>
+
+ <para>
+ The <structname>pg_stat_toast</structname> view will contain
+ one row for each column of variable size that has been TOASTed since
+ the last statistics reset. The <xref linkend="guc-track-toast"/> parameter
+ controls whether TOAST activities are tracked or not.
+ </para>
+
+ <table id="pg-stat-toast-view" xreflabel="pg_stat_toast">
+ <title><structname>pg_stat_toast</structname> View</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>schemaname</structfield> <type>name</type>
+ </para>
+ <para>
+ Name of the schema the relation is in
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>reloid</structfield> <type>oid</type>
+ </para>
+ <para>
+ OID of the relation
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>attnum</structfield> <type>int</type>
+ </para>
+ <para>
+ Attribute (column) number in the relation
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>relname</structfield> <type>name</type>
+ </para>
+ <para>
+ Name of the relation
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>attname</structfield> <type>name</type>
+ </para>
+ <para>
+ Name of the attribute (column)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>storagemethod</structfield> <type>char</type>
+ </para>
+ <para>
+ Storage method of the attribute
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>externalized</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times this attribute was externalized (pushed to TOAST relation)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>compressmethod</structfield> <type>char</type>
+ </para>
+ <para>
+ Current compression method of the attribute (empty means default)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>compressattempts</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times compression of this attribute was attempted
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>compresssuccesses</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times compression of this attribute was successful
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>compressedsize</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Total size of all compressed datums
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>originalsize</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Total size of all compressed datums before compression
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>total_time</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Total time spent TOASTing this attribute, in microseconds.
+ </para></entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect2>
+
<sect2 id="monitoring-pg-stat-slru-view">
<title><structname>pg_stat_slru</structname></title>
diff --git a/doc/src/sgml/storage.sgml b/doc/src/sgml/storage.sgml
index 7136bbe7a3..2a47922573 100644
--- a/doc/src/sgml/storage.sgml
+++ b/doc/src/sgml/storage.sgml
@@ -517,6 +517,15 @@ pages). There was no run time difference compared to an un-<acronym>TOAST</acron
comparison table, in which all the HTML pages were cut down to 7 kB to fit.
</para>
+<para>
+When you enable <xref linkend="guc-track-toast"/>, the system view
+<link linkend="monitoring-pg-stat-toast-view"><structname>pg_stat_toast</structname>
+</link> provides details on the number and effect of compression attempts,
+number of externalizations and some more useful information that enables you
+to decide if a different storage method and/or compression method would suite a
+column better.
+</para>
+
</sect2>
<sect2 id="storage-toast-inmemory">
@@ -1068,7 +1077,8 @@ data. Empty in ordinary tables.</entry>
<type>struct varlena</type>, which includes the total length of the stored
value and some flag bits. Depending on the flags, the data can be either
inline or in a <acronym>TOAST</acronym> table;
- it might be compressed, too (see <xref linkend="storage-toast"/>).
+ it might be compressed, too (see <xref linkend="storage-toast"/> and
+ <xref linkend="monitoring-pg-stat-toast-view"/>).
</para>
</sect2>
diff --git a/src/backend/access/table/toast_helper.c b/src/backend/access/table/toast_helper.c
index 013236b73d..d0a8ca4a1c 100644
--- a/src/backend/access/table/toast_helper.c
+++ b/src/backend/access/table/toast_helper.c
@@ -19,7 +19,9 @@
#include "access/toast_helper.h"
#include "access/toast_internals.h"
#include "catalog/pg_type_d.h"
+#include "pgstat.h"
+extern PGDLLIMPORT bool pgstat_track_toast;
/*
* Prepare to TOAST a tuple.
@@ -226,10 +228,15 @@ toast_tuple_find_biggest_attribute(ToastTupleContext *ttc,
void
toast_tuple_try_compression(ToastTupleContext *ttc, int attribute)
{
- Datum *value = &ttc->ttc_values[attribute];
- Datum new_value;
+ Datum *value = &ttc->ttc_values[attribute];
+ Datum new_value;
ToastAttrInfo *attr = &ttc->ttc_attr[attribute];
+ instr_time start_time;
+ if (pgstat_track_toast)
+ {
+ INSTR_TIME_SET_CURRENT(start_time);
+ }
new_value = toast_compress_datum(*value, attr->tai_compression);
if (DatumGetPointer(new_value) != NULL)
@@ -239,6 +246,15 @@ toast_tuple_try_compression(ToastTupleContext *ttc, int attribute)
pfree(DatumGetPointer(*value));
*value = new_value;
attr->tai_colflags |= TOASTCOL_NEEDS_FREE;
+ if (pgstat_track_toast)
+ {
+ pgstat_report_toast_activity(ttc->ttc_rel->rd_rel->oid, attribute,
+ false,
+ true,
+ attr->tai_size,
+ VARSIZE(DatumGetPointer(*value)),
+ start_time);
+ }
attr->tai_size = VARSIZE(DatumGetPointer(*value));
ttc->ttc_flags |= (TOAST_NEEDS_CHANGE | TOAST_NEEDS_FREE);
}
@@ -246,6 +262,15 @@ toast_tuple_try_compression(ToastTupleContext *ttc, int attribute)
{
/* incompressible, ignore on subsequent compression passes */
attr->tai_colflags |= TOASTCOL_INCOMPRESSIBLE;
+ if (pgstat_track_toast)
+ {
+ pgstat_report_toast_activity(ttc->ttc_rel->rd_rel->oid, attribute,
+ false,
+ true,
+ 0,
+ 0,
+ start_time);
+ }
}
}
@@ -258,6 +283,12 @@ toast_tuple_externalize(ToastTupleContext *ttc, int attribute, int options)
Datum *value = &ttc->ttc_values[attribute];
Datum old_value = *value;
ToastAttrInfo *attr = &ttc->ttc_attr[attribute];
+ instr_time start_time;
+
+ if (pgstat_track_toast)
+ {
+ INSTR_TIME_SET_CURRENT(start_time);
+ }
attr->tai_colflags |= TOASTCOL_IGNORE;
*value = toast_save_datum(ttc->ttc_rel, old_value, attr->tai_oldexternal,
@@ -266,6 +297,15 @@ 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);
+ if (pgstat_track_toast)
+ {
+ pgstat_report_toast_activity(ttc->ttc_rel->rd_rel->oid, attribute,
+ true,
+ false,
+ 0,
+ 0,
+ start_time);
+}
}
/*
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 61b515cdb8..3de3025488 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1039,6 +1039,26 @@ CREATE VIEW pg_stat_user_functions AS
WHERE P.prolang != 12 -- fast check to eliminate built-in functions
AND pg_stat_get_function_calls(P.oid) IS NOT NULL;
+
+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,
+ attstorage AS storagemethod,
+ pg_stat_get_toast_externalizations(a.attrelid,a.attnum) AS externalized,
+ attcompression AS compressmethod,
+ pg_stat_get_toast_compressions(a.attrelid,a.attnum) AS compressattempts,
+ pg_stat_get_toast_compressionsuccesses(a.attrelid,a.attnum) AS compresssuccesses,
+ pg_stat_get_toast_compressedsizesum(a.attrelid,a.attnum) AS compressedsize,
+ pg_stat_get_toast_originalsizesum(a.attrelid,a.attnum) AS originalsize,
+ pg_stat_get_toast_total_time(a.attrelid,a.attnum) AS total_time
+ 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) IS NOT NULL;
CREATE VIEW pg_stat_xact_user_functions AS
SELECT
P.oid AS funcid,
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 7264d2c727..68aa770d21 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 64
#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 = false;
/* ----------
* 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,63 @@ 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)
+{
+ 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 +2228,72 @@ 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,
+ instr_time start_time)
+{
+ PgStat_BackendAttrIdentifier toastattr = { relid, attr };
+ PgStat_BackendToastEntry *htabent;
+ instr_time time_spent;
+ bool found;
+
+ if (pgStatSock == PGINVALID_SOCKET || !pgstat_track_toast)
+ return;
+
+ INSTR_TIME_SET_CURRENT(time_spent);
+ INSTR_TIME_SUBTRACT(time_spent, start_time);
+
+ 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)
+ {
+ MemSet(&htabent->t_counts, 0, sizeof(PgStat_ToastCounts));
+ }
+
+ /* update counters */
+ if (externalized)
+ {
+ htabent->t_counts.t_numexternalized++;
+ }
+ if (compressed)
+ {
+ htabent->t_counts.t_numcompressed++;
+ htabent->t_counts.t_size_orig+=old_size;
+ if (new_size)
+ {
+ htabent->t_counts.t_numcompressionsuccess++;
+ htabent->t_counts.t_size_compressed+=new_size;
+ }
+ }
+ /* record time spent */
+ INSTR_TIME_ADD(htabent->t_counts.t_comp_time, time_spent);
+
+ /* indicate that we have something to send */
+ have_toast_stats = true;
+}
+
/* ----------
* pgstat_initstats() -
@@ -3028,6 +3171,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 +3880,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 +4028,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 +4243,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 +4359,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 +4429,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 +4680,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 +4726,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 +4742,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 +4827,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 +4836,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 +4986,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 +5687,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 +5728,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 +6391,60 @@ 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;
+
+ 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.
+ */
+ toastentry->t_numexternalized = toastmsg->t_numexternalized;
+ toastentry->t_numcompressed = toastmsg->t_numcompressed;
+ toastentry->t_numcompressionsuccess = toastmsg->t_numcompressionsuccess;
+ toastentry->t_size_orig = toastmsg->t_size_orig;
+ toastentry->t_size_compressed = toastmsg->t_size_compressed;
+ toastentry->t_comp_time = toastmsg->t_comp_time;
+ }
+ else
+ {
+ /*
+ * Otherwise add the values to the existing entry.
+ */
+ toastentry->t_numexternalized += toastmsg->t_numexternalized;
+ toastentry->t_numcompressed += toastmsg->t_numcompressed;
+ toastentry->t_numcompressionsuccess += toastmsg->t_numcompressionsuccess;
+ toastentry->t_size_orig += toastmsg->t_size_orig;
+ toastentry->t_size_compressed += toastmsg->t_size_compressed;
+ toastentry->t_comp_time += toastmsg->t_comp_time;
+ }
+ }
+}
+
/* ----------
* pgstat_recv_subworker_error() -
*
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index f529c1561a..8b40c43626 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -410,6 +410,78 @@ 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 - 1)) == 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 - 1)) == 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 - 1)) == 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 - 1)) == 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 - 1)) == NULL)
+ PG_RETURN_NULL();
+ PG_RETURN_INT64(toastentry->t_size_compressed);
+}
+
+Datum
+pg_stat_get_toast_total_time(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 - 1)) == NULL)
+ PG_RETURN_NULL();
+ PG_RETURN_INT64(toastentry->t_comp_time);
+}
+
Datum
pg_stat_get_backend_idset(PG_FUNCTION_ARGS)
{
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index f9504d3aec..f7ff934d4e 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -1538,6 +1538,15 @@ static struct config_bool ConfigureNamesBool[] =
true,
NULL, NULL, NULL
},
+ {
+ {"track_toast", PGC_SUSET, STATS_COLLECTOR,
+ gettext_noop("Collects statistics on TOAST activity."),
+ NULL
+ },
+ &pgstat_track_toast,
+ false,
+ NULL, NULL, NULL
+ },
{
{"track_io_timing", PGC_SUSET, STATS_COLLECTOR,
gettext_noop("Collects timing statistics for database I/O activity."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index a1acd46b61..12f4bb1a38 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -607,6 +607,7 @@
#track_io_timing = off
#track_wal_io_timing = off
#track_functions = none # none, pl, all
+#track_toast = off
#stats_temp_directory = 'pg_stat_tmp'
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 4d992dc224..22c34b96a6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5668,6 +5668,31 @@
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 => '9705', descr => 'statistics: total time spend TOASTing data',
+ proname => 'pg_stat_get_toast_total_time', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid int4',
+ prosrc => 'pg_stat_get_toast_total_time' },
+
{ 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..b65f08de40 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -86,6 +86,7 @@ typedef enum StatMsgType
PGSTAT_MTYPE_DISCONNECT,
PGSTAT_MTYPE_SUBSCRIPTIONPURGE,
PGSTAT_MTYPE_SUBWORKERERROR,
+ PGSTAT_MTYPE_TOASTSTAT,
} StatMsgType;
/* ----------
@@ -733,6 +734,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 MsgToaststat
+ * ----------
+ */
+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 +835,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 +909,7 @@ typedef struct PgStat_StatDBEntry
HTAB *tables;
HTAB *functions;
HTAB *subworkers;
+ HTAB *toastactivity;
} PgStat_StatDBEntry;
@@ -1022,6 +1099,22 @@ 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 +1138,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 +1290,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,
+ instr_time start_time);
+
extern void AtEOXact_PgStat(bool isCommit, bool parallel);
extern void AtEOSubXact_PgStat(bool isCommit, int nestDepth);
@@ -1228,9 +1332,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);
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index b58b062b10..2b49bebcb8 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2147,6 +2147,23 @@ 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_toast| SELECT n.nspname AS schemaname,
+ a.attrelid AS reloid,
+ a.attnum,
+ c.relname,
+ a.attname,
+ a.attstorage AS storagemethod,
+ pg_stat_get_toast_externalizations(a.attrelid, (a.attnum)::integer) AS externalized,
+ a.attcompression AS compressmethod,
+ pg_stat_get_toast_compressions(a.attrelid, (a.attnum)::integer) AS compressattempts,
+ pg_stat_get_toast_compressionsuccesses(a.attrelid, (a.attnum)::integer) AS compresssuccesses,
+ pg_stat_get_toast_compressedsizesum(a.attrelid, (a.attnum)::integer) AS compressedsize,
+ pg_stat_get_toast_originalsizesum(a.attrelid, (a.attnum)::integer) AS originalsize,
+ pg_stat_get_toast_total_time(a.attrelid, (a.attnum)::integer) AS total_time
+ 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)::integer) IS NOT NULL);
pg_stat_user_functions| SELECT p.oid AS funcid,
n.nspname AS schemaname,
p.proname AS funcname,
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 3e9ab0915f..880380aafa 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -11,6 +11,32 @@ SHOW track_counts; -- must be on
on
(1 row)
+-- prepare and fill the pg_stat_toast table now:
+SHOW track_toast;
+ track_toast
+-------------
+ off
+(1 row)
+
+SET track_toast TO on;
+SHOW track_toast;
+ track_toast
+-------------
+ on
+(1 row)
+
+TABLE pg_stat_toast; -- view exists
+ schemaname | reloid | attnum | relname | attname | storagemethod | externalized | compressmethod | compressattempts | compresssuccesses | compressedsize | originalsize | total_time
+------------+--------+--------+---------+---------+---------------+--------------+----------------+------------------+-------------------+----------------+--------------+------------
+(0 rows)
+
+CREATE TABLE toast_test (cola TEXT, colb TEXT, colc TEXT , cold TEXT);
+ALTER TABLE toast_test ALTER colb SET STORAGE EXTERNAL;
+ALTER TABLE toast_test ALTER colc SET STORAGE MAIN;
+ALTER TABLE toast_test ALTER cold SET STORAGE PLAIN;
+INSERT INTO toast_test VALUES (repeat(md5('a'),100), repeat(md5('a'),100), repeat(md5('a'),100), repeat(md5('a'),100) );
+-- make sure we don't interfere with the other tests:
+SET track_toast TO off;
-- ensure that both seqscan and indexscan plans are allowed
SET enable_seqscan TO on;
SET enable_indexscan TO on;
@@ -252,4 +278,40 @@ SELECT pg_stat_get_tuples_hot_updated('brin_hot'::regclass::oid);
DROP TABLE brin_hot;
DROP FUNCTION wait_for_hot_stats();
+-- now check that the track_toast stuff worked:
+SELECT attname
+ ,storagemethod
+ ,externalized
+ ,compressmethod
+ ,compressattempts
+ ,compresssuccesses
+ ,compressedsize < originalsize AS compression_works
+ , total_time > 0 AS takes_time
+FROM pg_stat_toast WHERE relname = 'toast_test' ORDER BY attname;
+ attname | storagemethod | externalized | compressmethod | compressattempts | compresssuccesses | compression_works | takes_time
+---------+---------------+--------------+----------------+------------------+-------------------+-------------------+------------
+ cola | x | 1 | | 1 | 1 | t | t
+ colb | e | 1 | | 0 | 0 | f | t
+ colc | m | 0 | | 1 | 1 | t | t
+(3 rows)
+
+SELECT compressattempts=0 AS external_doesnt_compress FROM pg_stat_toast WHERE relname = 'toast_test' AND storagemethod = 'e';
+ external_doesnt_compress
+--------------------------
+ t
+(1 row)
+
+SELECT externalized=0 AS main_doesnt_externalize FROM pg_stat_toast WHERE relname = 'toast_test' AND storagemethod = 'm';
+ main_doesnt_externalize
+-------------------------
+ t
+(1 row)
+
+DROP TABLE toast_test;
+SELECT count(*) FROM pg_stat_toast WHERE relname = 'toast_test';
+ count
+-------
+ 0
+(1 row)
+
-- End of Stats Test
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 82e6f24c39..ba462537f6 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -8,6 +8,20 @@
-- conditio sine qua non
SHOW track_counts; -- must be on
+-- prepare and fill the pg_stat_toast table now:
+SHOW track_toast;
+SET track_toast TO on;
+SHOW track_toast;
+TABLE pg_stat_toast; -- view exists
+
+CREATE TABLE toast_test (cola TEXT, colb TEXT, colc TEXT , cold TEXT);
+ALTER TABLE toast_test ALTER colb SET STORAGE EXTERNAL;
+ALTER TABLE toast_test ALTER colc SET STORAGE MAIN;
+ALTER TABLE toast_test ALTER cold SET STORAGE PLAIN;
+INSERT INTO toast_test VALUES (repeat(md5('a'),100), repeat(md5('a'),100), repeat(md5('a'),100), repeat(md5('a'),100) );
+-- make sure we don't interfere with the other tests:
+SET track_toast TO off;
+
-- ensure that both seqscan and indexscan plans are allowed
SET enable_seqscan TO on;
SET enable_indexscan TO on;
@@ -225,5 +239,19 @@ SELECT pg_stat_get_tuples_hot_updated('brin_hot'::regclass::oid);
DROP TABLE brin_hot;
DROP FUNCTION wait_for_hot_stats();
+-- now check that the track_toast stuff worked:
+SELECT attname
+ ,storagemethod
+ ,externalized
+ ,compressmethod
+ ,compressattempts
+ ,compresssuccesses
+ ,compressedsize < originalsize AS compression_works
+ , total_time > 0 AS takes_time
+FROM pg_stat_toast WHERE relname = 'toast_test' ORDER BY attname;
+SELECT compressattempts=0 AS external_doesnt_compress FROM pg_stat_toast WHERE relname = 'toast_test' AND storagemethod = 'e';
+SELECT externalized=0 AS main_doesnt_externalize FROM pg_stat_toast WHERE relname = 'toast_test' AND storagemethod = 'm';
+DROP TABLE toast_test;
+SELECT count(*) FROM pg_stat_toast WHERE relname = 'toast_test';
-- End of Stats Test
--
2.32.0
