Re: BUG #13750: Autovacuum slows down with large numbers of tables. More workers makes it slower.

David Gould wrote:

> This is for an idle system with 100,000 new small tables to analyze. I ran
> all the test for an hour or 5000 tables processed. "jj" refers to the patch
> from Jeff Janes, "dg" refers to the attached patch (same as previous).
>
> /autovacuum actions per minute/
> workers 9.5b1 jj dg
> ------- ----- ---- -----
> 1 183 171 285
> 4 45 212 1158
> 8 23 462 1225

Nice numbers.

> Could someone please take a look at the patch and comment? Thanks.

1. What's with all the FIXMEs?

2. I think you need more of an explanation of what your patch actually
does.

3. Do we want to backpatch? Changes in behavior aren't acceptable on
existing branches, because it might destabilize autovacuum behavior
that's been carefully tuned in existing systems. So if we want
something to backpatch, ideally it shouldn't change the ordering in
which tables are vacuumed, and instead arrive at the same results
faster. (I don't care about this restriction myself, but others do and
strongly so.)

4. In the master branch, behavior changes are acceptable, so we can do
something more invasive.

5. Is it easier to use a binary heap rather than the OID list thing you
have? (see src/include/lib/binaryheap.h) I don't think so, but it's
worth asking. Note that older branches don't have this, so
backpatchable should not rely on it.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services