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

On Mon, Mar 21, 2011 at 8:14 PM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> wrote:
> Thanks Merlin, Scott.
>
> First, yes, I can increase maintenance_work_memory. I have 8GB RAM in
> total, and sure, I can dedicate 1GB of it to PG. Currently PG is the
> most intensive software here.

If we're talking maintenance work mem, then you might want to set it
for a single connection.

set maintenance_work_mem='1000MB';
reindex yada yada;

etc. So it's not global, just local.

> Second, how can I check if there are other things going on in the
> database while i REINDEX? Maybe some kind of vacuum is going on, but
> isn't that supposed to wait while REINDEX is happening for at least
> this table?

OK, my main point has been that if autovacuum is running well enough,
then you don't need reindex, and if you are running it it's a
maintenance thing you shouldn't have to schedule all the time, but
only run until you get autovac tuned up enough to handle your db
during the day. however, I know sometimes you're stuck with what
you're stuck with.

You can see what else is running with the pg_stats_activity view,
which will show you all running queries. That and iotop cna show you
which processes are chewing up how much IO. The other pg_stat_*
tables can get you a good idea of what's happening to your tables in
the database. iostat and vmstat can give you an idea how much IO
bandwidth you're using.

If a vacuum starts after the reindex it will either wait or abort and
not get in the way. If a vacuum is already running I'm not sure if it
will get killed or not.