Re: Some other CLOBBER_CACHE_ALWAYS culprits - Mailing list pgsql-hackers

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