pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Goldner <mgoldner@agmednet.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Is my vacuumdb stuck in a loop?
Date: Sun, 02 Mar 2008 11:31:58 -0500
Message-ID: <C3F040AE.1E4AD%mgoldner@agmednet.com> (raw)
In-Reply-To: <20299.1204474559@sss.pgh.pa.us>

On 3/2/08 11:15 AM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

> "Michael Goldner" <MGoldner@agmednet.com> writes:
>> Am I stuck in a loop, or is this happening because the size of the relation
>> is so large that postgres is operating on smaller chunks?
> 
> It's removing as many dead rows at a time as it can handle.  Arithmetic
> suggests that you've got maintenance_work_mem set to 64MB, which would
> be enough room to process 11184810 rows per index scanning cycle.
> 
That is exactly correct regarding maintenance_work_mem.  Maybe I should
double this.  I think my server has sufficient memory.  Is there any
downside?

> The fact that there are so many dead large objects is what I'd be
> worrying about.  Does that square with your sense of what you've
> removed, or does it suggest you've got a large object leak?  Do you
> use contrib/lo and/or contrib/vacuumlo to manage them?

I am in the process of archiving off about 1.4 million large objects from a
total of 1.7 million.  I removed about 700,000 before running the vacuum.
Each large object averages about 256k, with outliers as big as 100MB.  At 2k
per row in pg_largeobject (if I understand correctly), the number of dead
large objects doesn't seem unreasonable.

> 
> The numbers also suggest that you might be removing all or nearly
> all of the rows in pg_largeobject.  If so, a CLUSTER on it might
> be more effective than VACUUM as a one-shot cleanup method.
>

My understanding is that CLUSTER is a blocking operation.  My database
supports a 24x7 operation, so I cannot bring the system offline for extended
periods.  How fast is the "CLUSTER" operation?  Given my database, am I
looking at minutes, hours, or days?  Can a CLUSTER be cancelled without
negative consequences if I see it is taking longer than I can allow?

Thanks

-- 
Mike Goldner




view thread (4+ messages)  latest in thread

Message-ID: <C3F040AE.1E4AD%mgoldner@agmednet.com>
Permalink:  ../C3F040AE.1E4AD%25mgoldner@agmednet.com/
Also on:    postgresql.org/message-id/C3F040AE.1E4AD%mgoldner@agmednet.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: mgoldner@agmednet.com, tgl@sss.pgh.pa.us
  Subject: Re: Is my vacuumdb stuck in a loop?
  In-Reply-To: <C3F040AE.1E4AD%mgoldner@agmednet.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox