On Thu, Oct 06, 2011 at 03:39:02PM +0200, Vincent de Phily wrote: > In the case of vacuuming however, I think there's a point to be made about > finishing fast when all vacuum workers are constantly busy : say the vacuum > daemon notices that there are 10 tables that need vacuuming now. It allocates > 3 workers, but while they do their intentionally-slow work, the other 7 tables > keep creating more vacuumable tuples, so it'll be more work overall because > they're "late" in their "vacuum schedule". Does that make sense (I'm not sure > id does) ? Yes, that's exactly the issue. You need to balance the resource you're depriving the "real" database transactions (i.e. the user ones) against the cost of waiting, which waiting will probably cost those user transactions in performance. The reason there's no magic solution is because much of this depends on your use patterns. > Anyway, my particular issue is solved for now : I realized those tables were > terribly bloated (often more than 99% slack), so I vacuum-fulled them and now > the autovacuums run very fast and the disk is 90% idle again. That slack > probably appeared at table initialization time because the fsm was not big > enough. I since raised the fsm, but I think it's big enough during normal > (non-init) usage anyway. This is what you want to keep an eye on, then. Why do you think it came from "initialization time", though? VACUUM only has work to do when dead tuples show up (e.g. from DELETE or UPDATE), and normally when you first populate a table you do a COPY, which isn't going to create dead tuples. > I'm still interested in more opinions about my two questions : > * When does it make sense to make autovacuum more aggressive on IO, and by > how much ? At bottom, you don't want your tables to get so bloated that they exhibit the problem you just saw, but you also don't want vacuum to be taking so much I/O that your other tasks can't get done. That's the general principle; how it applies to your case depends rather on use patters. For instance, if you know that there will be at most 10% churn on every table every day, but all transactions happen between 9:00 and 17:00 local time, then it's probably safe to allow that to happen: as long as your FSM can keep track, it can all be recovered every day after 17:00, so you might as well allow the work to build up, & let the vacuums happen when they're not stealing any I/O from user queries. If, on the other hand, you get 100% churn on 50% of the tables every day between 09:00 and 11:00, and the rest of the day is mostly read-only traffic, with read-only traffic during all 24 hours (don't scoff -- I had exactly this problem once) then you want to be quite aggressive with the autovacuum settings, because keeping that 100% bloat down is going to pay off in a big way on the read-only traffic. > * Does vacuuming fill the OS's disk cache, and is it an issue if it does ? Well, it _affects_ the OS's disk cache. Whether it fills it is controlled by the cache algorithms and the amount of memory you have devoted to cache. Every time you touch the disk, you potentially alter the cache in favour of what you just saw. In the above artificial examples, the vacuums that run "after everyone went home" will almost certainly end up taking over the cache, because there's no other activity to keep other things in the disk cache. In the second example, though, with a lot of read-only activity all the time, the things that are most popular are likely to remain in a (modern) disk cache most of the time because they're called so often that the vacuumed page doesn't end up being enough traffic to cause an eviction (or, anyway, to evict for any significant time). A -- Andrew Sullivan ajs@xxxxxxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
