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