On Fri, Jan 31, 2014 at 10:22 PM, Bruce Momjian <br...@momjian.us> wrote: > On Thu, Sep 19, 2013 at 02:39:43PM -0400, Robert Haas wrote: >> Right now, whether or not to autovacuum is the rest of a two-pronged >> test. The first prong is based on number of updates and deletes >> relative to table size; that triggers a regular autovacuum. The >> second prong is based on age(relfrozenxid) and triggers a >> non-page-skipping vacuum (colloquially, an anti-wraparound vacuum). >> >> The typical case in which this doesn't work out well is when the table >> has a lot of inserts but few or no updates and deletes. So I propose >> that we change the first prong to count inserts as well as updates and >> deletes when deciding whether it needs to vacuum the table. We >> already use that calculation to decide whether to auto-analyze, so it >> wouldn't be very novel. We know that the work of marking pages >> all-visible will need to be done at some point, and doing it sooner >> will result in doing it in smaller batches, which seems generally >> good. >> >> However, I do have one concern: it might lead to excessive >> index-vacuuming. Right now, we skip the index vac step only if there >> ZERO dead tuples are found during the heap scan. Even one dead tuple >> (or line pointer) will cause an index vac cycle, which may easily be >> excessive. So I further propose that we introduce a threshold for >> index-vac; so that we only do index vac cycle if the number of dead >> tuples exceeds, say 0.1% of the table size. >> >> Thoughts? Let the hurling of rotten tomatoes begin. > > Robert, where are we on this? Should I post a patch?
I started working on this at one point but didn't finish the implementation, let alone the no-doubt-onerous performance testing that will be needed to validate whatever we come up with. It would be really easy to cause serious regressions with ill-considered changes in this area, and I don't think many people here have the bandwidth for a detailed study of all the different workloads that might be affected here right this very minute. More generally, you're sending all these pings three weeks after the deadline for CF4. I don't think that's a good time to encourage people to *start* revising old patches, or writing new ones. I've also had some further thoughts about the right way to drive vacuum scheduling. I think what we need to do is tightly couple the rate at which we're willing to do vacuuming to the rate at which we're incurring "vacuum debt". That is, if we're creating 100kB/s of pages needing vacuum, we vacuum at 2-3MB/s (with default settings). If we're creating 10MB/s of pages needing vacuum, we *still* vacuum at 2-3MB/s. Not shockingly, vacuum gets behind, the database bloats, and everything goes to heck. The rate of vacuuming needs to be tied somehow to the rate at which we're creating stuff that needs to be vacuumed. Right now we don't even have a way to measure that, let alone auto-regulate the aggressiveness of autovacuum on that basis. Similarly, for marking of pages as all-visible, we currently make the same decision whether the relation is getting index-scanned (in which case the failure to mark those pages all-visible may be suppressing the use of index scans or making them less effective) or whether it's not being accessed at all (in which case vacuuming it won't help anything, and might hurt by pushing other pages out of cache). Again, if we had better statistics, we could measure this - counting heap fetches for actual index-only scans plus heap fetches for index scans that might have been planned index-only scans but for the relation having too few all-visible pages doesn't sound like an impossible metric to gather. And if we had that, we could use it to trigger vacuuming, instead of guessing. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
