(unknown)

This is a multi-part message in MIME format.

------=_NextPart_000_0015_01CA7408.4D6140A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

"Kevin Grittner" Kevin.Grittner [at] wicourts.gov wrote
>"John Lister" <john.lister-ps [at] kickstone.com> wrote:
> When you do a vacuum it marks the deleted rows as being usable
> again and I can see that it reports that "xxx index row versions
> were removed", however are these rows marked for reuse in an index
> in the same manner as they are in the table? I note that the docs
> say that vacuum full doesn't shrink indexes and that a reindex is
> recommended periodically, is this still true if the table is
> frequently vacuumed?

>VACUUM makes space in indexes available for re-use. I don't think
>that reindex is normally needed for recent releases, although I seem
>to remember hearing that it was needed in older versions. What
>version are you running?

Thanks for your reply. I'm using 8.3.8 (ubuntu).

I thought it would do, but couldn't see anything to confirm that and =
some of 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.

Thanks

--

Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/

------=_NextPart_000_0015_01CA7408.4D6140A0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" =
http-equiv=3DContent-Type>
<META name=3DGENERATOR content=3D"MSHTML 8.00.6001.18852">
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT size=3D2 face=3DArial>"Kevin Grittner" <A
href=3D"mailto:Kevin.Grittner [at] wicourts.gov">Kevin.Grittner [at] w icourts.gov</=
A> wrote</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial>>"John Lister" <<A
href=3D"mailto:john.lister-ps [at] kickstone.com">john.lister-ps [at] kickstone.com=
</A>>
wrote: <BR>> When you do a vacuum it marks the deleted rows as =
being
usable<BR>> again and I can see that it reports that "xxx index row
versions<BR>> were removed", however are these rows marked for reuse =
in an
index<BR>> in the same manner as they are in the table? I note that =
the
docs<BR>> say that vacuum full doesn't shrink indexes and that a =
reindex
is<BR>> recommended periodically, is this still true if the table =
is<BR>>
frequently vacuumed?<BR> <BR>>VACUUM makes space in indexes =
available
for re-use. I don't think<BR>>that reindex is normally needed =
for
recent releases, although I seem<BR>>to remember hearing that it was =
needed
in older versions. What<BR>>version are you =
running?<BR></FONT></DIV>
<DIV><FONT size=3D2 face=3DArial>Thanks for your reply. </FONT><FONT =
size=3D2
face=3DArial>I'm using 8.3.8 (ubuntu). </FONT></DIV>
<DIV><FONT size=3D2 face=3DArial></FONT> </DIV>
<DIV><FONT size=3D2 face=3DArial>I thought it would do, but couldn't see =
anything to
confirm that and some of 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.</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial></FONT> </DIV>
<DIV><FONT size=3D2 face=3DArial>Thanks</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial></FONT> </DIV>
<DIV><FONT size=3D2 face=3DArial>--</FONT></DIV>
<DIV> </DIV>
<DIV><FONT size=3D2 face=3DArial>Got needs? Get Goblin'! - <A
href=3D"http://www.pricegoblin.co.uk/">http://www.pricegobli n.co.uk/</A><=
BR></FONT></DIV></BODY></HTML>

------=_NextPart_000_0015_01CA7408.4D6140A0--

"John Lister" <john.lister-ps [at] kickstone.com> wrote:

> I'm using 8.3.8

That's recent. :-) 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?

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

"Kevin Grittner" <Kevin.Grittner [at] wicourts.gov> wrote:
> "John Lister" <john.lister-ps [at] kickstone.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

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

"John Lister" <john.lister-ps [at] kickstone.com> wrote:

> 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

When we first started using PostgreSQL we initially had problems
with bloat and the autovacuum process started creating significant
load. Our first reaction was to make autovacuum less aggressive,
but this just made things worse. The counter-intuitive step of
making autovacuum very aggressive actually prevented bloat to the
point where autovacuum wound up creating less load. Don't be shy
about running it frequently and at low thresholds; but if it does
have a performance impact, a small autovacuum_cost_delay (I've never
needed more than 10ms) will probably solve the issue.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

"Kevin Grittner" <Kevin.Grittner [at] wicourts.gov> wrote:
> When we first started using PostgreSQL we initially had problems
> with bloat and the autovacuum process started creating significant
> load. Our first reaction was to make autovacuum less aggressive,
> but this just made things worse. The counter-intuitive step of
> making autovacuum very aggressive actually prevented bloat to the
> point where autovacuum wound up creating less load. Don't be shy
> about running it frequently and at low thresholds; but if it does
> have a performance impact, a small autovacuum_cost_delay (I've never
> needed more than 10ms) will probably solve the issue.

Cheers, I might give that a go. I've set the cost_delay to 10 already, so
will decrease the thresholds more and see what happens.

John

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

(unknown)

Re:

Re:

Re:

Re: