On Thu, Aug 28, 2014 at 1:36 PM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:
> Robert Haas wrote:
>> Now, in the case where you are setting an overall limit, there is at
>> least an argument to be made that you can determine the overall rate
>> of autovacuum-induced I/O activity that the system can tolerate, and
>> set your limits to stay within that budget, and then let the system
>> decide how to divide that I/O up between workers. But if you're
>> overriding a per-table limit, I don't really see how that holds any
>> water. The system I/O budget doesn't go up just because one
>> particular table is being vacuumed rather than any other. The only
>> plausible use case for setting a per-table rate that I can see is when
>> you actually want the system to use that exact rate for that
>> particular table.
>
> Yeah, this makes sense to me too -- at least as long as you only have
> one such table. But if you happen to have more than one, and due to
> some bad luck they happen to be vacuumed concurrently, they will eat a
> larger share of your I/O bandwidth budget than you anticipated, which
> you might not like.
I agree that you might not like that. But you might not like having
the table vacuumed slower than the configured rate, either. My
impression is that the time between vacuums isn't really all that
negotiable for some people. I had one customer who had horrible bloat
issues on a table that was vacuumed every minute; when we changed the
configuration so that it was vacuumed every 15 seconds, those problems
went away. Now that is obviously more a matter for autovacuum_naptime
than this option, but the point seems general to me: if you need the
table vacuumed every N seconds, minutes, or hours, and it only gets
vacuumed every 2N or 3N or 5N seconds, minutes, or hours because there
are other autovacuum workers running, the table is going to bloat.
That *might* be better than busting your I/O budget, but it might also
be (and I think frequently is) much worse.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
