From: Tory M Blue <tmblue@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Index bloat, reindex weekly, suggestions etc?
Date: Fri, 17 Oct 2008 09:05:26 -0700
Message-ID: <8a547c840810170905m7323dbd8x90de7dd004abc7d4@mail.gmail.com> (raw)
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
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: tmblue@gmail.com
Subject: Re: Index bloat, reindex weekly, suggestions etc?
In-Reply-To: <8a547c840810170905m7323dbd8x90de7dd004abc7d4@mail.gmail.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