From: Guillaume Cottenceau <gc@mnc.ch>
To: Markus Schaber <schabi@logix-tt.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: perf pb solved only after pg_dump and restore
Date: 28 Aug 2006 11:43:16 +0200
Message-ID: <87sljhyz4b.fsf@meuh.mnc.lan> (raw)
In-Reply-To: <44F2B737.7070301@logix-tt.com>
References: <8764gd1dkz.fsf@meuh.mnc.lan>
<44F2B737.7070301@logix-tt.com>
Hi Markus,
Thanks for your message.
> Guillaume Cottenceau wrote:
>
> > We noticed a slowdown on our application while traffic was kinda
> > heavy. The logics after reading the docs commanded us to trim the
> > enlarged tables, run VACUUM ANALYZE and then expect fast
> > performance again; but it wasn't the case[1].
>
> What exactly do you mean with "trim the enlarged tables"?
We have a couple of logs files which get larger over time
(millions of rows). As they are log files, they can be trimmed
from older values.
> > Out of the blue, we dumped the database, removed it, recreated
> > from the restore, and now the performance is lightning fast
> > again.
> >
> > Does it look familiar to anyone? I thought running VACUUM ANALYZE
> > after a trim should be enough so that pg has assembled the data
> > and has good statistical knowledge of the tables contents..
>
> This looks like either your free_space_map setting is way to low, or you
I don't know much about free_space_map. Trying to search in
documentation, I found run time configuration of the two
following parameters for which the current values follow:
max_fsm_pages is 20000
max_fsm_relations is 1000
Do they look low?
Notice: table data is only 600M after trim (without indexes),
while it was probably 3x to 10x this size before the trim.
Machine is a 2G Dell 1850 with lsi logic megaraid.
> have index bloat.
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..
> 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?
> It also might make sense to issue a CLUSTER instead (which combines the
> effects of VACUUM FULL, REINDEX and physically reordering the data).
I was reluctant in using CLUSTER because you have to choose an
index and there are multiple indexes on the large tables..
> 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 :/
--
Guillaume Cottenceau
Create your personal SMS or WAP Service - visit http://mobilefriends.ch/
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: gc@mnc.ch, schabi@logix-tt.com
Subject: Re: perf pb solved only after pg_dump and restore
In-Reply-To: <87sljhyz4b.fsf@meuh.mnc.lan>
* 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