Re: [PATCH] pg_stat_toast v10
On Thu, Mar 31, 2022 at 9:16 AM Gunnar "Nick" Bluth wrote: > That was meant to say "v10", sorry! Hi, >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. 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. 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 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. 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. 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. Thanks, -- Robert Haas EDB: http://www.enterprisedb.com
Re: [PATCH] pg_stat_toast v10
Am 31.03.22 um 15:14 schrieb Gunnar "Nick" Bluth: > 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, That was meant to say "v10", sorry! -- 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
