Re: Does RelCache/SysCache shrink except when relations are deleted?

On Thu, Sep 29, 2011 at 9:39 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> Like I said, this doesn't really come up this often but the 'real'
> solution in terms of postgrs is probably some kind of upper bound in
> the amount of cache memory used plus some intelligence in the cache
> implementation.  This is tricky stuff though and so far no credible
> proposals have been made and the demand for the feature is not very
> high.

We (i.e. $EMPLOYER) have a customer who ran into this problem (i.e.
relcache/syscache memory usage shooting through the roof) in testing,
so I'm somewhat motivated to see if we can't come up with a fix. I am
fairly sure that was on a 64-bit build, so the issue wasn't just that
they didn't have enough address space. It seems that we used to have
some kind of LRU algorithm to prevent excessive memory usage, but we
rippped it out because it was too expensive (see commit
8b9bc234ad43dfa788bde40ebf12e94f16556b7f). I don't have a brilliant
idea at the moment, but I wonder if we could come up with something
that's cheap enough to manage that it doesn't materially affect
performance in normal cases, but just kicks in when things get really
out of control.

A trivial algorithm would be - if you're about to run out of memory,
flush all the caches; or evict 10% of the entries at random. Of
course, the problem with anything like this is that it's hard to know
when you're about to run out of memory before you actually do, and any
hard-coded limit you care to set will sometimes be wrong. So maybe
that's not the right approach. At the same time, I don't think that
simply hoping the user has enough memory is an adequate answer.

One thing to consider is that in some cases a user may plan to do
something like touch every table in the database exactly once and then
exit. In that case, if we knew in advance what the user's intentions
were, we'd want to use an MRU eviction algorithm rather than LRU.
Again, we don't know that in advance. But in such a use case it's
reasonable for the user to expect that the amount of backend-private
memory used for caching will not grow without bound.

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