Re: 7.3.1 takes long time to vacuum table?

On Tue, Feb 18, 2003 at 03:20:36PM -0000, Mark Cave-Ayland wrote:
> Hi everyone,
>
> Does anyone know of any issues with 7.3.1 which would cause it to take a
> long time to do a vacuum full? We have a resource table consisting of
> about 70M records and we have recently performed an update query on the
> entire table and now I'm trying to do a vacuum full to reclaim back the
> disk space. So far on a dual PIII 1.4GHz machine with hardware RAID5 and
> 2GB RAM, the vacuum full verbose is still running after 30 hours!

Ok, this is a hard one. Doing a vacuum full is very hard on the disk cache.
It basically copies a lot of tuples around. In your case it's going to be
copying every tuple from somewhere near the end of the table to somewhere
near the beginning. This makes the pattern of disk access something like:

seek, read, seek, write, seek, read, seek, write, ...

Which, not surprisingly, sucks (especially from RAID5 I think, but I'm not
sure about that). Note this varies a bit between versions of Unix,
postgresql and your C library.

My only advice is that you can use strace to work out approximatly where
it's up to. Use /proc/ to work out which file descriptor is the table you're
working with and then strace the backend (the -p option) to work out which
part it is reading from. It'll look like:

seek(<fd>,<offset>,SEEK_SET) = <offset>
read(<fd>,"lots of rubbish", 8192) = 8192

It's the offset you want, it may jump around a bit but it should be
increasing on the whole. If your table is split into multiple files because
it's over 1GB, take this into account when working out how far it's in.

This is probably a good argument to have VACUUM emit a notice every 10
minutes or so giving some indication of its progress. I don't know how hard
this would be.

> I have also set shmmax to 800Mb just to give things some breathing
> space. One thing I have noticed is that the postmaster process running
> the vacuum has now reached 1Gb of memory and looks like it is beginning
> to touch swap(!) which is going to slow things even more. Can anyone
> help me out and reduce the time it takes to do this vacuum?

The only thing I can suggest is that SELECT * INTO newtables FROM table; may
have been faster, though it shouldn't be.

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