Thread
-
pgstat vs aset
Andres Freund <andres@anarazel.de> — 2026-04-08T23:04:58Z
Hi, In [1] I looked at pgstat memory usage after sort of a complaint by Nathan. The conversion of "PgStat Shared Ref" to slab seems like an improvement we obviously should make [2]. In the email I also showed how much CacheMemoryContext using a lot of memory after doing a database wide VACUUM (with ~100k tables), not very surprising. I didn't immediately look at the next few entries: ┌────────────────────────┬─────────────┬───────────────┬────────────┬─────────────┬────────────┐ │ name │ total_bytes │ total_nblocks │ free_bytes │ free_chunks │ used_bytes │ ├────────────────────────┼─────────────┼───────────────┼────────────┼─────────────┼────────────┤ │ CacheMemoryContext │ 168820784 │ 31 │ 2363064 │ 33 │ 166457720 │ │ PgStat Pending │ 27983872 │ 3419 │ 27846912 │ 112699 │ 136960 │ │ smgr relation table │ 16777216 │ 12 │ 2797592 │ 50 │ 13979624 │ │ MdSmgr │ 8388608 │ 11 │ 3475256 │ 1 │ 4913352 │ │ PgStat Shared Ref │ 5767344 │ 88 │ 19712 │ 352 │ 5747632 │ │ PgStat Shared Ref Hash │ 4195376 │ 2 │ 712 │ 0 │ 4194664 │ │ Relcache by OID │ 4194304 │ 10 │ 57448 │ 24 │ 4136856 │ │ TopMemoryContext │ 173264 │ 7 │ 32968 │ 79 │ 140296 │ └────────────────────────┴─────────────┴───────────────┴────────────┴─────────────┴────────────┘ When I did I was quite surprised to see "PgStat Pending" using so much, as it should all be freed after the stats have been submitted, a few seconds later at most. After a moment of worry about having introduced a leak, and adding the necessary columns to see more information, it's clear that all the memory was actually freed. It's just that we don't ever free aset blocks, even if all the constituent memory has been freed. So the overall context size doesn't shrink. That seems decidedly not great. I wonder how bad it would be to teach aset to recognize this situation. But I think for this use case we actually have a more fitting memory context type for this workload, i.e. GenerationContext. With that the size after the same vacuum is │ PgStat Pending │ 134144 │ 3 │ 133848 │ 0 │ 296 │ Seems like a fairly obvious change. My local experimental changes attached. Greetings, Andres Freund [1] https://postgr.es/m/fcvnawwq32mamvf66q5i3sk73xudxz5corqlgqljtocepspjps%40ypvl6yjzy5xk [2] It's a big enough saving that I'm kinda wondering about whether we should try to sneak it into 19.
-
Re: pgstat vs aset
Michael Paquier <michael@paquier.xyz> — 2026-04-09T00:20:00Z
On Wed, Apr 08, 2026 at 07:04:58PM -0400, Andres Freund wrote: > Seems like a fairly obvious change. This gain is a nice surprise. I am +1 for the sneak argument with v19. -- Michael
-
Re: pgstat vs aset
Chao Li <li.evan.chao@gmail.com> — 2026-04-09T01:51:26Z
> On Apr 9, 2026, at 07:04, Andres Freund <andres@anarazel.de> wrote: > > Hi, > > In [1] I looked at pgstat memory usage after sort of a complaint by Nathan. > The conversion of "PgStat Shared Ref" to slab seems like an improvement we > obviously should make [2]. > > In the email I also showed how much CacheMemoryContext using a lot of memory > after doing a database wide VACUUM (with ~100k tables), not very surprising. > > > I didn't immediately look at the next few entries: > ┌────────────────────────┬─────────────┬───────────────┬────────────┬─────────────┬────────────┐ > │ name │ total_bytes │ total_nblocks │ free_bytes │ free_chunks │ used_bytes │ > ├────────────────────────┼─────────────┼───────────────┼────────────┼─────────────┼────────────┤ > │ CacheMemoryContext │ 168820784 │ 31 │ 2363064 │ 33 │ 166457720 │ > │ PgStat Pending │ 27983872 │ 3419 │ 27846912 │ 112699 │ 136960 │ > │ smgr relation table │ 16777216 │ 12 │ 2797592 │ 50 │ 13979624 │ > │ MdSmgr │ 8388608 │ 11 │ 3475256 │ 1 │ 4913352 │ > │ PgStat Shared Ref │ 5767344 │ 88 │ 19712 │ 352 │ 5747632 │ > │ PgStat Shared Ref Hash │ 4195376 │ 2 │ 712 │ 0 │ 4194664 │ > │ Relcache by OID │ 4194304 │ 10 │ 57448 │ 24 │ 4136856 │ > │ TopMemoryContext │ 173264 │ 7 │ 32968 │ 79 │ 140296 │ > └────────────────────────┴─────────────┴───────────────┴────────────┴─────────────┴────────────┘ > > When I did I was quite surprised to see "PgStat Pending" using so much, as it > should all be freed after the stats have been submitted, a few seconds later > at most. > > After a moment of worry about having introduced a leak, and adding the > necessary columns to see more information, it's clear that all the memory was > actually freed. It's just that we don't ever free aset blocks, even if all the > constituent memory has been freed. So the overall context size doesn't shrink. > > That seems decidedly not great. > > I wonder how bad it would be to teach aset to recognize this situation. > > But I think for this use case we actually have a more fitting memory context > type for this workload, i.e. GenerationContext. With that the size after the > same vacuum is > > │ PgStat Pending │ 134144 │ 3 │ 133848 │ 0 │ 296 │ > > Seems like a fairly obvious change. > > > My local experimental changes attached. > > > Greetings, > > Andres Freund > > [1] https://postgr.es/m/fcvnawwq32mamvf66q5i3sk73xudxz5corqlgqljtocepspjps%40ypvl6yjzy5xk > [2] It's a big enough saving that I'm kinda wondering about whether we should > try to sneak it into 19. > <pgstat-memory.diff> I tested this patch by creating a large number of tables and then running VACUUM. Before the patch: ``` evantest=# select name, total_bytes, free_bytes, used_bytes, total_nblocks, free_chunks from pg_backend_memory_contexts where name in ('PgStat Pending', 'PgStat Shared Ref', 'PgStat Shared Ref Hash') order by name; name | total_bytes | free_bytes | used_bytes | total_nblocks | free_chunks ------------------------+-------------+------------+------------+---------------+------------- PgStat Pending | 16384 | 8368 | 8016 | 5 | 8 PgStat Shared Ref | 8192 | 3672 | 4520 | 4 | 2 PgStat Shared Ref Hash | 9280 | 704 | 8576 | 2 | 0 (3 rows) evantest=# vacuum; VACUUM evantest=# evantest=# select name, total_bytes, free_bytes, used_bytes, total_nblocks, free_chunks from pg_backend_memory_contexts where name in ('PgStat Pending', 'PgStat Shared Ref', 'PgStat Shared Ref Hash’) order by name; name | total_bytes | free_bytes | used_bytes | total_nblocks | free_chunks ------------------------+-------------+------------+------------+---------------+------------- PgStat Pending | 65536 | 64896 | 640 | 11 | 244 PgStat Shared Ref | 8142848 | 79328 | 8063520 | 997 | 1987 PgStat Shared Ref Hash | 4195392 | 704 | 4194688 | 2 | 0 (3 rows) ``` After the patch: ``` evantest=# select name, total_bytes, free_bytes, used_bytes, total_nblocks, free_chunks from pg_backend_memory_contexts where name in ('PgStat Pending', 'PgStat Shared Ref', 'PgStat Shared Ref Hash’) order by name; name | total_bytes | free_bytes | used_bytes | total_nblocks | free_chunks ------------------------+-------------+------------+------------+---------------+------------- PgStat Pending | 8192 | 3360 | 4832 | 4 | 0 PgStat Shared Ref | 16802 | 13536 | 3266 | 1 | 188 PgStat Shared Ref Hash | 9280 | 704 | 8576 | 2 | 0 (3 rows) evantest=# evantest=# vacuum; VACUUM evantest=# evantest=# evantest=# evantest=# select name, total_bytes, free_bytes, used_bytes, total_nblocks, free_chunks from pg_backend_memory_contexts where name in ('PgStat Pending', 'PgStat Shared Ref', 'PgStat Shared Ref Hash’) order by name; name | total_bytes | free_bytes | used_bytes | total_nblocks | free_chunks ------------------------+-------------+------------+------------+---------------+------------- PgStat Pending | 37888 | 37592 | 296 | 3 | 0 PgStat Shared Ref | 7274914 | 3672 | 7271242 | 444 | 51 PgStat Shared Ref Hash | 4195392 | 704 | 4194688 | 2 | 0 (3 rows) ``` For PgStat Pending, total_bytes went from about 64kB down to about 36kB. For PgStat Shared Ref, total_bytes went from about 8MB down to about 7MB. As a comparison point, PgStat Shared Ref Hash showed no change before and after the patch. So this does show improvement to me. Best regards, -- Chao Li (Evan) HighGo Software Co., Ltd. https://www.highgo.com/ -
Re: pgstat vs aset
Nathan Bossart <nathandbossart@gmail.com> — 2026-04-09T14:50:49Z
On Wed, Apr 08, 2026 at 07:04:58PM -0400, Andres Freund wrote: > In [1] I looked at pgstat memory usage after sort of a complaint by Nathan. > The conversion of "PgStat Shared Ref" to slab seems like an improvement we > obviously should make [2]. > > [...] > > But I think for this use case we actually have a more fitting memory context > type for this workload, i.e. GenerationContext. With that the size after the > same vacuum is Nice. > [2] It's a big enough saving that I'm kinda wondering about whether we should > try to sneak it into 19. I don't know whether it's appropriate to try to sneak this into v19 at this point, but it at least seems like a "moment v20 opens for development" thing. Should we double check there's no meaningful performance differences? Otherwise, this seems like an easy win. -- nathan