Re: autovacuum scheduling starvation and frenzy
| От | Jeff Janes |
|---|---|
| Тема | Re: autovacuum scheduling starvation and frenzy |
| Дата | |
| Msg-id | CAMkU=1yZcZv-Yu-ySjXYpnBsSRi76_iJCPM_+adAAT_+24HbcA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: autovacuum scheduling starvation and frenzy (Alvaro Herrera <alvherre@2ndquadrant.com>) |
| Ответы |
Re: autovacuum scheduling starvation and frenzy
Re: autovacuum scheduling starvation and frenzy |
| Список | pgsql-hackers |
On Thu, May 15, 2014 at 12:55 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
Jeff Janes wrote:Bah. Of course :-(
> If you have a database with a large table in it that has just passed
> autovacuum_freeze_max_age, all future workers will be funnelled into that
> database until the wrap-around completes. But only one of those workers
> can actually vacuum the one table which is holding back the frozenxid.
> Maybe the 2nd worker to come along will find other useful work to do, but
> eventually all the vacuuming that needs doing is already in progress, and
> so each worker starts up, gets directed to this database, finds it can't
> help, and exits. So all other databases are entirely starved of
> autovacuuming for the entire duration of the wrap-around vacuuming of this
> one large table.
Note that if you have two databases in danger of wraparound, the oldest
will always be chosen until it's no longer in danger. Ignoring the
second one past freeze_max_age seems bad also.
I'm not sure how bad that is. If you really do want to get the frozenxid advanced as soon as possible, it makes sense to focus on one at a time, rather than splitting the available IO throughput between two of them. So I wouldn't go out of my way to enable two to run at the same time, nor go out of my way to prevent it.
If most wrap around scans were done as part of a true emergency it would make sense to forbid all other vacuums (but only if you also automatically disabled autovacuum_vacuum_cost_delay as part of the emergency) so as not to divide up the IO throughput. But most are not emergencies, as 200,000,000 is a long way from 2,000,000,000.
This code is in autovacuum.c, do_start_worker(). Not sure what does
your proposal look like in terms of code.
I wasn't sure either, I was mostly trying the analyze the situation. But I decided just moving the "skipit" chunk of code to above the wrap-around code might work for experimental purposes, as attached. It has been running for a few of hours that way and I no longer see the frenzies occurring whenever pgbench_history gets vacuumed..
But I can't figure out why we sometimes use adl_next_worker and sometimes use last_autovac_time, which makes me question how much I really understand this code.
I think that instead of
trying to get a single target database in that foreach loop, we could
try to build a prioritized list (in-wraparound-danger first, then
in-multixid-wraparound danger, then the one with the oldest autovac time
of all the ones that remain); then recheck the wrap-around condition by
seeing whether there are other workers in that database that started
after the wraparound condition appeared.
I think we would want to check for one worker that is still running, and at least one other worker that started and completed since the wraparound threshold was exceeded. If there are multiple tables in the database that need full scanning, it would make sense to have multiple workers. But if a worker already started and finished without increasing the frozenxid and, another attempt probably won't accomplish much either. But I have no idea how to do that bookkeeping, or how much of an improvement it would be over something simpler.
Cheers,
Jeff
Вложения
В списке pgsql-hackers по дате отправления: