Re: problems with new vacuum (??)

From: Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz>
To: Barry Lind <barry(at)xythos(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: problems with new vacuum (??)
Date: 2002-01-02 08:29:59
Message-ID: 1009960199.1949.2.camel@kant.mcmillan.net.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Wed, 2002-01-02 at 13:31, Barry Lind wrote:
> Over the last two days I have been struggling with running vacuum on a
> 7.2b4 database that is running in a production environment. This was
> essentially the first time I ran vacuum on this database since it was
> upgraded to 7.2. This database is characterized by one large table that
> has many inserts and deletes, however generally contains zero rows. So
> over the course of the last few weeks this table had grown in size to
> about 2.5G (or more correctly the corresponding toast table grew that
> large).
>
> So the first problem I had was that the vaccum (regular vacuum not full
> vacuum) took a very long time on this table (2+ hours). Now I would
> expect it to take a while, so that in and of itself isn't a problem.
> But while this vacuum was running the rest of the system was performing
> very poorly. Opperations that usually are subsecond, where taking
> minutes to complete. At first I thought there was some sort of locking
> problem, but these opperations did complete, but after a very long time.

Is it possible that you waited until a point when the work that vacuum
has to do is being undone faster by the new transactions coming
through? This might be complicated by the fact that (from your vague
description) the table is heavily toasted.

Also, as a suggestion, if you can know there are zero records in the
table very often, why not TRUNCATE it at those times? That should be a
_lot_ quicker than vacuuming it!

Cheers,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Are you enrolled at http://schoolreunions.co.nz/ yet?

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Holger Krug 2002-01-02 09:26:52 Feature proposal: generalizing deferred trigger events
Previous Message Barry Lind 2002-01-02 05:23:44 Re: problems with new vacuum (??)