DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type;
	b=bajyxqNhaBDUZBFaJMKk7g7Xz4LPzI5Eg9XxRwrOrY1AT0ZjrAbbgnayljAZlGusBPMNyOm8xGZZ0UAOLk3J2CHUTtxiBdGICbKmz7b1VokULk+X46V+eN6rrNJXTM8C98GT4n1AT1QoZXno+aHtn941HhgR8fVSWga7GdY4OVs=
Message-ID: <9e4684ce05092800075d37d0cc@mail.gmail.com>
Date: Wed, 28 Sep 2005 09:07:07 +0200
From: hubert depesz lubaczewski <depesz@gmail.com>
Reply-To: hubert depesz lubaczewski <depesz@gmail.com>
To: PostgreSQL General <pgsql-general@postgresql.org>,
	pgsqlperform <pgsql-performance@postgresql.org>
Subject: database bloat, but vacuums are done, and fsm seems to be setup ok
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_1249_15693059.1127891227369"

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

hi
setup:
postgresql 8.0.3 put on debian on dual xeon, 8GB ram, hardware raid.

database just after recreation from dump takes 15gigabytes.
after some time (up to 3 weeks) it gets really slow and has to be dump'ed
and restored.

as for fsm:
end of vacuum info:
INFO: free space map: 248 relations, 1359140 pages stored; 1361856 total
pages needed
DETAIL: Allocated FSM size: 1000 relations + 10000000 pages =3D 58659 kB
shared memory.

so it looks i have plenty of space in fsm.

vacuums run constantly.
4 different tasks, 3 of them doing:
while true
vacuum table
sleep 15m
done
with different tables (i have chooses the most updated tables in system).

and the fourth vacuum task does the same, but without specifying table - so
it vacuumes whole database.

after last dump/restore cycle i noticed that doing reindex on all indices i=
n
database made it drop in side from 40G to about 20G - so it might be that i
will be using reindex instead of drop/restore.
anyway - i'm not using any special indices - just some (117 to be exact)
indices of btree type. we use simple, multi-column, partial and multi-colum=
n
partial indices. we do not have functional indices.

database has quite huge load of updates, but i thought that vacum will guar=
d
me from database bloat, but from what i observed it means that vacuuming of
b-tree indices is somewhat faulty.

any suggestions? what else can i supply you with to help you help me?

best regards

depesz

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

hi<br>
setup:<br>
postgresql 8.0.3 put on debian on dual xeon, 8GB ram, hardware raid.<br>
<br>
database just after recreation from dump takes 15gigabytes.<br>
after some time (up to 3 weeks) it gets really slow and has to be dump'ed a=
nd restored.<br>
<br>
as for fsm:<br>
end of vacuum info:<br>
INFO:&nbsp; free space map: 248 relations, 1359140 pages stored; 1361856 to=
tal pages needed<br>
DETAIL:&nbsp; Allocated FSM size: 1000 relations + 10000000 pages =3D 58659=
 kB shared memory.<br>
<br>
so it looks i have plenty of space in fsm.<br>
<br>
vacuums run constantly.<br>
4 different tasks, 3 of them doing:<br>
while true<br>
vacuum table<br>
sleep 15m<br>
done<br>
with different tables (i have chooses the most updated tables in system).<b=
r>
<br>
and the fourth vacuum task does the same, but without specifying table - so=
 it vacuumes whole database.<br>
<br>
after last dump/restore cycle i noticed that doing reindex on all
indices in database made it drop in side from 40G to about 20G - so it
might be that i will be using reindex instead of drop/restore.<br>
anyway - i'm not using any special indices - just some (117 to be
exact) indices of btree type. we use simple, multi-column, partial and
multi-column partial indices. we do not have functional indices.<br>
<br>
database has quite huge load of updates, but i thought that vacum will
guard me from database bloat, but from what i observed it means that
vacuuming of b-tree indices is somewhat faulty.<br>
<br>
any suggestions? what else can i supply you with to help you help me?<br>
<br>
best regards<br>
<br>
depesz<br>

------=_Part_1249_15693059.1127891227369--