Re: PostgreSQL (9.3 and 9.6) eats all memory when using many tables

On Mon, Jun 13, 2016 at 6:36 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> hubert depesz lubaczewski <depesz(at)depesz(dot)com> writes:
>> While I do appreciate caching of metadata, it is causing serious
>> problems, which we will alleviate with server_lifetime, but I would much
>> prefer a setting like:
>> internal_cache_limit = 256MB
>
> Be careful what you ask for, you might get it.
>
> There used to be exactly such a limit in the catcache logic, which we
> ripped out because it caused far more performance problems than it fixed.
> See
> https://www.postgresql.org/message-id/flat/5141.1150327541%40sss.pgh.pa.us
>
> While we have no direct experience with limiting the plancache size,
> I'd expect a pretty similar issue there: a limit will either do nothing
> except impose substantial bookkeeping overhead (if it's more than the
> number of plans in your working set) or it will result in a performance
> disaster from cache thrashing (if it's less).

We don't need to keep a LRU list or do a clock sweep or anything. We
could go really simple and just toss the whole thing into /dev/null
when it gets too large, and start over.

The accounting overhead should be about as close to zero as you can get.

There would be no performance hit for people who don't set a limit, or
set a high one which is never exceeded.

For people who do exceed the limit, the performance hit would
certainly be no worse than if they have to gratuitously close and
re-open the connection. And it would be far better than swapping to
death, or incurring the wrath of OOM.

> You can only avoid falling
> off the performance cliff if your workload has *very* strong locality of
> reference, and that tends not to be the case.

If you have a weak locality of reference, than there is a pretty good
chance you aren't getting much help from the cache in the first place.
Periodically tossing it won't cost you much.

Cheers,

Jeff