Re: 7.3.1 takes long time to vacuum table?

On Wed, Feb 19, 2003 at 11:03:12AM -0000, Mark Cave-Ayland wrote:
> Ok, I've managed to do that.... offset just seems to keep jumping around
> though, not much of a specific pattern... although it does seem to be
> reading from 2 separate files :( But what IS interesting is that between
> each seek(), postgres does 2 reads of 8k (which I guess is a page) and
> then 4 writes! This I don't understand? Surely given the memory
> parameters then it should read as many pages into memory as possible,
> sort them, then seek back and write them? What appears to be happening
> is that it is only one or two pages are being moved at a time which
> seems really inefficient.

Hmm, odd. The offset is going *backwards*. It's starting at 981934080 and
made it down to 969719808. It looks like it's reading each page. I don't
think I need to tell you that from a caching point of view, it's not probably
not as good as going forward.

> Or is the assumption here that by limiting the pages being moved around,
> more memory can be given to the OS so it can cache aggressively? I've
> uploaded the strace file to
> http://www.infomapper.com/strace/strace.log.txt if you (or anyone else)
> would be interested in taking a look - I logged about 700ks worth.

How long did it take to get that trace? Also, what are file descriptors 58,
97 and 114?

> The vacuum has emitted a line of statistics within the first few hours
> which reads the following:
>
> INFO:  --Relation public.res--
> INFO:  Pages 9167290: Changed 5366493, reaped 6000857, Empty 0, New 0;
> Tup 72475389: Vac 105298234, Keep/VTL 0/0, UnUsed 221144, MinLen 72,
> MaxLen 727; Re-using: Free/Avail. Space 48478844644/48455035400;
> EndEmpty/Avail. Pages 0/8458521.
>         CPU 733.62s/151.22u sec elapsed 4656.51 sec.

Ok, that means it has finished the vacuum stage (since that's printed at the
end of scan_heap). It's now going through the heap compacting. From that
output it appears that your table is around 75Gig (9 million 8K pages)!
You're into the repair_frag stage where indeed it scans through the table
backwards.

While I can see the argument for going backwards, from a caching perspective
I think it's terrible. Especially in the case where the entire table has
been replaced, the entire exercise becomes a very expensive copy operation.

> But now it's just sat there churning away.... I guess the above was the
> result of marking which tuples were to be kept and now I'm guessing its
> in the process of moving data around. So yes, some additional
> notification during this phase would be very useful for large tables
> like this.

Let me know what those file descriptors point to and we can probably work
out how far along it is.

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Support bacteria! They're the only culture some people have.