On 02/08/2012 11:22 AM, Bruce Momjian wrote:
Why can't vacuum handle things automatically like checkpoint smoothing? Why can't it detect when it is falling behind and speed up? Why can't it see as busy background writer and slow down? Unless we answer these questions, we are not solving the problem for 99% of our users.
Scheduling VACUUM is a much harder problem than checkpoint smoothing. Checkpoints can define "ahead" and "behind" simply; defining what those terms mean for vacuum scheduling across a set of tables is tricky. A possible downside to not vacuuming hard enough is that you'll have tables reach their thresholds faster than you're cleaning them up. That's a future bad event possible if you don't do the right thing now. Predicting when that will happen is a whole different class of problem than tracking whether checkpoints are running on schedule.
I have a design sketched out for something that adjusts the vacuum rate based on background writer activity. I'm not optimistic that will ever be automatic to the level you'd like here either. That looks like it will be prone to feedback loops where autovacuum can starve forever. If it ever lags enough that commonly accessed tables have become inefficiently stored, they will then generate more buffer I/O to access, and thus continue to block future vacuum work. That's the exact situation where vacuum is most needed, even though it seems too expensive to do. That's one trap people already fall into when doing this by hand. Everyone who's ever said "autovacuum uses too much I/O when it pops up unexpectedly during the day, I'm going to turn that off" has seen how that works out. Hilarity ensues when the inevitable and gigantic wraparound vacuums happen in the middle of the day instead.
Just trying to set the expectations bar realistically here. I don't consider the easier problem of checkpoint smoothing a solved one, either. Saying autovacuum needs to reach even that level of automation to be a useful improvement over now is a slippery goal. Regardless, the simple idea I submitted to this CF is clearly dead for now. I'll take the feedback of "this level of change can live in a user-side tuning tool" and do that instead. Since I was already thinking in the direction of background activity monitoring, I have a good idea how I'd need to approach this next, to be more likely to gain community buy-in as an automated improvement. That's a longer term project though, which I'll hopefully be able to revisit for 9.3.
-- Greg Smith 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
