Re: perf pb solved only after pg_dump and restore

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Guillaume,

On 28 Aug 2006 11:43:16 +0200, Guillaume Cottenceau <gc@xxxxxx> 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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux