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