Re: Vacuum rate limit in KBps - Mailing list pgsql-hackers

On Thu, Jan 19, 2012 at 09:42:52PM -0500, Robert Haas wrote:
> I certainly didn't intend to come across as disparaging your work on
> this topic.  I understand that there are big problems with the way
> things work now; I'm just cautious about trying to replace them too
> hastily with something that may not turn out to be any better.  Of
> course, if we can replace it with something that we're sure is
> actually an improvement, I'm all in favor of that.  But, IMHO, the
> problems in this area are too serious to be solved by renaming the
> knobs.  At most, we're going to buy ourselves a little time to come up
> with a better solution.
> 
> IMHO, and at the risk of repeating myself, one of the big problems in
> this area is that we're making the user guess something that we really
> ought to be figuring out for them.  Just as users want checkpoints to
> run as slowly as possible while still not bumping into the next
> checkpoint, they'd presumably like vacuum to run as slowly as possible
> without bumping into the next vacuum.  Instead, we make them tell us
> how fast they'd like it tor run, which requires them to guess a value
> high enough to finish soon enough but low enough to minimize the
> impact on the rest of the system.
> 
> Another problem is that the vacuum algorithm itself could, I think, be
> made much smarter.  We could teach HOT to prune pages that contain no
> HOT chains but do contain dead tuples.  That would leave dead line
> pointers behind, but that's not nearly as bad as leaving the entire
> tuple behind.  We could, as Simon and others have suggested, have one
> threshold for vacuuming the heap (i.e. reclaiming dead tuples) and
> another for vacuuming the indexes (i.e. reclaiming dead line
> pointers).  That would open the door to partial vacuuming: just vacuum
> half a gigabyte or so of the heap, and then move on; the next vacuum
> can pick up where that one left off, at least up to the point where we
> decide we need to make an index pass; it would possibly also allow us
> to permit more than one vacuum on the same table at the same time,
> which is probably needed for very large tables.  We could have
> backends that see dead tuples on a page throw them over to the fence
> to the background writer for immediate pruning.  I blather, but I
> guess my point is that I really hope we're going to do something
> deeper here at some point in the near future, whatever becomes of the
> proposals now on the table.

As much as I hate to poo-poo a patch addition, I have to agree with
Robert Haas on this one.  Renaming settings really isn't moving us
forward.  It introduces a migration problem and really doesn't move us
forward in solving the underlying problem.  Additional monitoring, while
helpful, also is only a stop-gap.

Only a small number of sites are going to monitor auto-vacuum/analyze. 
Let's not start writing Postgres for those super-busy sites with a team
of administrators --- while they are important, they are not the
majority of our user-base, and we can pride ourselves that Postgres runs
pretty well without a team of admins.  We don't want to get into a case
where our super-visible, high-volume folks are overly setting the
project direction.

If we look at checkpoint smoothing, that was solved the right way with
a setting that worked automatically for everyone.

Now, I don't know if the solution is to time read/write duration to see
how busy the system is, or to look at the statistics to see how
backlogged the autovacuum system is when it gets time to actually
process a table, but those are the questions we should be asking here.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +