Re: REINDEX takes half a day (and still not complete!)

On Sun, Apr 17, 2011 at 9:30 AM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)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?