On Fri, Oct 30, 2015 at 8:40 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
>> David Gould wrote:
>>> Anyway, they are not actually vacuuming. They are waiting on the
>>> VacuumScheduleLock. And requesting freshs snapshots from the
>>> stats_collector.
>
>> Oh, I see. Interesting. Proposals welcome. I especially dislike the
>> ("very_expensive") pgstat check.
>
> Couldn't we simply move that out of the locked stanza? That is, if no
> other worker is working on the table, claim it, and release the lock
> immediately. Then do the "very expensive" check. If that fails, we
> have to re-take the lock to un-claim the table, but that sounds OK.
The attached patch does that. In a system with 4 CPUs and that had
100,000 tables, with a big chunk of them in need of vacuuming, and
with 30 worker processes, this increased the throughput by a factor of
40. Presumably it will do even better with more CPUs.
It is still horribly inefficient, but 40 times less so.
Cheers,
Jeff