Re: Freebsd vs linux and hardware question

On Mon, Sep 22, 2003 at 10:51:51AM -0700, Josh Berkus wrote:
> 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.

We'll look at all of this.

I forgot to mention that we're running VACUUM ANALYZE every five minutes
on only one table which is the one that gets updated. We're running
VACUUM on the whole db once a night.

Also, we're running two databases that have almost identical data and
similar configs. One is running on the Netapp, and the other from local
disk. The one that runs from local disk, is plenty fast, even before we
did all the tunning. The one on the netapp is the one that is giving us
problem with speed. Running the VACUUM FULL made a big difference.

>
> > 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.

I used to be on quite a few of the lists, and ended up dropping out
because of the signal to noise ratio.

Now I mostly look at the archives or do searchs.

>
> > > 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

--
Dror Matalon, President
Zapatec Inc
1700 MLK Way
Berkeley, CA 94709
http://www.zapatec.com