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. Then in the morning when the workload picks up the system starts trying to pull the stuff they actually need into memory one block at a time. Such a customer can go from a 99% hit rate on Monday morning to say 50% on Tuesday morning, which results in a fifty-fold increase in I/O, all of which is random I/O. The system basically grinds to a halt for hours. 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. -- Robert Haas EDB: http://www.enterprisedb.com
