From: hubert depesz lubaczewski <depesz@gmail.com>
To: PostgreSQL General <pgsql-general@postgresql.org>
To: pgsqlperform <pgsql-performance@postgresql.org>
Subject: database bloat, but vacuums are done, and fsm seems to be setup ok
Date: Wed, 28 Sep 2005 09:07:07 +0200
Message-ID: <9e4684ce05092800075d37d0cc@mail.gmail.com> (raw)
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 = 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 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.
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.
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.
any suggestions? what else can i supply you with to help you help me?
best regards
depesz
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: depesz@gmail.com, pgsql-general@postgresql.org
Subject: Re: database bloat, but vacuums are done, and fsm seems to be setup ok
In-Reply-To: <9e4684ce05092800075d37d0cc@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox