Skip site navigation (1) Skip section navigation (2)

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: (view raw, whole thread or download thread mbox)
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!

Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB:        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 yet?

In response to

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group