Re: 7.3.1 takes long time to vacuum table?

> -----Original Message-----
> From: Martijn van Oosterhout [mailto:kleptog@svana.org]
> Sent: 19 February 2003 02:14
> To: Mark Cave-Ayland
> Cc: PostgreSQL General
> Subject: Re: [GENERAL] 7.3.1 takes long time to vacuum table?

Hi Martijn,

Thanks for taking the time to look at this one.

> 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.

Hmmm.... so the news is not good then :( We timed an insert of about
800,000 records into the DB under 7.2.1 and that was just over 10mins...
so extrapolating that out it would take at least 15 hours just to write
the direct 70M records to disk without the seeking.... *sigh*

> 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.

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.

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.

> 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.

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.

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.

> > 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.

My instinct would suggest this would be faster, given that the writing
would be guaranteed sequential, and a single sequential scan on the
table. The problem with this is that we don't have enough disk space to
maintain 2 copies of the table on one device at the moment as the table
takes about 40Gb space.

BTW the vacuum is of course still going as we enter the 50hr stage....


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.