From: Robert Haas <robertmhaas@gmail.com>
To: Phoenix Kiula <phoenix.kiula@gmail.com>
Cc: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: REINDEX takes half a day (and still not complete!)
Date: Sat, 23 Apr 2011 15:44:23 -0400
Message-ID: <34CE0668-2A83-4BE4-B191-86871C2F8334@gmail.com> (raw)
In-Reply-To: <BANLkTinq7KSstviqjGfnaKh7YOfan7sjig@mail.gmail.com>
References: <AANLkTim1aO2hG+D4h_VFXas8GTL6TQMLj7aDc7tJ0vok@mail.gmail.com>
<AANLkTinjsS7Aj0ZoMVq3ZDhKepBsZ_HOpBqEjRrabk_J@mail.gmail.com>
<BANLkTinq7KSstviqjGfnaKh7YOfan7sjig@mail.gmail.com>
On Apr 17, 2011, at 11:30 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
> Sorry, rejuvenating a thread that was basically unanswered.
>
> I closed the database for any kinds of access to focus on maintenance
> operations, killed all earlier processes so that my maintenance is the
> only stuff going on.
>
> REINDEX is still taking 3 hours -- and it is still not finished!
>
> Similarly, if I cancel the REINDEX and issue a VACUUM ANALYZE VERBOSE,
> this too seems to just hang there on my big table.
>
> I changed the maintenance_work_men to 2GB for this operation. It's
> highly worrisome -- the above slow times are with 2GB of my server
> dedicated to Postgresql!!!!
>
> Surely this is not tenable for enterprise environments? I am on a
> 64bit RedHat server with dual CPU Intel Woodcrest or whatever that was
> called. Postgres is 8.2.9.
>
> How do DB folks do this with small maintenance windows? This is for a
> very high traffic website so it's beginning to get embarrassing.
>
> Would appreciate any thoughts or pointers.
An upgrade would probably help you a lot, and as others have said it sounds like your hardware is failing, so you probably want to deal with that first.
I am a bit surprised, however, that no one seems to have mentioned using CLUSTER rather than VACUUM or REINDEX. Sometimes that's worth a try...
...Robert
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: robertmhaas@gmail.com, phoenix.kiula@gmail.com, mmoncure@gmail.com
Subject: Re: REINDEX takes half a day (and still not complete!)
In-Reply-To: <34CE0668-2A83-4BE4-B191-86871C2F8334@gmail.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