Thread: 7.3.1 takes long time to vacuum table?
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! The reason I am concerned is that before performing the update, the vacuum full would normally take about 12 hours and that was with a trigger and 5 indexes on the table. This time, before initiating the vacuum, all the indexes were dropped, and a single update performed on 1 field over the entire table. I understand that postgres has to compact the valid tuples down to the front of the file after removing the previous ones, but should it really take this long on such a powerful machine? Or have I made a gross error somewhere in the configuration? When I first configured the database, vacuuming the table took nearly 20 hours, but thanks to a post in the archives, I found some suggestions to increase vacuum mem & FSM size and reduce the number of buffers which got it down to its normal time of about 12 hours :) Here are the settings I changed from the defaults based on that post: Max_fsm_relations = 50000 Max_fsm_pages = 5000000 Vacuum_mem = 65535 Fsync = false 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? Cheers, Mark. --- Mark Cave-Ayland Webbased Ltd. Tamar Science Park Derriford Plymouth PL6 8BX England Tel: +44 (0)1752 764445 Fax: +44 (0)1752 764446 This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
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@svana.org> http://svana.org/kleptog/ > Support bacteria! They're the only culture some people have.
Attachment
On Wednesday 19 February 2003 02:14, Martijn van Oosterhout wrote:
> On Tue, Feb 18, 2003 at 03:20:36PM -0000, Mark Cave-Ayland wrote:
> > Hi everyone,
> >
> 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.
>
Try Vacuum Verbose; It does not print a message out every 10 minites but it
should tell you that somthing is happerning, oh and whats happerning if you
can make any sence of the messages.....
Peter Childs