Re: Some other CLOBBER_CACHE_ALWAYS culprits

Hi,

On 2021-05-11 19:30:48 -0400, Tom Lane wrote:
> > IMO the problem largely stems from eagerly rebuilding *all* relcache entries
> > during invalidation processing.
>
> Uh, we don't do that; only for relations that are pinned, which we
> know are being used.

Sorry, all surviving relcache entries - but that's typically quite a
few.

> I spent some time thinking along those lines too, but desisted after
> concluding that that would fundamentally break the point of CCA
> testing, namely to be sure we survive when a cache flush occurs at
> $any-random-point.

Why would rebuilding non-accessed relcache entries over and over help
with that? I am not proposing that we do not mark all cache entries are
invalid, or that we do not rebuild tables that aren't accessed.

During an extremely trivial query from a user defined table ('blarg'),
here's top 10 RelationBuildDesc() calls:
344 rebuild pg_attrdef
274 rebuild pg_opclass
274 rebuild pg_amproc
260 rebuild pg_index
243 rebuild pg_am
236 rebuild pg_attrdef_adrelid_adnum_index
236 rebuild blarg
74 rebuild pg_namespace
52 rebuild pg_statistic
37 rebuild pg_tablespace
134.420 ms

Here's the same when joining two tables:
5828 rebuild pg_opclass
2897 rebuild pg_amop
2250 rebuild pg_cast
2086 rebuild pg_amproc
1465 rebuild pg_statistic
1274 rebuild pg_index
936 rebuild pg_attrdef
646 rebuild pg_operator
619 rebuild pg_am
518 rebuild pg_tablespace
1414.886 ms

three tables:
16614 rebuild pg_opclass
7787 rebuild pg_amop
6750 rebuild pg_cast
5388 rebuild pg_amproc
5141 rebuild pg_statistic
3058 rebuild pg_index
1824 rebuild pg_operator
1374 rebuild pg_attrdef
1233 rebuild pg_am
1110 rebuild pg_tablespace
3971.506 ms

four:
33328 rebuild pg_opclass
16020 rebuild pg_amop
14000 rebuild pg_statistic
13500 rebuild pg_cast
10876 rebuild pg_amproc
5792 rebuild pg_index
3950 rebuild pg_operator
2035 rebuild pg_am
1924 rebuild pg_tablespace
1746 rebuild pg_attrdef
7927.172 ms

This omits all the work done as part of RelationReloadNailed(), but
shows the problem quite clearly, I think?

Basically, every additional accessed table in a transaction makes things
drastically slower.

In the four join case my four user defined tables were rebuilt a lot of
times:
463 rebuild blarg
440 rebuild blarg2
293 rebuild blarg3
233 rebuild blarg4
despite obviously not being relevant for the cache invalidation
processing itself.

The list of sytable scans in the four table case:
380278 systable_beginscan: pg_class, using index: 1
111539 systable_beginscan: pg_attribute, using index: 1
73544 systable_beginscan: pg_class, using index: 0
4134 systable_beginscan: pg_opclass, using index: 1
4099 systable_beginscan: pg_amproc, using index: 1
2791 systable_beginscan: pg_am, using index: 0
2061 systable_beginscan: pg_index, using index: 1
1429 systable_beginscan: pg_attrdef, using index: 1
345 systable_beginscan: pg_type, using index: 1
300 systable_beginscan: pg_cast, using index: 1
195 systable_beginscan: pg_statistic, using index: 1
191 systable_beginscan: pg_amop, using index: 1
103 systable_beginscan: pg_operator, using index: 1
52 systable_beginscan: pg_tablespace, using index: 1
33 systable_beginscan: pg_proc, using index: 1
27 systable_beginscan: pg_authid, using index: 1
20 systable_beginscan: pg_namespace, using index: 1
4 systable_beginscan: pg_statistic_ext, using index: 1

581145 in total.

> Sure, in practice it will not be the case that a flush occurs at EVERY
> random point. But I think if you try to optimize away a rebuild at
> point B on the grounds that you just did one at point A, you will fail
> to cover the scenario where flush requests arrive at exactly points A
> and B.

I don't think we'd loose a lot of practical coverage if we avoided
rebuilding non-accessed relcache entries eagerly during cache
lookups. What coverage do we e.g. gain by having a single
SearchCatCacheMiss() triggering rebuilding the relcache of a user
defined table several times?

The InvalidateSystemCaches() marks all catcache entries as invalid. The
next catcache lookup will thus trigger a cache miss. That cache miss
will typically at least open the previously not locked relation + index
the cache is over. Each of those relation opens will fire off another
InvalidateSystemCaches(). Which will rebuild all the surviving relcache
entries at least twice - despite never being accessed in that path.

> What it looked like to me, in an admittedly cursory bit of perf
> testing, was that most of the cycles were going into fetching
> cache entries from catalogs over and over. But it's hard to avoid
> that.

Sure - but that's only because we rebuild stuff over and over despite
not being accessed...

Greetings,

Andres Freund