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

On Mon, 29 Feb 2016 18:33:50 -0300
Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:

> Hi David, did you ever post an updated version of this patch?

No. Let me fix that now. I've attached my current revision of the patch
based on master. This version is significantly better than the original
version and resolves multiple issues:

- autovacuum workers no longer race each other
- autovacuum workers do not revacuum each others tables
- autovacuums workers no longer thrash the stats collector which saves a
lot of IO when the stats is large.

Hopefully the earlier discussion and the comments in the patch are
sufficient, but please ask questions if it is not clear.

The result is that on a freshly created 40,000 table database with tiny
tables that all need an initial analyze the unpatched postgres saturates
an SSD updating the stats and manages to process less than tables per
minute. With the attached patch it processes several thousand tables per
minute.

The following is a summary of strace output for the autovacuum workers
and the stats collector while the 40k table test is running. The counts and
times are the cost per table.

postgresql 9.5: 85 tables per minute.

Operations per Table
calls msec system call [ 4 autovacuum workers ]
------ ------ -------------------
19.46 196.09 select(0, <<< Waiting for stats snapshot
3.26 1040.46 semop(43188238, <<< Waiting for AutovacuumScheduleLock
2.05 0.83 sendto(8, <<< Asking for stats snapshot

calls msec system call [ stats collector ]
------ ------ -------------------
3.02 0.05 recvfrom(8, <<< Request for snapshot refresh
1.55 248.64 rename("pg_stat_tmp/db_16385.tmp", <<< Snapshot refresh

+ autovacuum contention patch: 5225 tables per minute

Operations per Table
calls msec system call [ 4 autovacuum workers ]
------ ------ -------------------
0.63 6.34 select(0, <<< Waiting for stats snapshot
0.21 0.01 sendto(8, <<< Asking for stats snapshot
0.07 0.00 semop(43712518, <<< Waiting for AutovacuumLock

calls msec system call [ stats collector ]
------ ------ -------------------
0.40 0.01 recvfrom(8, <<< Request for snapshot refresh
0.04 6.75 rename("pg_stat_tmp/db_16385.tmp", <<< Snapshot refresh

Regards,

-dg

--
David Gould daveg(at)sonic(dot)net
If simplicity worked, the world would be overrun with insects.