Re: Cache relation sizes?

po 3. 8. 2020 v 17:54 odesílatel Konstantin Knizhnik <
k(dot)knizhnik(at)postgrespro(dot)ru> napsal:

>
>
> On 01.08.2020 00:56, Thomas Munro wrote:
> > On Fri, Jul 31, 2020 at 2:36 PM Thomas Munro <thomas(dot)munro(at)gmail(dot)com>
> wrote:
> >> There's still the matter of crazy numbers of lseeks in regular
> >> backends; looking at all processes while running the above test, I get
> >> 1,469,060 (9.18 per pgbench transaction) without -M prepared, and
> >> 193,722 with -M prepared (1.21 per pgbench transaction). Fixing that
> >> with this approach will require bullet-proof shared invalidation, but
> >> I think it's doable, in later work.
> > I couldn't help hacking on this a bit. Perhaps instead of
> > bullet-proof general shared invalidation, we should have a way for
> > localised bits of code to state that they are ok with a "relaxed"
> > value. Then they should explain the theory for why that is safe in
> > each case based on arguments about memory barrier pairs, but leave all
> > other callers making the system call so that we don't torpedo the
> > whole project by making it too hard. For now, the main cases I'm
> > interested in are the ones that show up all the time as the dominant
> > system call in various workloads:
> >
> > (1) Sequential scan relation-size probe. This should be OK with a
> > relaxed value. You can't miss the invalidation for a truncation,
> > because the read barrier in your lock acquisition on the relation
> > pairs with the write barrier in the exclusive lock release of any
> > session that truncated, and you can't miss relation any relation
> > extension that your snapshot can see, because the release of the
> > extension lock pairs with the lock involved in snapshot acquisition.
> >
> > (2) Planner relation-size probe, which should be OK with a relaxed
> > value. Similar arguments give you a fresh enough view, I think.
> >
> > Or maybe there is a theory along these lines that already covers every
> > use of smgrnblocks(), so a separate mode isn't require... I don't
> > know!
> >
> > The attached sketch-quality patch shows some of the required elements
> > working, though I ran out of steam trying to figure out how to thread
> > this thing through the right API layers so for now it always asks for
> > a relaxed value in table_block_relation_size().
> So in this thread three solutions are proposed:
> 1. "bullet-proof general shared invalidation"
> 2. recovery-only solution avoiding shared memory and invalidation
> 3. "relaxed" shared memory cache with simplified invalidation
>
> If solving such very important by still specific problem of caching
> relation size requires so much efforts,
> then may be it is time to go further in the direction towards shared
> catalog?
> This shared relation cache can easily store relation size as well.
> In addition it will solve a lot of other problems:
> - noticeable overhead of local relcache warming
> - large memory consumption in case of larger number of relations
> O(max_connections*n_relations)
> - sophisticated invalidation protocol and related performance issues
>
> Certainly access to shared cache requires extra synchronization.But DDL
> operations are relatively rare.
>

Some applications use very frequently CREATE TEMP TABLE, DROP TEMP TABLE,
or CREATE TABLE AS SELECT ..

Regards

Pavel

So in most cases we will have only shared locks. May be overhead of
> locking will not be too large?
>
>
>
>