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

On Sat, 31 Oct 2015 01:37:14 -0700
David Gould <daveg(at)sonic(dot)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(at)sonic(dot)net
If simplicity worked, the world would be overrun with insects.