Re: 3-days-long vacuum of 20GB table

On Fri, Apr 18, 2008 at 10:34 AM, Jeffrey Baker <jwbaker@gmail.com> wrote:
>
> On Fri, Apr 18, 2008 at 10:32 AM, Jeffrey Baker <jwbaker@gmail.com> wrote:
>  >
>  > On Fri, Apr 18, 2008 at 10:03 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>  >  > "Jeffrey Baker" <jwbaker@gmail.com> writes:
>  >  >  > This autovacuum has been hammering my server with purely random i/o
>  >  >  > for half a week.  The table is only 20GB and the i/o subsystem is good
>  >  >  > for 250MB/s sequential and a solid 5kiops.  When should I expect it to
>  >  >  > end (if ever)?
>  >  >
>  >  >  What have you got maintenance_work_mem set to?  Which PG version
>  >  >  exactly?
>  >
>  >  This is 8.1.9 on Linux x86_64,
>  >
>  >  # show maintenance_work_mem ;
>  >   maintenance_work_mem
>  >  ----------------------
>  >   16384
>
>  That appears to be the default.  I will try increasing this.  Can I
>  increase it globally from a single backend, so that all other backends
>  pick up the change, or do I have to restart the instance?

I increased it to 1GB, restarted the vacuum, and system performance
seems the same.  The root of the problem, that an entire CPU is in the
iowait state and the storage device is doing random i/o, is unchanged:

 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
 1  1  30328  53632     60 6914716    0    0   904  2960 1216 4720  1  1 74 23
 0  1  30328  52492     60 6916036    0    0  1152  1380  948 3637  0  0 75 24
 0  1  30328  49600     60 6917680    0    0  1160  1420 1055 4191  1  1 75 24
 0  1  30328  49404     60 6919000    0    0  1048  1308 1133 5054  2  2 73 23
 0  1  30328  47844     60 6921096    0    0  1552  1788 1002 3701  1  1 75 23

At that rate it will take a month.  Compare the load generated by
create table foo as select * from bar:

 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
 2  2  30328  46580     60 6911024    0    0 145156   408 2006 10729 52  8 17 23
 3  1  30328  46240     60 6900976    0    0 133312   224 1834 10005 23 12 42 23
 1  3  30328  60700     60 6902056    0    0 121480   172 1538 10629 22 14 32 32
 1  2  30328  49520     60 6914204    0    0 122344   256 1408 14374 13 17 41 28
 1  2  30328  47844     60 6915960    0    0 127752   248 1313 9452 16 15 42 27

That's rather more like it.  I guess I always imagined that VACUUM was
a sort of linear process, not random, and that it should proceed at
sequential scan speeds.

-jwb