Re: Need input on postgres used for phpBB

On Mon, 2005-05-09 at 23:35, Jerome Macaranas wrote:
> i didnt set fsm... the config i paste is all that i put into place...

OK, that's likely a part of your problem.

Did you run the vacuumdb -af I recommended? Did it help? If so, you
likely need to run plain (i.e. lazy) vacuums more often, and crank up
your fsm settings. Just uncomment them and add a zero behind them for
now. you might have to increase your shared memory settings to handle
them, but fsm doesn't use a lot of shared memory.

There are some issues with 7.3 that were fixed with 7.4, but I don't
think you're hitting any of them. That said, I'd highly recommend at
least an upgrade to the latest 7.4, if not 8.0 series.

Note you may also need to reindex as well.

>
> is there a way to look at the query that's eating too much process
> without starting the DB and redirect stdout out to a file?

Right now, that's more a symptom than a problem. i.e. when we
(hopefully) get rid of the bloat in your tables / indexes this problem
will go away.

> > port = 5432
> > shared_buffers = 40102
> > sort_mem = 4096
> > effective_cache_size = 4000

IF you have 3G of ram, then your effective_cache_size is definitely too
small for your machine. Even if it's doing other things, at least a gig
or so is likely being used by the machine to cache postgresql data. So
your effective_cache_size should be about 1G/8k.

You can also increase sort_mem a bit without too much worry. 16 meg or
so is not unreasonable for a machine with 3 Gigs of ram, unless you're
expecting all 260 possible connections to start doing selects with
sorts.

So, I'd recommend:

vacuum full all dbs
Increase FSM settings (and shm settings as necessary)
increase sort_mem (work_mem if you go to 8.0)
use the contrib/dbsize package to look for bloated tables and / or
indexes.
upgrade pg versions if possible