From: Tom Lane <tgl@sss.pgh.pa.us>
To: Gavin Hamill <gdh@laterooms.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: VACUUMs take twice as long across all nodes
Date: Fri, 27 Oct 2006 14:07:43 -0400
Message-ID: <7564.1161972463@sss.pgh.pa.us> (raw)
In-Reply-To: <20061027102025.8183c5d9.gdh@laterooms.com>
References: <20061026095827.9e85e1c0.gdh@laterooms.com>
<20196.1161874041@sss.pgh.pa.us>
<20061026160609.5e94e581.gdh@laterooms.com>
<20061026191728.GD26892@nasby.net>
<20061026213556.174bd7b5.gdh@laterooms.com>
<20061026220937.GA6866@phlogiston.dyndns.org>
<20061027102025.8183c5d9.gdh@laterooms.com>
Gavin Hamill <gdh@laterooms.com> writes:
> 2006-10-27 08:37:12 UTC INFO: vacuuming "public.Allocation"
> 2006-10-27 08:37:21 UTC INFO: "Allocation": found 56449 removable, 4989360 nonremovable row versions in 47158 pages
> 2006-10-27 08:37:21 UTC DETAIL: 0 dead row versions cannot be removed yet.
> Nonremovable row versions range from 64 to 72 bytes long.
> There were 1 unused item pointers.
> Total free space (including removable row versions) is 5960056 bytes.
> 13 pages are or will become empty, including 0 at the end of the table.
> 5258 pages containing 4282736 free bytes are potential move destinations.
> CPU 0.16s/0.07u sec elapsed 9.55 sec.
> 2006-10-27 08:44:25 UTC INFO: index "allocation_pkey" now contains 4989360 row versions in 102198 pages
> 2006-10-27 08:44:25 UTC DETAIL: 56449 index row versions were removed.
> 1371 index pages have been deleted, 1371 are currently reusable.
> CPU 1.02s/0.38u sec elapsed 423.22 sec.
So the time is all in index vacuuming, eh? I think what's happening is
that the physical order of the index is degrading over time, and so the
vacuum scan takes longer due to more seeking. Can you afford to do a
REINDEX? If this theory is correct that should drive the time back
down.
8.2 has rewritten btree index vacuuming code that scans the index in
physical not logical order, so this problem should largely go away in
8.2, but in existing releases I can't see much you can do about it
except REINDEX when things get slow.
regards, tom lane
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-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, gdh@laterooms.com
Subject: Re: VACUUMs take twice as long across all nodes
In-Reply-To: <7564.1161972463@sss.pgh.pa.us>
* 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