Robert Haas wrote:
> Since people *already* raise deadlock_timeout to obscenely high
> values (a minute? an hour???) and then complain that things blow
> up in their face, I think there's a decent argument to be made
> that piggybacking anything else on that setting is unwise.
If people are really doing that, then I tend to agree. I wasn't
aware of that practice.
> Against that, FWICT, this problem only affects a small number of
> users: Jan is the only person I can ever remember reporting this
> issue. I'm not dumb enough to think he's the only person who it
> affects; but my current belief is that it's not an enormously
> common problem. So the main argument I can see against adding a
> GUC is that the problem is too marginal to justify a setting of
> its own. What I really see as the key issue is: suppose we
> hardcode this to say 2 seconds. Is that going to fix the problem
> effectively for 99% of the people who have this problem, or for
> 25% of the people who have this problem? In the former case, we
> probably don't need a GUC; in the latter case, we probably do.
Given the fact that autovacuum will keep throwing workers at it to
essentially loop indefinitely at an outer level, I don't think the
exact setting of this interval is all that critical either. My gut
feel is that anything in the 2 second to 5 second range would be
sane, so I won't argue over any explicit setting within that range.
Below that, I think the overhead of autovacuum coming back to the
table repeatedly would probably start to get too high; below that
we could be causing some small, heavily-updated table to be
neglected by autovacuum -- especially if you get multiple
autovacuum workers tied up in this delay on different tables at the
same time.
Regarding how many people are affected, I have seen several reports
of situations where users claim massive impact on performance when
autovacuum kicks in. The reports have not included enough detail to
quantify the impact or in most cases to establish a cause, but this
seems like it could have a noticable impact, especially if the
deadlock timeout was set to more than a second.
-Kevin