Index bloat, reindex weekly, suggestions etc?

From: "Tory M Blue" <tmblue(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Index bloat, reindex weekly, suggestions etc?
Date: 2008-10-17 16:05:26
Message-ID: 8a547c840810170905m7323dbd8x90de7dd004abc7d4@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Good day,

So I've been running 8.3 for a few months now and things seem good.

I also note there are some bug fixes and you are up to 8.3.4 now, but
reading it I don't see that I'm being affected by anything, but please
tell me if i should plan upgrades to 8.3.4..

The real issue is my index growth and my requirement for weekly
re-indexing (which blocks and therefore is more or less a manual
process in a live production environment (fail over, changing vips
etc).

After Reindex

My data
3.1G /data/cls

My Indexes
1.5G /data/clsindex

After 2 weeks

My data
3.4G /data/cls

My indexes
6.8G /data/clsindex

This is 2 weeks. and throughout those 2 weeks I can see performance
degrading. In fact I just bumped the memory footprint of my server to
32gigs from 8gigs as my entire PostgreSQL DB is now 15gb, this has
helped tremendously, everything ends up in memory and there is almost
zero read access from the DB (that's great), however as my DB grows I
can't expect to manage it by adding Terabytes of memory to the system,
so something is not being done efficiently.

We are testing with fill factor, but that just seems to allocate space
and doesn't really decrease my index size on disk. It does however
seem to help performance a bit, but not a ton.

Where should I start looking, what type of information should I
provide in order for me to help you, help me?

This is a transactional DB with over 800K updates happening each day.
tons of updates and deletes. autovac is on and I do a analyze each
night after the dump

Thanks
Tory

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Lincoln Yeoh 2008-10-17 16:12:02 Re: Drupal and PostgreSQL - performance issues?
Previous Message Stephen Frost 2008-10-17 15:29:28 Re: Annoying Reply-To