On Thursday 06 October 2011 07:00:20 Andrew Sullivan wrote: > On Thu, Oct 06, 2011 at 11:58:15AM +0200, Vincent de Phily wrote: > > Fearing that vacuuming might accumulate lateness and hoping to see the > > system idle every now and then, > > Why is your goal to see the system idle every now and then? It's not > going to get tired if it keeps working, and if you have a lot of work > and can spin out that work so that the system always has a little bit > of work to do, then you use your resources more efficiently. > > Normally, one likes to see some idle time because it is evidence of > "headroom" -- that you have more capacity than you actually need. If > that's the reason you want to see the idle times, then surely you > don't want to tune the system with the goal of causing idleness. You > want to tune the system so that the work gets done in as smooth and > fast a way possible. So I would aim for maximum throughput (including > but not exclusively complete table maintenance) and then check whether > you're getting any idle time. Off the cuff, though, it sounds to me > like you need more capacity than you have. I agree idleness per se is not the goal, and whatever work needs to be done might as well be spread smoothly over time. Idleness *is* however a measure of the ressource headroom available, and that headroom was incomfortably small (for IO) in this case. I was just groping for more performance out of the system. 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) ? 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. 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 ? * Does vacuuming fill the OS's disk cache, and is it an issue if it does ? Cheers. -- Vincent de Phily -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
