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
