X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 311E3D9D3E
	for <pgsql-general-postgresql.org@localhost.postgresql.org>;
	Sat,  1 Oct 2005 07:01:07 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 99483-06
	for <pgsql-general-postgresql.org@localhost.postgresql.org>;
	Sat,  1 Oct 2005 10:01:01 +0000 (GMT)
Received: from qproxy.gmail.com (qproxy.gmail.com [72.14.204.205])
	by svr1.postgresql.org (Postfix) with ESMTP id 24030D9D20
	for <pgsql-general@postgresql.org>;
	Sat,  1 Oct 2005 07:01:01 -0300 (ADT)
Received: by qproxy.gmail.com with SMTP id q10so57900qbq
	for <pgsql-general@postgresql.org>;
	Sat, 01 Oct 2005 03:01:04 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=YWOphVT45xsRrUwlMgAlvZ0+DAEOi55UZshtJFFX6D9zkKSxTIeoqFIVYN005RYW61Y+PbQCa8yQYtMlT2Fgf5erDXWGC9Kg0NhI4YIfieO4JJQfW1rb+C4moMyyW2wXJQOm3rmF0qW9zQ7E0fPxEgCVT7QLLLBvLlF3MNjQYII=
Received: by 10.64.213.15 with SMTP id l15mr1422942qbg;
	Sat, 01 Oct 2005 03:01:04 -0700 (PDT)
Received: by 10.64.195.4 with HTTP; Sat, 1 Oct 2005 03:01:04 -0700 (PDT)
Message-ID: <9e4684ce0510010301ibfafcb2l10e5344b798433b4@mail.gmail.com>
Date: Sat, 1 Oct 2005 12:01:04 +0200
From: hubert depesz lubaczewski <depesz@gmail.com>
Reply-To: hubert depesz lubaczewski <depesz@gmail.com>
To: "Jim C. Nasby" <jnasby@pervasive.com>
Subject: Re: database bloat, but vacuums are done,
 and fsm seems to be setup ok
Cc: PostgreSQL General <pgsql-general@postgresql.org>,
	pgsqlperform <pgsql-performance@postgresql.org>
In-Reply-To: <20050930193459.GC40138@pervasive.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_6602_30473026.1128160864130"
References: <9e4684ce05092800075d37d0cc@mail.gmail.com>
	<20050930193459.GC40138@pervasive.com>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=2.208 required=5 tests=[AWL=-0.152, HTML_20_30=0.504,
	HTML_MESSAGE=0.001, RCVD_BY_IP=0.024,
	RCVD_IN_BL_SPAMCOP_NET=1.832]
X-Spam-Level: **
X-Archive-Number: 200510/1
X-Sequence-Number: 84230

------=_Part_6602_30473026.1128160864130
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On 9/30/05, Jim C. Nasby <jnasby@pervasive.com> wrote:
>
> Looks like it's definately an issue with index bloat. Note that it's
> normal to have some amount of empty space depending on vacuum and update
> frequency, so 15G -> 20G isn't terribly surprising. I would suggest
> using pg_autovacuum instead of the continuous vacuum; it's very possible
> that some of your tables need more frequent vacuuming than they're
> getting now. If you go this route, you might want to change the default
> settings a bit to make pg_autovacuum more agressive.



actually i have a very bad experience with autovaccum - of course it is
because i dont know how to setup it correctly, but for me it's just easier
to setup continuos vacuums. and i know which tables are frequently updated,
so i setup additional vacuums on them.

Also, I'd suggest posting to -hackers about the index bloat. Would you
> be able to make a filesystem copy (ie: tar -cjf database.tar.bz2
> $PGDATA) available? It might also be useful to keep an eye on index size
> in pg_class.relpages and see exactly what indexes are bloating.



i'm watching it right now (which indices are bloating), but i cannot send
copy of pgdata - it contains very sensitive information.
depesz

------=_Part_6602_30473026.1128160864130
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On 9/30/05, <b class=3D"gmail_sendername">Jim C. Nasby</b> &lt;<a href=3D"m=
ailto:jnasby@pervasive.com">jnasby@pervasive.com</a>&gt; wrote:<div><span c=
lass=3D"gmail_quote"></span><blockquote class=3D"gmail_quote" style=3D"bord=
er-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-l=
eft: 1ex;">
Looks like it's definately an issue with index bloat. Note that it's<br>nor=
mal to have some amount of empty space depending on vacuum and update<br>fr=
equency, so 15G -&gt; 20G isn't terribly surprising. I would suggest<br>
using pg_autovacuum instead of the continuous vacuum; it's very possible<br=
>that some of your tables need more frequent vacuuming than they're<br>gett=
ing now. If you go this route, you might want to change the default<br>
settings a bit to make pg_autovacuum more agressive.</blockquote><div><br>
<br>
actually i have a very bad experience with autovaccum - of course it is
because i dont know how to setup it correctly, but for me it's just
easier to setup continuos vacuums. and i know which tables are
frequently updated, so i setup additional vacuums on them.<br>
&nbsp;</div><br><blockquote class=3D"gmail_quote" style=3D"border-left: 1px=
 solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">A=
lso, I'd suggest posting to -hackers about the index bloat. Would you<br>be=
 able to make a filesystem copy (ie: tar -cjf=20
database.tar.bz2<br>$PGDATA) available? It might also be useful to keep an =
eye on index size<br>in pg_class.relpages and see exactly what indexes are =
bloating.</blockquote><div><br>
<br>
i'm watching it right now (which indices are bloating), but i cannot
send copy of pgdata - it contains very sensitive information.<br>
&nbsp;</div></div>depesz<br>

------=_Part_6602_30473026.1128160864130--