Index bloat, reindex weekly, suggestions etc?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux