On Thu, Jan 28, 2021 at 9:30 AM Robert Haas <robertmh...@gmail.com> wrote: > On Thu, Jan 14, 2021 at 8:09 PM Peter Geoghegan <p...@bowt.ie> wrote: > > So dirty pages are debt that VACUUM can easily create, whereas buffer > > misses are paid directly by VACUUM. It is its own backpressure, for > > the most part. Making the costing stuff highly sensitive to dirtying > > pages (but not sensitive to much else) works out because it either > > avoids making a bad situation worse, or has no real additional > > downside when the system is completely overwhelmed (i.e. bottlenecked > > on cleaning dirty pages). > > This isn't really true. The cost of a buffer miss is not limited to > the cost of reading the replacement buffer, a cost which is paid by > VACUUM. It is also very often the cost of rereading the evicted > buffer, which VACUUM does nothing about. Customers get hosed by VACUUM > reading a lot of rarely-used data overnight and evicting all of the > actually-hot data from cache.
Well, I did say "for the most part". In any case there is not much reason to think that throttling VACUUM on shared_buffers page misses can make very much difference in this scenario. > It is fair to argue that perhaps such customers should invest in more > and better hardware. In some cases, a customer who can fit 1% of their > database in cache is relying on a 99% cache hit ratio, which is > precarious at best. But, they can sometimes get away with it until a > large batch job like VACUUM gets involved. Actually, my first observation here is that VACUUM probably shouldn't do this at all. In other words, I agree with what I suspect your customer's intuition was in a rather straightforward way: VACUUM really shouldn't be reading several large indexes in full when they have barely been modified in months or years -- that's the real problem. It ought to be possible to make big improvements in that area without changing the fundamental invariants. I am once again referring to the pending work on VACUUM from Masahiko. -- Peter Geoghegan
