"Kevin Grittner" <Kevin.Grittner@xxxxxxxxxxxx> wrote:
"John Lister" <john.lister-ps@xxxxxxxxxxxxx> wrote:
I'm using 8.3.8
That's recent. :-)
Thanks for the reply, wasn't sure if 8.4 had fixed anything :)
If you have index bloat you either have some
process has held open a database transaction for a very long time
while the table underwent updates or deletes, or your vacuum policy
is not aggressive enough.
my indexes seem to grow disproportionately to the size of the
tables, but I haven't studied it in detail yet - I was trying to
increase performance on a number of tables that seem to be
extremely bloated for some reason.
Well, if they're already extremely bloated, you may need to use
CLUSTER or some other technique to recover; but it is important to
understand how you got into that state so you can avoid doing it
again.
By the way, how are you measuring bloat, and how extreme is it?
at the extreme case one table was 30Gb with 25Gb of indexes and after
forcing a full vacuum and reindex dropped to around 7gb each.
the stats claimed that autovacuum had run fairly recently (in fact it was
trying to run as I glomped it) and I can't see any long standing
transactions, but the db
had been up for over a year so it is possible some hung around longer than
they should.
As you say, I suspect I may have had the autovacuum settings too low so as
to avoid loading the db too much (it seems to be a delicate balance between
having autovacuum run and slowing down normal use) and have upped them a
little. but wanted to make sure that (auto)vacuum was doing what I thought
before getting more aggressive with them
Thanks
John
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
