On Sat, Feb 14, 2015 at 10:14 AM, <jd@ods.org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 12772
> Logged by: Jason DiCioccio
> Email address: jd@ods.org
> PostgreSQL version: 9.3.6
> Operating system: Ubuntu 14.04 (Linux 3.13.0-40-generic)
> Description:
>
> Here's the situation I ran into:
>
> autovacuum was vacuuming a large table in database 'db1' (to prevent txid
> wraparound). It turns out that one of the indexes of this table was
> corrupted from the 9.3.4 WAL issue. So VACUUM failed repeatedly.
> Nonetheless, autovacuum kept trying.
>
> Meanwhile in database 'db2', there were a number of tables in serious need
> of vacuuming. However, autovacuum would never touch these, even though
> there were far less than autovacuum_max_workers running. Other tables in
> database 'db1' WERE being vacuumed, however.
>
> So it appears that the logic is that autovacuum operates solely on one
> database at a time. Even if there is only one table that needs vacuuming
> in
> that first database, it will not spawn any workers to vacuum tables that do
> need vacuuming in a second database until it has completed vacuuming the
> necessary tables from the first database.
>
> This, to me, is unexpected behavior. I'd expect autovacuum to not act as
> if
> there were a large barrier between databases and to vacuum any table that
> need it and that the configuration permits.
>
(Sorry, accidentally did not include the list on my original reply, here is
a copy to the list)
This is a known issue, but so far there hasn't be a lot of urgency to fix
it, probably because of a lack of complaints from the field until now, and
because there are a few ways to address it and no one has forcefully
advocated for one particular solution.
Basically it considers the database being in need of wrap around vacuum as
an emergency (even thought it probably isn't) and funnels all future
workers to it. But when only one table is in need of that wrap around
vacuum, all the other workers can't accomplish anything in that database,
and other databases get starved.
http://www.postgresql.org/message-id/CAMkU=1yE4YyCC00W_GcNoOZ4X2qxF7x5DUAR_kMt-Ta=YPyFPQ@mail.gmail.com
Cheers,
Jeff