From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andres Freund <andres@anarazel.de>
cc: Robert Haas <robertmhaas@gmail.com>,
        Michael Paquier <michael.paquier@gmail.com>,
        Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>,
        David Steele <david@pgmasters.net>,
        Jim Nasby <Jim.Nasby@bluetreble.com>,
        Craig Ringer <craig@2ndquadrant.com>,
        PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
Subject: Re: Protect syscache from bloating with negative cache entries
In-reply-to: <20171201213113.z4gbol66ipvg5b4o@alap3.anarazel.de>
References: <3b7b7f90-db46-8c37-c4f7-443330c3ae33@pgmasters.net>
 <20170317.142313.232290068.horiguchi.kyotaro@lab.ntt.co.jp>
 <20171031.184622.92520047.horiguchi.kyotaro@lab.ntt.co.jp>
 <CAB7nPqSGdesvS-odcP+RRJaBOssPazwKURf1GAUYZED4dKSmiQ@mail.gmail.com>
 <CA+TgmobPHMmQEEUFDU6XCSj5ArEfyagS1+wisJ8QAY+EsUNbxg@mail.gmail.com>
 <CAB7nPqQvbMc5Exx=Qfx=42Osntqrm-thLLt3wCiLvLaokcsg4Q@mail.gmail.com>
 <84820.1512015443@sss.pgh.pa.us>
 <CA+TgmoY9qNYtdx8xh463f=82NosFw4PgthFV4UeP7WhvWu_cbQ@mail.gmail.com>
 <85587.1512101829@sss.pgh.pa.us>
 <CA+TgmoYiztC-M7qf-LVGdG10qN2wvyVUgudtcyeMCkn0UATOqw@mail.gmail.com>
 <20171201213113.z4gbol66ipvg5b4o@alap3.anarazel.de>
Comments: In-reply-to Andres Freund <andres@anarazel.de>
	message dated "Fri, 01 Dec 2017 13:31:13 -0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3543.1512164423.1@sss.pgh.pa.us>
Date: Fri, 01 Dec 2017 16:40:23 -0500
Message-ID: <3544.1512164423@sss.pgh.pa.us>
Precedence: bulk

Andres Freund <andres@anarazel.de> writes:
> On 2017-12-01 16:20:44 -0500, Robert Haas wrote:
>> Well, yeah, that would be insane.  But I think even something very
>> rough could work well enough.  I think our goal should be to eliminate
>> cache entries that are have gone unused for many *minutes*, and
>> there's no urgency about getting it to any sort of exact value.  For
>> non-idle backends, using the most recent statement start time as a
>> proxy would probably be plenty good enough.  Idle backends might need
>> a bit more thought.

> Our timer framework is flexible enough that we can install a
> once-a-minute timer without much overhead. That timer could increment a
> 'cache generation' integer. Upon cache access we write the current
> generation into relcache / syscache (and potentially also plancache?)
> entries. Not entirely free, but cheap enough. In those once-a-minute
> passes entries that haven't been touched in X cycles get pruned.

I have no faith in either of these proposals, because they both assume
that the problem only arises over the course of many minutes.  In the
recent complaint about pg_dump causing relcache bloat, it probably does
not take nearly that long for the bloat to occur.

Maybe you could make it work on the basis of number of cache accesses,
or some other normalized-to-workload-not-wall-clock time reference.

			regards, tom lane