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
