Re: Protect syscache from bloating with negative cache entries

On 18/01/2019 08:48, Bruce Momjian wrote:
> On Thu, Jan 17, 2019 at 11:33:35AM -0500, Robert Haas wrote:
>> The flaw in your thinking, as it seems to me, is that in your concern
>> for "the likelihood that cache flushes will simply remove entries
>> we'll soon have to rebuild," you're apparently unwilling to consider
>> the possibility of workloads where cache flushes will remove entries
>> we *won't* soon have to rebuild. Every time that issue gets raised,
>> you seem to blow it off as if it were not a thing that really happens.
>> I can't make sense of that position. Is it really so hard to imagine
>> a connection pooler that switches the same connection back and forth
>> between two applications with different working sets? Or a system
>> that keeps persistent connections open even when they are idle? Do
>> you really believe that a connection that has not accessed a cache
>> entry in 10 minutes still derives more benefit from that cache entry
>> than it would from freeing up some memory?
> Well, I think everyone agrees there are workloads that cause undesired
> cache bloat. What we have not found is a solution that doesn't cause
> code complexity or undesired overhead, or one that >1% of users will
> know how to use.
>
> 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
> 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.
>
If you take that approach, then this number should be configurable. 
What if I had 12 common queries I used in rotation?

The ARM3 processor cache logic was to simply eject an entry at random,
as the obviously Acorn felt that the silicon required to have a more
sophisticated algorithm would reduce the cache size too much!

I upgraded my Acorn Archimedes that had an 8MHZ bus, from an 8MHz ARM2
to a 25MZ ARM3. that is a clock rate improvement of about 3 times. 
However BASIC programs ran about 7 times faster, which I put down to the
ARM3 having a cache.

Obviously for Postgres this is not directly relevant, but I think it
suggests that it may be worth considering replacing cache items at
random.  As there are no pathological corner cases, and the logic is
very simple.

Cheers,
Gavin