Re: [PATCH] pg_stat_toast v9
Am 22.03.22 um 02:17 schrieb Andres Freund: > Hi, > > On 2022-03-08 19:32:03 +0100, Gunnar "Nick" Bluth wrote: >> v8 (applies cleanly to today's HEAD/master) attached. > > This doesn't apply anymore, likely due to my recent pgstat changes - which > you'd need to adapt to... Now, that's been quite an overhaul... kudos! > http://cfbot.cputube.org/patch_37_3457.log > > Marked as waiting on author. v9 attached. TBTH, I don't fully understand all the external/static stuff, but it applies to HEAD/master, compiles and passes all tests, so... ;-) 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 | 161 - 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 | 110 - 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, 897 insertions(+), 8 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 7a48973b3c..64b4219ded 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -7892,6 +7892,32 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; + + track_toast (boolean) + + track_toast configuration parameter + + + + +Enables tracking of TOAST activities. +Compressions and externalizations are tracked. +The default is off. +Only superusers can change this setting. + + + + +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. + + + + + stats_temp_directory (string) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 35b2923c5e..7bbacd67fb 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 pg_stat_user_functions). + + pg_stat_toastpg_stat_toast + + 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. + + pg_stat_toast for details. + + + pg_stat_slrupg_stat_slru One row per SLRU, showing statistics of operations. See @@ -4942,6 +4953,158 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i + + pg_stat_toast + + + pg_stat_toast + + + + The pg_stat_toast view will contain + one row for each column of variable size that has been TOASTed since + the last statistics reset. The parameter + controls whether TOAST activities are tracked or not. + + + + pg_stat_toast View + + + + + Column Type + + + Description + + + + + + + + schemaname name + + + Name of the schema the relation is in + + + + + + reloid oid + + + OID of the relation + + + + + + attnum int + + + Attribute (column) number in the relation + + + + + + relname name + + + Name of the relation + + + + + + attname name + + + Name of the attribute (column) + + + + + + storagemethod char + + + Storage method of the attribute + + + + + + externalized bigint + + + Number of ti
Re: [PATCH] pg_stat_toast v9
Am 22.03.22 um 12:23 schrieb Gunnar "Nick" Bluth: > Am 22.03.22 um 02:17 schrieb Andres Freund: >> Hi, >> >> On 2022-03-08 19:32:03 +0100, Gunnar "Nick" Bluth wrote: >>> v8 (applies cleanly to today's HEAD/master) attached. >> >> This doesn't apply anymore, likely due to my recent pgstat changes - which >> you'd need to adapt to... > > Now, that's been quite an overhaul... kudos! > > >> http://cfbot.cputube.org/patch_37_3457.log >> >> Marked as waiting on author. > > v9 attached. > > TBTH, I don't fully understand all the external/static stuff, but it > applies to HEAD/master, compiles and passes all tests, so... ;-) And v10 catches up to master once again. Best, -- 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 | 161 - 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 | 110 - 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, 897 insertions(+), 8 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 43e4ade83e..e6f0768472 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; + + track_toast (boolean) + + track_toast configuration parameter + + + + +Enables tracking of TOAST activities. +Compressions and externalizations are tracked. +The default is off. +Only superusers can change this setting. + + + + +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. + + + + + stats_temp_directory (string) 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 pg_stat_user_functions). + + pg_stat_toastpg_stat_toast + + 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. + + pg_stat_toast for details. + + + pg_stat_slrupg_stat_slru 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 + + pg_stat_toast + + + pg_stat_toast + + + + The pg_stat_toast view will contain + one row for each column of variable size that has been TOASTed since + the last statistics reset. The parameter + controls whether TOAST activities are tracked or not. + + + + pg_stat_toast View + + + + + Column Type + + + Description + + + + + + + + schemaname name + + + Name of the schema the relation is in + + + + + + reloid oid + + + OID of the relation + + + + + + attnum int + + + Attribute (column) number in the relation + + + + + + relname name + + + Name of the relation + + + + + + attname name + + + Name of the attribute (column) + + + + + + storagemethod char + + + Storage method of
