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/
