From: | Jerome Macaranas <jerome(at)gmanmi(dot)tv> |
---|---|
To: | Scott Marlowe <smarlowe(at)g2switchworks(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Need input on postgres used for phpBB |
Date: | 2005-05-12 05:33:55 |
Message-ID: | 200505121333.55157.jerome@gmanmi.tv |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Andrei Gaspar | 2005-05-12 05:37:04 | Collation problem |
Previous Message | Tom Lane | 2005-05-12 04:36:38 | Re: Need to determine how badly tables need vacuuming |