Re: Need input on postgres used for phpBB - Mailing list pgsql-general

On Tuesday 10 May 2005 22:00, Scott Marlowe wrote:
> 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

i have a routine of vacuumdb -af every midnight and vacuumdb -a every 8:00 , 12:00, 17:00

what im seeing is:


postgres 25542 32.3 10.5 337680 327816 ?     R    12:17   1:09 postgres: myuser mydb myip DELETE
postgres 25578 34.5 10.5 337684 327880 ?     R    12:17   1:13 postgres: myuser mydb myip DELETE

delete takes too long to finish..

> 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.
>
ill be doing this.. can i reindex all tables in my DB without starting my db on standalone mode?

> > 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq