|From:||Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>|
|Cc:||bruce(at)momjian(dot)us, robertmhaas(at)gmail(dot)com, tgl(at)sss(dot)pgh(dot)pa(dot)us, ideriha(dot)takeshi(at)jp(dot)fujitsu(dot)com, pgsql-hackers(at)lists(dot)postgresql(dot)org, tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com, alvherre(at)alvh(dot)no-ip(dot)org, andres(at)anarazel(dot)de, michael(dot)paquier(at)gmail(dot)com, david(at)pgmasters(dot)net, Jim(dot)Nasby(at)bluetreble(dot)com, craig(at)2ndquadrant(dot)com|
|Subject:||Re: Protect syscache from bloating with negative cache entries|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
At Fri, 18 Jan 2019 16:39:29 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote in <20190118(dot)163929(dot)229869562(dot)horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>
> At Fri, 18 Jan 2019 11:46:03 +1300, Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz> wrote in <4e62e6b7-0ffb-54ae-3757-5583fcca38c0(at)archidevsys(dot)co(dot)nz>
> > On 18/01/2019 08:48, Bruce Momjian wrote:
> > > Unfortunately, because we have not found something we are happy with,
> > > we
> > > have done nothing. I agree LRU can be expensive. What if we do some
> > > kind of clock sweep and expiration like we do for shared buffers? I
> So, it doesn't use LRU but a kind of clock-sweep method. If it
> finds the size is about to exceed the threshold by
> resiz(doubl)ing when the current hash is filled up, it tries to
> trim away the entries that are left for a duration corresponding
> to usage count. This is not a hard limit but seems to be a good
> > > think the trick is figuring how frequently to do the sweep. What if
> > > we
> > > mark entries as unused every 10 queries, mark them as used on first
> > > use,
> > > and delete cache entries that have not be used in the past 10 queries.
> As above, it tires pruning at every resizing time. So this adds
> complexity to the frequent paths only by setting last accessed
> time and incrementing access counter. It scans the whole hash at
> resize time but it doesn't add much comparing to resizing itself.
> > If you take that approach, then this number should be configurable.
> > What if I had 12 common queries I used in rotation?
> This basically has two knobs. The minimum hash size to do the
> pruning and idle time before reaping unused entries, per
This is the rebased version.
0001: catcache pruning
syscache_memory_target controls per-cache basis minimum size
where this starts pruning.
syscache_prune_min_time controls minimum idle duration until an
catcache entry is removed.
0002: catcache statistics view
track_syscache_usage_interval is the interval statitics of
catcache is collected.
pg_stat_syscache is the view that shows the statistics.
0003: Remote GUC setting
It is independent from the above two, and heavily arguable.
pg_set_backend_config(pid, name, value) changes the GUC <name> on
the backend with <pid> to <value>.
NTT Open Source Software Center
|Next Message||Fabien COELHO||2019-01-18 08:37:26||Re: PSA: we lack TAP test coverage on NetBSD and OpenBSD|
|Previous Message||Laurenz Albe||2019-01-18 08:27:04||Re: Libpq support to connect to standby server as priority|