Re: 7.3.1 takes long time to vacuum table? - Mailing list pgsql-general

Hi Martijn,

Thanks again for your reply.

> -----Original Message-----
> From: Martijn van Oosterhout [mailto:kleptog@svana.org]
> Sent: 19 February 2003 12:11
> To: Mark Cave-Ayland
> Cc: PostgreSQL General
> Subject: Re: [GENERAL] 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.

Backwards? Ouch I guess that will break most caching strategies! But as
I said before, do you know why it only appears to be moving one or two
pages at a time before seeking again???

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

The trace lasted about a couple of minutes. I've listed the filesystems
from /proc and uploaded them to
http://www.infomapper.com/strace/fd.log.txt for you to look - I have a
feeling you would find it useful to see the total number of files open
in terms of their size and quantity....

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

Hmmm, a little big bigger than I was expecting, although I guess that
contains old and new pages. Here is the select result:

SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
             relname             | relpages
---------------------------------+----------
 res                        |  5586167

...so at 8Kb/page then that's about 42Gb.


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

Agreed! If it ruins any caching then in my view it's something that has
to change in order to keep performance. While there may be a penalty to
pay on smaller tables, the benefits of caching would more than make up
for the cost of going forwards - imagine how slow CPUs would be if
everything was a cache miss....

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

Yes please, that would be really useful for us to know.


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.