Skip site navigation (1) Skip section navigation (2)

Re: Freebsd vs linux and hardware question

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Aditya <aditya(at)grot(dot)org>
Cc: sfpug(at)postgresql(dot)org
Subject: Re: Freebsd vs linux and hardware question
Date: 2003-09-22 17:51:51
Message-ID: 200309221051.51342.josh@agliodbs.com (view raw or flat)
Thread:
Lists: sfpug
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

In response to

Responses

sfpug by date

Next:From: Dror MatalonDate: 2003-09-22 17:57:59
Subject: Re: Freebsd vs linux and hardware question
Previous:From: AdityaDate: 2003-09-22 17:42:14
Subject: Re: Freebsd vs linux and hardware question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group