From: Luke Lonergan <llonergan@greenplum.com>
To: John A Meinel <john@arbash-meinel.com>
To: Tomeh, Husam <htomeh@firstam.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: "Vacuum Full Analyze" taking so long
Date: Mon, 25 Jul 2005 17:51:48 -0700
Message-ID: <BF0AD734.9F38%llonergan@greenplum.com> (raw)
In-Reply-To: <42E5763C.7030804@arbash-meinel.com>
Husam,
On 7/25/05 4:31 PM, "John A Meinel" <john@arbash-meinel.com> wrote:
> Tomeh, Husam wrote:
>>
>> Nothing was running except the job. The server did not look stressed out
>> looking at top and vmstat. We have seen slower query performance when
>> performing load tests, so I run the re-index on all application indexes
>> and then issue a full vacuum. I ran the same thing on a staging server
>> and it took less than 12 hours. Is there a possibility the DB pages are
>> corrupted. Is there a command to verify that. (In Oracle, there's a
>> dbverify command that checks for corruption on the data files level).
>>
>> The other question I have. What would be the proper approach to rebuild
>> indexes. I re-indexes and then run vacuum/analyze. Should I not use the
>> re-index approach, and instead, drop the indexes, vacuum the tables, and
>> then create the indexes, then run analyze on tables and indexes??
>
> I *think* if you are planning on dropping the indexes anyway, just drop
> them, VACUUM ANALYZE, and then recreate them, I don't think you have to
> re-analyze after you have recreated them.
I agree - and don't run "VACUUM FULL", it is quite different from "VACUUM".
Also - you should only need to vacuum if you've deleted a lot of data. It's
job is to reclaim space lost to rows marked deleted. So, in fact, you may
not even need to run VACUUM.
"VACUUM FULL" is like a disk defragmentation operation within the DBMS, and
is only necessary if there is a slowdown in performance from lots and lots
of deletes and/or updates and new data isn't finding sequential pages for
storage, which is rare. Given the need for locking, it's generally better
to dump and restore in that case, but again it's a very rare occasion.
I don't know of a command to check for page corruption, but I would think
that if you can run VACUUM (not full) you should be OK.
- Luke
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: llonergan@greenplum.com, john@arbash-meinel.com, htomeh@firstam.com
Subject: Re: "Vacuum Full Analyze" taking so long
In-Reply-To: <BF0AD734.9F38%llonergan@greenplum.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