Am 05.04.22 um 18:17 schrieb Robert Haas:
> On Thu, Mar 31, 2022 at 9:16 AM Gunnar "Nick" Bluth
> <gunnar.bl...@pro-open.de> wrote:
>> That was meant to say "v10", sorry!
>
> Hi,
Hi Robert,
and thx for looking at this.
> From my point of view, at least, it would be preferable if you'd stop
> changing the subject line every time you post a new version.
Terribly sorry, I believed to do the right thing! I removed the "suffix"
now for good.
> Based on the test results in
> http://postgr.es/m/42bfa680-7998-e7dc-b50e-480cdd986...@pro-open.de
> and the comments from Andres in
> https://www.postgresql.org/message-id/20211212234113.6rhmqxi5uzgipwx2%40alap3.anarazel.de
> my judgement would be that, as things stand today, this patch has no
> chance of being accepted, due to overhead. Now, Andres is currently
> working on an overhaul of the statistics collector and perhaps that
> would reduce the overhead of something like this to an acceptable
> level. If it does, that would be great news; I just don't know whether
> that's the case.
AFAICT, Andres' work is more about the structure (e.g.
13619598f1080d7923454634a2570ca1bc0f2fec). Or I've missed something...?
The attached v11 incorporates the latest changes in the area, btw.
Anyway, my (undisputed up to now!) understanding still is that only
backends _looking_ at these stats (so, e.g., accessing the pg_stat_toast
view) actually read the data. So, the 10-15% more space used for pg_stat
only affect the stats collector and _some few_ backends.
And those 10-15% were gathered with 10.000 tables containing *only*
TOASTable attributes. So the actual percentage would probably go down
quite a bit once you add some INTs or such.
Back then, I was curious myself on the impact and just ran a few
syntetic tests quickly hacked together. I'll happily go ahead and run
some tests on real world schemas if that helps clarifying matters!
> As far as the statistics themselves are concerned, I am somewhat
> skeptical about whether it's really worth adding code for this.
> According to the documentation, the purpose of the patch is to allow
> you to assess choice of storage and compression method settings for a
> column and is not intended to be enabled permanently. However, it
TBTH, the wording there is probably a bit over-cautious. I very much
respect Andres and thus his reservations, and I know how careful the
project is about regressions of any kind (see below on some elobarations
on the latter).
I alleviated the <note> part a bit for v11.
> seems to me that you could assess that pretty easily without this
> patch: just create a couple of different tables with different
> settings, load up the same data via COPY into each one, and see what
> happens. Now you might answer that with the patch you would get more
> detailed and accurate statistics, and I think that's true, but it
> doesn't really look like the additional level of detail would be
> critical to have in order to make a proper assessment. You might also
> say that creating multiple copies of the table and loading the data
> multiple times would be expensive, and that's also true, but you don't
> really need to load it all. A representative sample of 1GB or so would
> probably suffice in most cases, and that doesn't seem likely to be a
> huge load on the system.
At the end of the day, one could argue like you did there for almost all
(non-attribute) stats. "Why track function execution times? Just set up
a benchmark and call the function 1 mio times and you'll know how long
it takes on average!". "Why track IO timings? Run a benchmark on your
system and ..." etc. pp.
I maintain a couple of DBs that house TBs of TOASTable data (mainly XML
containing encrypted payloads). In just a couple of columns per cluster.
I'm completely clueless if TOAST compression makes a difference there.
Or externalization.
And I'm not allowed to copy that data anywhere outside production
without unrolling a metric ton of red tape.
Guess why I started writing this patch ;-)
*I* would certainly leave the option on, just to get an idea of what's
happening...
> Also, as we add more compression options, it's going to be hard to
> assess this sort of thing without trying stuff anyway. For example if
> you can set the lz4 compression level, you're not going to know which
> level is actually going to work best without trying out a bunch of
> them and seeing what happens. If we allow access to other sorts of
> compression parameters like zstd's "long" option, similarly, if you
> really care, you're going to have to try it.
Funny that you mention it. When writing the first version, I was
thinking about the LZ4 patch authors and was wondering how they
tested/benchmarked all of it and why they didn't implement something
like this patch for their tests ;-)
Yes, you're gonna try it. And you're gonna measure it. Somehow.
Externally, as things are now.
With pg_stat_toast, you'd get the byte-by-byte and - maybe even more
important - ms-by-ms comparison of the different compression and
externalization strategies straight from the core of the DB.
I'd fancy that!
And if you get these stats by just flicking a switch (or leaving it on
permanently...), you might start looking at the pg_stat_toast view from
time to time, maybe realizing that your DB server spent hours of CPU
time trying to compress data that's compressed already. Or of which you
_know_ that it's only gonna be around for a couple of seconds...
Mind you, a *lot* of people out there aren't even aware that TOAST even
exists. Granted, most probably just don't care... ;-)
Plus: this would (potentially, one day) give us information we could
eventually incorporate into EXPLAIN [ANALYZE]. Like, "estimated time for
(un)compressing TOAST values" or so.
> So my feeling is that this feels like a lot of machinery and a lot of
> worst-case overhead to solve a problem that's really pretty easy to
> solve without any new code at all, and therefore I'd be inclined to
> reject it. However, it's a well-known fact that sometimes my feelings
> about things are pretty stupid, and this might be one of those times.
> If so, I hope someone will enlighten me by telling me what I'm
> missing.
Most DBAs I met will *happily* donate a few CPU cycles (and MBs) to
gather as much first hand information about their *live* systems.
Why is pg_stat_statements so popular? Even if it costs 5-10% CPU
cycles...? If I encounter a tipped-over plan and have a load1 of 1200 on
my production server, running pgbadger on 80GB of (compressed) full
statement logs will just not give me the information I need _now_
(actually, an hour ago). So I happily deploy pg_stat_statements
everywhere, *hoping* that I'll never really need it...
Why is "replica" now the default WAL level? Because essentially
everybody changed it anyway, _just in case_. People looking for the last
couple of % disk space will tune it down to "minimal", for everybody
else, the gain in *options* vastly outweighs the additional disk usage.
Why is everybody asking for live execution plans? Or a progress
indication? The effort to get these is ridiculous from what I know,
still I'd fancy them a lot!
One of my clients is currently spending a lot of time (and thus $$$) to
get some profiling software (forgot the name) for their DB2 to work (and
not push AIX into OOM situations, actually ;). And compared to
PostgreSQL, I'm pretty sure you get a lot more insights from a stock DB2
already. As that's what customers ask for...
In essence: if *I* read in the docs "this will give you useful
information" (and saves you effort for testing it in a seperate
environment) "but may use up some RAM and disk space for pg_stats", I
flick that switch on and probably leave it there.
And in real world applications, you'd almost certainly never note a
difference (we're discussing ~ 50-60 bytes per attribute, afterall).
I reckon most DBAs (and developers) would give this a spin and leave it
on, out of curiosity first and out of sheer convenience later.
Like, if I run a DB relying heavily on stored procedures, I'll certainly
enable "track_functions".
Now show me the DB without any TOASTable attributes! ;-)
TBTH, I imagine this to be a default "on" GUC parameter *eventually*,
which some people with *very* special needs (and braindead schemas
causing the "worst-case overhead" you mention) turn "off". But alas!
that's not how we add features, is it?
Also, I wouldn't call ~ 583 LOC plus docs & tests "a lot of machinery" ;-).
Again, thanks a lot for peeking at this and
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
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 | 40 +++++++
src/backend/catalog/system_views.sql | 20 ++++
src/backend/postmaster/pgstat.c | 156 +++++++++++++++++++++++-
src/backend/utils/activity/Makefile | 1 +
src/backend/utils/activity/pgstat_toast.c | 157 +++++++++++++++++++++++++
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 | 101 ++++++++++++++++
src/include/utils/pgstat_internal.h | 1 +
src/test/regress/expected/rules.out | 17 +++
src/test/regress/expected/stats.out | 62 ++++++++++
src/test/regress/sql/stats.sql | 28 +++++
17 files changed, 886 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 43e4ade83e..544c1d7041 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -7935,6 +7935,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. If your databases have a very high
+ amount of TOASTable colums, it is recommended to only temporarily activate this
+ setting 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 3b9172f65b..cd0a5bea35 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
@@ -4946,6 +4957,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 f4b9f66589..d0e165d5f1 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">
@@ -1069,7 +1078,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 0cc5a30f9b..98d10a0670 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"
+PGDLLIMPORT bool pgstat_track_toast;
/*
* Prepare to TOAST a tuple.
@@ -229,7 +231,12 @@ 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;
+ 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 9eaa51df29..e9cce5c51a 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1066,6 +1066,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 ef1cba61a6..66e1263c44 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -116,7 +116,7 @@ static void pgstat_reset_subscription(PgStat_StatSubEntry *subentry, TimestampTz
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,
bool permanent);
static void backend_read_statsfile(void);
@@ -159,6 +159,7 @@ static void pgstat_recv_replslot(PgStat_MsgReplSlot *msg, int len);
static void pgstat_recv_tempfile(PgStat_MsgTempFile *msg, int len);
static void pgstat_recv_subscription_drop(PgStat_MsgSubscriptionDrop *msg, int len);
static void pgstat_recv_subscription_error(PgStat_MsgSubscriptionError *msg, int len);
+static void pgstat_recv_toaststat(PgStat_MsgToaststat *msg, int len);
/* ----------
@@ -946,6 +947,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);
@@ -1434,6 +1438,33 @@ pgstat_fetch_stat_funcentry(Oid func_id)
return funcentry;
}
+/* ----------
+ * 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;
+}
+
/*
* Support function for the SQL-callable pgstat* functions. Returns
* a pointer to the archiver statistics struct.
@@ -1814,6 +1845,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);
@@ -1950,6 +1985,13 @@ reset_dbentry_counters(PgStat_StatDBEntry *dbentry)
PGSTAT_FUNCTION_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);
}
/*
@@ -2245,7 +2287,7 @@ pgstat_write_statsfiles(bool permanent, bool allDbs)
while ((dbentry = (PgStat_StatDBEntry *) hash_seq_search(&hstat)) != NULL)
{
/*
- * Write out the table and function stats for this DB into the
+ * Write out the table, function and TOAST stats for this DB into the
* appropriate per-DB stat file, if required.
*/
if (allDbs || pgstat_db_requested(dbentry->databaseid))
@@ -2374,8 +2416,10 @@ pgstat_write_db_statsfile(PgStat_StatDBEntry *dbentry, bool permanent)
{
HASH_SEQ_STATUS tstat;
HASH_SEQ_STATUS fstat;
+ HASH_SEQ_STATUS ostat;
PgStat_StatTabEntry *tabentry;
PgStat_StatFuncEntry *funcentry;
+ PgStat_StatToastEntry *toastentry;
FILE *fpout;
int32 format_id;
Oid dbid = dbentry->databaseid;
@@ -2430,6 +2474,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
@@ -2665,6 +2720,7 @@ pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
memcpy(dbentry, &dbbuf, sizeof(PgStat_StatDBEntry));
dbentry->tables = NULL;
dbentry->functions = NULL;
+ dbentry->toastactivity = NULL;
/*
* In the collector, disregard the timestamp we read from the
@@ -2702,6 +2758,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.
@@ -2710,6 +2774,7 @@ pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
pgstat_read_db_statsfile(dbentry->databaseid,
dbentry->tables,
dbentry->functions,
+ dbentry->toastactivity,
permanent);
break;
@@ -2829,13 +2894,15 @@ done:
* at the moment though.
*/
static void
-pgstat_read_db_statsfile(Oid databaseid, HTAB *tabhash, HTAB *funchash,
+pgstat_read_db_statsfile(Oid databaseid, HTAB *tabhash, HTAB *funchash, HTAB *toasthash,
bool permanent)
{
PgStat_StatTabEntry *tabentry;
PgStat_StatTabEntry tabbuf;
PgStat_StatFuncEntry funcbuf;
PgStat_StatFuncEntry *funcentry;
+ PgStat_StatToastEntry toastbuf;
+ PgStat_StatToastEntry *toastentry;
FILE *fpin;
int32 format_id;
bool found;
@@ -2949,6 +3016,32 @@ pgstat_read_db_statsfile(Oid databaseid, HTAB *tabhash, HTAB *funchash,
memcpy(funcentry, &funcbuf, sizeof(funcbuf));
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.
*/
@@ -3602,6 +3695,8 @@ pgstat_recv_dropdb(PgStat_MsgDropdb *msg, int len)
hash_destroy(dbentry->tables);
if (dbentry->functions != NULL)
hash_destroy(dbentry->functions);
+ if (dbentry->toastactivity != NULL)
+ hash_destroy(dbentry->toastactivity);
if (hash_search(pgStatDBHash,
(void *) &dbid,
@@ -3635,6 +3730,8 @@ pgstat_recv_resetcounter(PgStat_MsgResetcounter *msg, int len)
hash_destroy(dbentry->tables);
if (dbentry->functions != NULL)
hash_destroy(dbentry->functions);
+ if (dbentry->toastactivity != NULL)
+ hash_destroy(dbentry->toastactivity);
dbentry->tables = NULL;
dbentry->functions = NULL;
@@ -4233,7 +4330,58 @@ pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len)
}
/*
- * Process a SUBSCRIPTIONDROP message.
+ * 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;
+ }
+ }
+}
+
+/* ----------
+ * Process a SUBSCRIPTIONDROP message.
*/
static void
pgstat_recv_subscription_drop(PgStat_MsgSubscriptionDrop *msg, int len)
diff --git a/src/backend/utils/activity/Makefile b/src/backend/utils/activity/Makefile
index 25a967ab7d..229cdaefd3 100644
--- a/src/backend/utils/activity/Makefile
+++ b/src/backend/utils/activity/Makefile
@@ -26,6 +26,7 @@ OBJS = \
pgstat_subscription.o \
pgstat_wal.o \
pgstat_slru.o \
+ pgstat_toast.o \
wait_event.o
include $(top_srcdir)/src/backend/common.mk
diff --git a/src/backend/utils/activity/pgstat_toast.c b/src/backend/utils/activity/pgstat_toast.c
new file mode 100644
index 0000000000..b6ba62c302
--- /dev/null
+++ b/src/backend/utils/activity/pgstat_toast.c
@@ -0,0 +1,157 @@
+/* -------------------------------------------------------------------------
+ *
+ * pgstat_toast.c
+ * Implementation of TOAST statistics.
+ *
+ * This file contains the implementation of TOAST statistics. It is kept
+ * separate from pgstat.c to enforce the line between the statistics access /
+ * storage implementation and the details about individual types of
+ * statistics.
+ *
+ * Copyright (c) 2001-2022, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * src/backend/utils/activity/pgstat_toast.c
+ * -------------------------------------------------------------------------
+ */
+
+#include "postgres.h"
+
+#include "utils/pgstat_internal.h"
+#include "utils/timestamp.h"
+
+/*
+ * Indicates if backend has some function stats that it hasn't yet
+ * sent to the collector.
+ */
+bool have_toast_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;
+
+/*
+ * 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;
+}
+
+/*
+ * Subroutine for pgstat_report_stat: populate and send a toast stat message
+ */
+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;
+}
+
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index ce84525d40..a1f74c74ff 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 9e8ab1420d..339d2553d4 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -1557,6 +1557,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 93d221a37b..4c3b7ae29b 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -613,6 +613,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 25304430f4..6d7ba55293 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5673,6 +5673,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 3584078f6e..c37d177d12 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -252,6 +252,7 @@ typedef enum StatMsgType
PGSTAT_MTYPE_DISCONNECT,
PGSTAT_MTYPE_SUBSCRIPTIONDROP,
PGSTAT_MTYPE_SUBSCRIPTIONERROR,
+ PGSTAT_MTYPE_TOASTSTAT,
} StatMsgType;
/* ----------
@@ -726,6 +727,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.
* ----------
@@ -754,6 +829,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;
@@ -851,6 +927,7 @@ typedef struct PgStat_StatDBEntry
*/
HTAB *tables;
HTAB *functions;
+ HTAB *toastactivity;
} PgStat_StatDBEntry;
typedef struct PgStat_StatFuncEntry
@@ -906,6 +983,17 @@ typedef struct PgStat_StatSubEntry
TimestampTz stat_reset_timestamp;
} PgStat_StatSubEntry;
+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;
+
typedef struct PgStat_StatTabEntry
{
Oid tableid;
@@ -991,6 +1079,7 @@ extern PgStat_BgWriterStats *pgstat_fetch_stat_bgwriter(void);
extern PgStat_CheckpointerStats *pgstat_fetch_stat_checkpointer(void);
extern PgStat_StatDBEntry *pgstat_fetch_stat_dbentry(Oid dbid);
extern PgStat_StatFuncEntry *pgstat_fetch_stat_funcentry(Oid funcid);
+extern PgStat_StatToastEntry *pgstat_fetch_stat_toastentry(Oid rel_id, int attr);
extern PgStat_GlobalStats *pgstat_fetch_global(void);
extern PgStat_StatReplSlotEntry *pgstat_fetch_replslot(NameData slotname);
extern PgStat_StatSubEntry *pgstat_fetch_stat_subscription(Oid subid);
@@ -1163,6 +1252,17 @@ extern void pgstat_report_subscription_drop(Oid subid);
extern void pgstat_send_wal(bool force);
+/*
+ * Functions in pgstat_toast.c
+ */
+
+extern void pgstat_send_toaststats(void);
+extern void pgstat_report_toast_activity(Oid relid, int attr,
+ bool externalized,
+ bool compressed,
+ int32 old_size,
+ int32 new_size,
+ instr_time start_time);
/*
* Variables in pgstat.c
@@ -1171,6 +1271,7 @@ extern void pgstat_send_wal(bool force);
/* GUC parameters */
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;
diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index abbb4f8d96..44b0aeb7af 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -27,6 +27,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_SUBSCRIPTION_HASH_SIZE 32
#define PGSTAT_REPLSLOT_HASH_SIZE 32
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 423b9b99fb..91358a5b62 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2128,6 +2128,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 06a1d2f229..b9514e1f34 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;
@@ -255,6 +281,42 @@ 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)
+
-- ensure that stats accessors handle NULL input correctly
SELECT pg_stat_get_replication_slot(NULL);
pg_stat_get_replication_slot
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index ae1ec173e3..b30de2fa7a 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;
@@ -228,6 +242,20 @@ 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';
-- ensure that stats accessors handle NULL input correctly
SELECT pg_stat_get_replication_slot(NULL);
