Re: Freebsd vs linux and hardware question

Aditya,

> these are the defaults in the config that we're using:
>
> #max_fsm_relations = 100 # min 10, fsm is free space map
> #max_fsm_pages = 10000 # min 1000, fsm is free space map

No wonder you're having performance issues. Those are much too low.

1. Raise fsm_relations to 500-1000 ... it should be greater than the total
number of tables in all databases, with a safety margin.

2. Replace your 5-minute VACUUM ANALYZE with VACUUM VERBOSE, and log the
output to a file. Add up the total for pages reclaimed from tables (not
indexes in 7.2), and average them over the VACUUM periods. Set fsm_pages to
this number + 10-20%, or +50% if you have lots of RAM.

> FWIW, there are several thousand rows that get updated every few minutes.
>
> I'll let Dror answer as to why VACUUM ANALYZE vs. VACUUM alone.

The question is whether the distribution of data changes significantly.

You guys would really benefit by joining, and reading, the PGSQL-PERFORMANCE
list.

> > Finally, what about REINDEX?
>
> Because we have to remove all those addl. deleted/updated rows...

Well, yes. I was wondering about frequency .....

--
Josh Berkus
Aglio Database Solutions
San Francisco