Re: Need input on postgres used for phpBB

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?

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.