Skip site navigation (1) Skip section navigation (2)

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

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Cc: Merlin Moncure <mmoncure(at)gmail(dot)com>, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com>
Subject: Re: REINDEX takes half a day (and still not complete!)
Date: 2011-03-22 02:48:34
Message-ID: AANLkTimtQhGZX4HUBaycG_-poNwg3noVOA5-NyzkC=Ay@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.

In response to

pgsql-performance by date

Next:From: markDate: 2011-03-22 02:55:11
Subject: Re: Select in subselect vs select = any array
Previous:From: Merlin MoncureDate: 2011-03-21 13:28:35
Subject: Re: REINDEX takes half a day (and still not complete!)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group