From: Kenneth Marshall <ktm@rice.edu>
To: Phoenix Kiula <phoenix.kiula@gmail.com>
Cc: Greg Smith <greg@2ndquadrant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: REINDEX takes half a day (and still not complete!)
Date: Sat, 30 Apr 2011 09:34:21 -0500
Message-ID: <20110430143421.GA26488@aart.is.rice.edu> (raw)
In-Reply-To: <BANLkTimqib72+6GUOtuFey9XSR9woBE2zA@mail.gmail.com>
References: <AANLkTim1aO2hG+D4h_VFXas8GTL6TQMLj7aDc7tJ0vok@mail.gmail.com>
<AANLkTinjsS7Aj0ZoMVq3ZDhKepBsZ_HOpBqEjRrabk_J@mail.gmail.com>
<BANLkTinq7KSstviqjGfnaKh7YOfan7sjig@mail.gmail.com>
<34CE0668-2A83-4BE4-B191-86871C2F8334@gmail.com>
<4DBBC331.7080200@2ndQuadrant.com>
<BANLkTimqib72+6GUOtuFey9XSR9woBE2zA@mail.gmail.com>
On Sat, Apr 30, 2011 at 05:26:36PM +0800, Phoenix Kiula wrote:
> On Sat, Apr 30, 2011 at 4:07 PM, Greg Smith <greg@2ndquadrant.com> wrote:
> > On 04/23/2011 03:44 PM, Robert Haas wrote:
> >>
> >> On Apr 17, 2011, at 11:30 AM, Phoenix Kiula<phoenix.kiula@gmail.com>
> >> ?wrote:
> >>
> >>>
> >>> Postgres is 8.2.9.
> >>>
> >>>
> >>
> >> 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...
> >>
> >
> > Don't know if it was for this reason or not for not mentioning it by others,
> > but CLUSTER isn't so great in 8.2. ?The whole "not MVCC-safe" bit does not
> > inspire confidence on a production server.
>
>
>
>
> To everyone. Thanks so much for everything, truly. We have managed to
> salvage the data by exporting it in bits and pieces.
>
> 1. First the schema only
> 2. Then pg_dump of specific small tables
> 3. Then pg_dump of timed bits of the big mammoth table
>
> Not to jinx it, but the newer hardware seems to be doing well. I am on
> 9.0.4 now and it's pretty fast.
>
> Also, as has been mentioned in this thread and other discussions on
> the list, just doing a dump and then fresh reload has compacted the DB
> to nearly 1/3rd of its previously reported size!
>
> I suppose that's what I am going to do on a periodic basis from now
> on. There is a lot of DELETE/UPDATE activity. But I wonder if the
> vacuum stuff really should do something that's similar in function?
> What do the high-end enterprise folks do -- surely they can't be
> dumping/restoring every quarter or so....or are they?
>
> Anyway, many many thanks to the lovely folks on this list. Much appreciated!
>
The autovacuum and space management in 9.0 is dramatically more effective
and efficient then that of 8.2. Unless you have an odd corner-case there
really should be no reason for a periodic dump/restore. This is not your
grandmother's Oldsmobile... :)
Regards,
Ken
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: ktm@rice.edu, phoenix.kiula@gmail.com, greg@2ndquadrant.com
Subject: Re: REINDEX takes half a day (and still not complete!)
In-Reply-To: <20110430143421.GA26488@aart.is.rice.edu>
* 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