Re: BUG #13750: Autovacuum slows down with large numbers of tables. More workers makes it slower. - Mailing list pgsql-bugs

On Sat, 31 Oct 2015 01:37:14 -0700
David Gould <daveg@sonic.net> wrote:

I've added numbers from the same test setup with Jeff Janes patch.

postgresql-9.5beta1 with Jeff Janes vac_move_lock.patch:

seconds  elapsed  actions   chunk   sec/av   av/min
  116.1    116.1     1000    1000    0.116    516.8
  224.0    107.9     2000    1000    0.108    556.1
  356.4    132.4     3000    1000    0.132    453.2
  489.8    133.4     4000    1000    0.133    449.8
  602.5    112.7     5000    1000    0.113    532.3
  706.6    104.1     6000    1000    0.104    576.3
  801.9     95.3     7000    1000    0.095    629.5
  886.3     84.4     8000    1000    0.084    710.9
  983.0     96.7     9000    1000    0.097    620.5
 1074.5     91.5    10000    1000    0.091    656.1
 1168.2     93.7    11000    1000    0.094    640.1
 1259.5     91.3    12000    1000    0.091    656.9
 1350.3     90.8    13000    1000    0.091    661.1
 1434.2     83.9    14000    1000    0.084    715.4
 1516.0     81.9    15000    1000    0.082    733.0

ps sample during run. Note that stats collector is very busy.
 3006 ?        Ds     2:43 postgres: stats collector process
 3014 ?        Ss     1:42 postgres: autovacuum worker process   avac
 3044 ?        Ss     1:29 postgres: autovacuum worker process   avac
 3053 ?        Ds     1:27 postgres: autovacuum worker process   avac
 3060 ?        Ss     1:25 postgres: autovacuum worker process   avac


> Unpatched postgresql 9.5beta1 (I let it run for over an hour but it did not
> get very far):
>
> seconds  elapsed  actions   chunk   sec/av   av/min
>   430.1    430.1     1000    1000    0.430    139.5
>  1181.2    751.1     2000    1000    0.751     79.9
>  1954.0    772.7     3000    1000    0.773     77.6
>  2618.5    664.5     4000    1000    0.664     90.3
>  3305.7    687.2     5000    1000    0.687     87.3
>  4010.1    704.4     6000    1000    0.704     85.2
>
>
> A ps sample from partway through the run. Most of the cpu used is by
> the stats collector:
> $ ps xww | awk '/collector|autovacuum worker/ && !/awk/'
> 30212 ?        Ss     0:00 postgres: autovacuum launcher process
> 30213 ?        Ds     0:55 postgres: stats collector process
> 30221 ?        Ss     0:23 postgres: autovacuum worker process   avac
> 30231 ?        Ss     0:12 postgres: autovacuum worker process   avac
> 30243 ?        Ss     0:11 postgres: autovacuum worker process   avac
> 30257 ?        Ss     0:10 postgres: autovacuum worker process   avac


> postgresql 9.5beta1 plus my ordered oids/high watermark autovacuum patch:
>
> seconds  elapsed  actions   chunk   sec/av   av/min
>    13.4     13.4     1000    1000    0.013   4471.9
>    22.9      9.5     2000    1000    0.010   6299.9
>    31.9      8.9     3000    1000    0.009   6718.9
>    40.2      8.3     4000    1000    0.008   7220.2
>    52.2     12.1     5000    1000    0.012   4973.1
>    59.5      7.2     6000    1000    0.007   8318.3
>    69.4     10.0     7000    1000    0.010   6024.7
>    78.9      9.5     8000    1000    0.010   6311.8
>    93.5     14.6     9000    1000    0.015   4105.1
>   104.3     10.7    10000    1000    0.011   5601.7
>   114.4     10.2    11000    1000    0.010   5887.0
>   127.5     13.1    12000    1000    0.013   4580.9
>   140.1     12.6    13000    1000    0.013   4763.0
>   153.8     13.7    14000    1000    0.014   4388.9
>   166.7     12.9    15000    1000    0.013   4638.6
,,,
> A ps sample from partway through the run. Most of the cpu used is by
> workers, not the collector.
> $ ps xww | awk '/collector|autovacuum worker/ && !/awk/'
>   872 ?        Ds     0:49 postgres: stats collector process
>   882 ?        Ds     3:42 postgres: autovacuum worker process   avac
>   953 ?        Ds     3:21 postgres: autovacuum worker process   avac
>  1062 ?        Ds     2:56 postgres: autovacuum worker process   avac
>  1090 ?        Ds     2:34 postgres: autovacuum worker process   avac

-dg


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