On 3/30/2010 4:32 AM, Gnanakumar wrote:
> We're using PostgreSQL 8.2.
> I have a question in connection to this question posted by me earlier:
> In our application, DML operations (INSERT/UPDATE/DELETE) are heavily
> performed in a day.
> I also read about pg_autovacuum & REINDEX at:
> I do not want to run pg_autovacuum daemon on a busy hour.
> In case, if I can afford to take my database offline at low-usage time
> and perform REINDEX database-wide manually/linux cron, to boost up index
> performance, what is the community answer/suggestion on the following:
> 1. Is it a good idea to perform this on a daily basis?
> 2. Any implications of doing this on a daily basis?
> 3. Is there a way to find out bloated indexes?
> 4. Any other maintenance command, like ANALYZE, that has to be executed
> before/after REINDEX?
> 5. Is there a way to find out when REINDEX was last run on an
> NOTE: I've also seen from my past experience that REINDEX database-wide
> greatly improves performance of the application.
I could be way off base here, so I hope others will confirm/deny this:
I think the more often you run vacuum, the less you notice it. If you
wait for too long then vacuum will have to work harder and you'll notice
a speed decrease. But many small vacuums which dont have as much work
to do, you wont notice.
It could be, and I'm guessing again, because your database grew from 3
to 30 gig (if I recall the numbers right), REINDEX had lots of affect.
But if vacuum can keep up with space reuse, REINDEX may not be needed.
(maybe a few weeks or once a month).
In response to
pgsql-performance by date
|Next:||From: Robert Haas||Date: 2010-03-30 14:43:27|
|Subject: Re: Why Wal_buffer is 64KB|
|Previous:||From: Andy Colson||Date: 2010-03-30 13:50:14|
|Subject: Re: Database size growing over time and leads to performance