Re: Protect syscache from bloating with negative cache entries

On Mon, Dec 19, 2016 at 6:15 AM, Kyotaro HORIGUCHI
<horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> Hello, recently one of my customer stumbled over an immoderate
> catcache bloat.

This isn't only an issue for negative catcache entries. A long time
ago, there was a limit on the size of the relcache, which was removed
because if you have a workload where the working set of relations is
just larger than the limit, performance is terrible. But the problem
now is that backend memory usage can grow without bound, and that's
also bad, especially on systems with hundreds of long-lived backends.
In connection-pooling environments, the problem is worse, because
every connection in the pool eventually caches references to
everything of interest to any client.

Your patches seem to me to have some merit, but I wonder if we should
also consider having a time-based threshold of some kind. If, say, a
backend hasn't accessed a catcache or relcache entry for many minutes,
it becomes eligible to be flushed. We could implement this by having
some process, like the background writer,
SendProcSignal(PROCSIG_HOUSEKEEPING) to every process in the system
every 10 minutes or so. When a process receives this signal, it sets
a flag that is checked before going idle. When it sees the flag set,
it makes a pass over every catcache and relcache entry. All the ones
that are unmarked get marked, and all of the ones that are marked get
removed. Access to an entry clears any mark. So anything that's not
touched for more than 10 minutes starts dropping out of backend
caches.

Anyway, that would be a much bigger change from what you are proposing
here, and what you are proposing here seems reasonable so I guess I
shouldn't distract from it. Your email just made me think of it,
because I agree that catcache/relcache bloat is a serious issue.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company