On Monday 09 May 2005 23:28, Scott Marlowe wrote:
> On Mon, 2005-05-09 at 08:55, JM wrote:
> > Hi ALL,
> >
> > we have a site that uses postgres as a backend for a forum. this forum
> > does a lot of deletes, selects and inserts. just recently for some
> > reason postgres eats a lot of processing power..
> >
> > here are some tech-details:
> >
> > tcpip_socket = true
> > max_connections = 260
> > superuser_reserved_connections = 2
> >
> > port = 5432
> > shared_buffers = 40102
> > sort_mem = 4096
> > effective_cache_size = 4000
>
> That's a LOT of shared buffers, and a very small setting for
> effective_cache_size, but I doubt those are causing your problems. On
> most machines you'd be better off if those numbers were reversed. how
> much RAM does your server have, by the way, and what version of
> postgresql and what os / version are you running as well?
>
i have 3G of ram..
but the server is not a dedicated DB server.. server also caters IRC server
and streaming media.
im using RH9
postgres 7.3.4
dual Xeon box
> Also, what are your fsm settings?
>
> > # (initialized by initdb -- may be changed)
> > LC_MESSAGES = 'en_US.UTF-8'
> > LC_MONETARY = 'en_US.UTF-8'
> > LC_NUMERIC = 'en_US.UTF-8'
> > LC_TIME = 'en_US.UTF-8'
> >
> > ** im doing an hourly vaccum
> > 0 1-23 * * * bin/vacuumdb --port 5432 --analyze -d myforumdb
> > 1>/dev/null 2>/tmp/vaccum_hourly.log
> >
> > --> is the hourly vaccum necessary? for some reason vaccum takes to much
> > time..
> >
> > input on how to make things work fast is highly appreciated..
>
> It is quite likely that your updates / deletes have outrun your
> vacuuming and you have table bloat. Try issuing a vacuumdb -faz and see
> if things speed up.
>
> I'd recommend buildind, installing and running the pg_autovacuum daemon
> from now on.