From: Scott Marlowe <scott.marlowe@gmail.com>
To: Phoenix Kiula <phoenix.kiula@gmail.com>
Cc: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: REINDEX takes half a day (and still not complete!)
Date: Sun, 17 Apr 2011 09:44:04 -0600
Message-ID: <BANLkTin9bB+LAScMMBnuJn9uROXOkPsQLw@mail.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 Sun, Apr 17, 2011 at 9: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.
Upgrade to something more modern than 8.2.x. Autovacuum was still
very much in its infancy back then. 9.0 or higher is a good choice.
What do iostat -xd 10 and vmstat 10 and top say about these processes
when they're running. "It's taking a really long time and seems like
it's hanging" tells us nothing useful. Your OS has tools to let you
figure out what's bottlenecking your operations, so get familiar with
them and let us know what they tell you. These are all suggestions I
made before which you have now classified as "not answering your
questions" so I'm getting a little tired of helping you when you don't
seem interested in helping yourself.
What are your vacuum and autovacuum costing values set to? Can you
make vacuum and / or autovacuum more aggresive?
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: scott.marlowe@gmail.com, phoenix.kiula@gmail.com, mmoncure@gmail.com
Subject: Re: REINDEX takes half a day (and still not complete!)
In-Reply-To: <BANLkTin9bB+LAScMMBnuJn9uROXOkPsQLw@mail.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