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

On Fri, Apr 18, 2008 at 10:34 AM, Jeffrey Baker <jwbaker(at)gmail(dot)com> wrote:
>
> On Fri, Apr 18, 2008 at 10:32 AM, Jeffrey Baker <jwbaker(at)gmail(dot)com> wrote:
> >
> > On Fri, Apr 18, 2008 at 10:03 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > > "Jeffrey Baker" <jwbaker(at)gmail(dot)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