DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=TXyQZBjHg8va/UicywliYRN0RC9Y1sPJa9+ZMzszF6JxjS1CszRVie42s74mrUvSV79Ot2bZjGCYWnG4qTJ8LOpIfOLxdCwxPM6il4n69NT8Xno5+Ovz6o5JOyPHvYGGp1/eS1ff+P203+g5njcOL4KDcBUz/R/nUiXpF/JxEYI=
Message-ID: <1d4e0c10608280317i4eb9d7d0r2b397845ffb8c19d@mail.gmail.com>
Date: Mon, 28 Aug 2006 12:17:08 +0200
From: "Guillaume Smet" <guillaume.smet@gmail.com>
To: "Guillaume Cottenceau" <gc@mnc.ch>
Subject: Re: perf pb solved only after pg_dump and restore
Cc: "Markus Schaber" <schabi@logix-tt.com>, pgsql-performance@postgresql.org
In-Reply-To: <87sljhyz4b.fsf@meuh.mnc.lan>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <8764gd1dkz.fsf@meuh.mnc.lan> <44F2B737.7070301@logix-tt.com>
	<87sljhyz4b.fsf@meuh.mnc.lan>

Guillaume,

On 28 Aug 2006 11:43:16 +0200, Guillaume Cottenceau <gc@mnc.ch> wrote:
>  max_fsm_pages is 20000
>  max_fsm_relations is 1000
> Do they look low?

Yes they are probably too low if you don't run VACUUM on a regular
basis and you have a lot of UPDATE/DELETE activity. FSM doesn't take a
lot of memory so it's usually recommended to have a confortable value
for it.

I usually recommend to read:
http://www.pervasive-postgres.com/instantkb13/article.aspx?id=10116&cNode=5K1C3W
http://www.pervasive-postgres.com/instantkb13/article.aspx?id=10087&cNode=5K1C3W
to understand better what VACUUM and FSM mean.

> Can you elaborate? I have created a couple of indexes (according
> to multiple models of use in our application) and they do take up
> quite some disk space (table dump is 600M but after restore it
> takes up 1.5G on disk) but I thought they could only do good or
> never be used, not impair performance..

Index slow downs write activity (you have to maintain them). It's not
always a good idea to create them.

> > Maybe a VACUUM FULL fullowed by a REINDEX will have solved your problem.
>
> So these would have reordered the data for faster sequential
> access which is not the case of VACUUM ANALYZE?

VACUUM ANALYZE won't help you if your database is completely bloated.
And AFAICS you're not running it on a regular basis so your database
was probably completely bloated which means:
- bloated indexes,
- bloated tables (ie a lot of fragmentation in the pages which means
that you need far more pages to store the same data).

The only ways to solve this situation is either to dump/restore or run
a VACUUM FULL ANALYZE (VERBOSE is better to keep a log), and
eventually reindex any bloated index (depends on your situation).

> > When the free_space_map is to low, VACUUM ANALYZE should have told you
> > via a warning (at least, if your logging is set appropriately).
>
> Unfortunately, we didn't keep the logs of VACUUM ANALYZE, so I
> can't be sure :/

You should really run VACUUM ANALYZE VERBOSE on a regular basis and
analyze the logs to be sure your VACUUM strategy and FSM settings are
OK.

I developed http://pgfouine.projects.postgresql.org/vacuum.html to
help us doing it on our production databases.

Regards,

--
Guillaume