From: | "John Lister" <john(dot)lister-ps(at)kickstone(dot)com> |
---|---|
To: | "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>, <pgsql-admin(at)postgresql(dot)org> |
Cc: | <john(dot)lister-ps(at)kickstone(dot)co(dot)uk> |
Subject: | Re: |
Date: | 2009-12-03 17:41:22 |
Message-ID: | BABC50D08DE64A4786D76411BBA76457@squarepi.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> "John Lister" <john(dot)lister-ps(at)kickstone(dot)com> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2009-12-03 17:51:55 | Re: |
Previous Message | Kevin Grittner | 2009-12-03 17:31:31 | Re: |