Re: [PATCH] pg_stat_toast - Mailing list pgsql-hackers

Am 13.12.21 um 00:41 schrieb Andres Freund:
> Hi,
> 
> On 2021-12-13 00:00:23 +0100, Gunnar "Nick" Bluth wrote:
>> Regarding stats size; it adds one PgStat_BackendToastEntry
>> (PgStat_BackendAttrIdentifier + PgStat_ToastCounts, should be 56-64 bytes or
>> something in that ballpark) per TOASTable attribute, I can't see that make
>> any system break sweat ;-)
> 
> That's actually a lot. The problem is that all the stats data for a database
> is loaded into private memory for each connection to that database, and that
> the stats collector regularly writes out all the stats data for a database.

My understanding is that the stats file is only pulled into the backend 
when the SQL functions (for the view) are used (see 
"pgstat_fetch_stat_toastentry()").

Otherwise, a backend just initializes an empty hash, right?

Of which I reduced the initial size from 512 to 32 for the below tests 
(I guess the "truth" lies somewhere in between here), along with making 
the GUC parameter an actual GUC parameter and disabling the elog() calls 
I scattered all over the place ;-) for the v0.2 patch attached.

>> A quick run comparing 1.000.000 INSERTs (2 TOASTable columns each) with and
>> without "pgstat_track_toast" resulted in 12792.882 ms vs. 12810.557 ms. So
>> at least the call overhead seems to be neglectible.
> 
> Yea, you'd probably need a few more tables and a few more connections for it
> to have a chance of mattering meaningfully.

So, I went ahead and
* set up 2 clusters with "track_toast" off and on resp.
* created 100 DBs
  * each with 100 tables
  * with one TOASTable column in each table
  * filling those with 32000 bytes of md5 garbage

These clusters sum up to ~ 2GB each, so differences should _start to_ 
show up, I reckon.

$ du -s testdb*
2161208 testdb
2163240 testdb_tracking

$ du -s testdb*/pg_stat
4448    testdb/pg_stat
4856    testdb_tracking/pg_stat

The db_*.stat files are 42839 vs. 48767 bytes each (so confirmed, the 
differences do show).


No idea if this is telling us anything, tbth, but the 
/proc/<pid>/smaps_rollup for a backend serving one of these DBs look 
like this ("0 kB" lines omitted):

track_toast OFF
===============
Rss:               12428 kB
Pss:                5122 kB
Pss_Anon:           1310 kB
Pss_File:           2014 kB
Pss_Shmem:          1797 kB
Shared_Clean:       5864 kB
Shared_Dirty:       3500 kB
Private_Clean:      1088 kB
Private_Dirty:      1976 kB
Referenced:        11696 kB
Anonymous:          2120 kB

track_toast ON (view not called yet):
=====================================
Rss:               12300 kB
Pss:                4883 kB
Pss_Anon:           1309 kB
Pss_File:           1888 kB
Pss_Shmem:          1685 kB
Shared_Clean:       6040 kB
Shared_Dirty:       3468 kB
Private_Clean:       896 kB
Private_Dirty:      1896 kB
Referenced:        11572 kB
Anonymous:          2116 kB

track_toast ON (view called):
=============================
Rss:               15408 kB
Pss:                7482 kB
Pss_Anon:           2083 kB
Pss_File:           2572 kB
Pss_Shmem:          2826 kB
Shared_Clean:       6616 kB
Shared_Dirty:       3532 kB
Private_Clean:      1472 kB
Private_Dirty:      3788 kB
Referenced:        14704 kB
Anonymous:          2884 kB

That backend used some memory for displaying the result too, of course...

A backend with just two TOAST columns in one table (filled with 
1.000.001 rows) looks like this before and after calling the 
"pg_stat_toast" view:
Rss:              146208 kB
Pss:              116181 kB
Pss_Anon:           2050 kB
Pss_File:           2787 kB
Pss_Shmem:        111342 kB
Shared_Clean:       6636 kB
Shared_Dirty:      45928 kB
Private_Clean:      1664 kB
Private_Dirty:     91980 kB
Referenced:       145532 kB
Anonymous:          2844 kB

Rss:              147736 kB
Pss:              103296 kB
Pss_Anon:           2430 kB
Pss_File:           3147 kB
Pss_Shmem:         97718 kB
Shared_Clean:       6992 kB
Shared_Dirty:      74056 kB
Private_Clean:      1984 kB
Private_Dirty:     64704 kB
Referenced:       147092 kB
Anonymous:          3224 kB

After creating 10.000 more tables (view shows 10.007 rows now), before 
and after calling "TABLE pg_stat_toast":
Rss:               13816 kB
Pss:                4898 kB
Pss_Anon:           1314 kB
Pss_File:           1755 kB
Pss_Shmem:          1829 kB
Shared_Clean:       5972 kB
Shared_Dirty:       5760 kB
Private_Clean:       832 kB
Private_Dirty:      1252 kB
Referenced:        13088 kB
Anonymous:          2124 kB

Rss:              126816 kB
Pss:               55213 kB
Pss_Anon:           5383 kB
Pss_File:           2615 kB
Pss_Shmem:         47215 kB
Shared_Clean:       6460 kB
Shared_Dirty:     113028 kB
Private_Clean:      1600 kB
Private_Dirty:      5728 kB
Referenced:       126112 kB
Anonymous:          6184 kB


That DB's stat-file is now 4.119.254 bytes (3.547.439 without track_toast).

After VACUUM ANALYZE, the size goes up to 5.919.812 (5.348.768).
The "100 tables" DBs' go to 97.910 (91.868) bytes.

In total:
$ du -s testdb*/pg_stat
14508   testdb/pg_stat
15472   testdb_tracking/pg_stat


IMHO, this would be ok to at least enable temporarily (e.g. to find out 
if MAIN or EXTERNAL storage/LZ4 compression would be ok/better for some 
columns).

All the best,
-- 
Gunnar "Nick" Bluth

Eimermacherweg 106
D-48159 Münster

Mobil +49 172 8853339
Email: gunnar.bluth@pro-open.de
__________________________________________________________________________
"Ceterum censeo SystemD esse delendam" - Cato