On Mon, Aug 12, 2013 at 11:47 PM, Jeff Janes wrote:
Reviving a very old thread, because I've run into the issue again.
On Tue, May 29, 2012 at 11:58 AM, Robert Haas wrote:
On Fri, May 25, 2012 at 4:06 PM, Jeff Janes wrote:
If I invoke vacuum manually and do so with VacuumCostDelay == 0, I
have basically declared my intentions to get this pain over with as
fast as possible even if it might interfere with other processes.

Under that condition, shouldn't it use BAS_BULKWRITE rather than
BAS_VACUUM? The smaller ring size leads to a lot of synchronous WAL
flushes which I think can slow the vacuum down a lot.
Of course, an autovacuum of a really big table could run too slowly,
too, even though it's not a foreground task.
True. But almost by definition, an autovacuum is not trying to run
inside a maintenance window.

Would it be reasonable to upgrade the ring buffer size whenever
VacuumCostDelay is zero, regardless of whether it is a manual or an
auto vac? One thing I worry about is that many people may have
changed autovacuum_vacuum_cost_delay from 20 directly to 0 or -1, and
the accidental throttling on WAL syncs might be the only thing
preventing their system from falling over each time autovac of a large
table kicks in.
I'm not sure what the right thing to do here is, but I definitely
agree there's a problem. There are definitely cases where people want
or indeed need to vacuum as fast as possible, and using a small ring
buffer is not the way to do that. Now, tying that to VacuumCostDelay
doesn't seem right, because setting that to 0 shouldn't suddenly
change the behavior in other ways, as well.

In general, the approach we've taken so far has been to try to hide
the ring-buffer behavior from users and not make it tunable, but I'm
not sure we can really get away with that in this case. Increasing
the ring-buffer size has system-wide performance implications which
could be very good (less bloat) or very bad (I/O starvation of
concurrent activity). I don't think the system knows enough to guess
which one will be better in any particular case.

Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Search Discussions

Discussion Posts


Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 4 of 7 | next ›
Discussion Overview
grouppgsql-hackers @
postedMay 25, '12 at 8:06p
activeAug 16, '13 at 6:33p



site design / logo © 2017 Grokbase