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

I've collected strace output for a one minute interval with 4 autovacuum
workers on my test setup for stock 9.5beta1, Jeff Janes vac_mov_lock patch,
and my ordered oids / high watermark patch. These results are normalized to
times and number of calls per table analyzed by autovacuum.

To refresh, on this setup:

Patch Version       TPM
none                 85
vac_mov_lock        600
ordered oids       5225

Strace summary:

postgresql 9.5 beta1 cost per table @ 85 tables per minute.
-----------------------------------------------------------

 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



9.5beta1 + Jeff Janes vac_mov_lock.patch cost per table @ 600 tpm
-----------------------------------------------------------------

 calls    msec    system call        [ 4 autovacuum workers ]
------  ------    -------------------
 24.90  260.16    select(0,           <<< Waiting for stats snapshot
  1.41    0.02    sendto(8,           <<< Asking for stats snapshot

 calls    msec    system call        [ stats collector ]
------  ------    -------------------
  1.29    0.02    recvfrom(8,         <<< Request for snapshot refresh
  0.59   62.40    rename("pg_stat_tmp/db_16385.tmp",  <<< Snapshot refresh



9.5beta1 + David Gould autovacuum ordering patch cost per table @ 5225 tpm
--------------------------------------------------------------------------

 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

-dg

--
David Gould                                   daveg@sonic.net
If simplicity worked, the world would be overrun with insects.