Re: Need input on postgres used for phpBB

i didnt set fsm... the config i paste is all that i put into place...

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?

process ID 32082..

32082 pts/3 S 0:08 postgres: mydbuser mydb 10.10.10.1 SELECT

if i do top..
CPU
32082 postgres 17 0 203M 203M 177M R 84.1 6.7 0:05 3 postmaster
31767 postgres 15 0 168M 168M 162M R 35.6 5.5 0:38 2 postmaster
12623 root 15 0 88 4 0 S 35.0 0.0 79:44 1 rmserver
32040 postgres 15 0 162M 162M 156M S 10.1 5.3 0:07 3 postmaster
32587 postgres 15 0 39624 38M 38256 S 10.1 1.2 0:00 0 postmaster
19837 postgres 15 0 295M 295M 294M R 7.4 9.7 10:28 0 postmaster
15891 postgres 15 0 300M 299M 298M S 5.3 9.9 8:14 2 postmaster
12348 postgres 15 0 295M 294M 294M S 4.7 9.7 9:55 3 postmaster
32589 postgres 15 0 57204 55M 55972 S 4.7 1.8 0:00 1 postmaster
32661 postgres 19 0 21272 20M 19516 S 4.7 0.6 0:00 3 postmaster
21061 postgres 15 0 304M 303M 302M S 4.0 10.0 4:32 0 postmaster
32695 postgres 22 0 14624 14M 13112 S 4.0 0.4 0:00 0 postmaster
23438 postgres 15 0 304M 304M 303M S 3.3 10.0 4:18 0 postmaster
26455 postgres 15 0 307M 307M 306M S 3.3 10.1 0:26 0 postmaster
27564 postgres 15 0 307M 306M 305M S 3.3 10.1 0:18 0 postmaster
20345 postgres 15 0 303M 303M 302M S 2.6 10.0 4:25 3 postmaster
14068 postgres 15 0 306M 306M 304M S 2.6 10.1 1:19 2 postmaster
29438 postgres 15 0 310M 310M 308M S 2.6 10.2 0:06 3 postmaster
32655 postgres 18 0 47604 46M 46416 S 2.6 1.5 0:00 0 postmaster
32683 postgres 21 0 8244 8188 6912 S 2.6 0.2 0:00 2 postmaster
32707 postgres 23 0 7500 7440 6336 S 2.6 0.2 0:00 2 postmaster
19003 postgres 15 0 298M 298M 297M S 2.0 9.8 7:05 0 postmaster
19189 postgres 15 0 303M 302M 301M S 2.0 10.0 4:38 2 postmaster
6616 postgres 15 0 312M 312M 311M S 2.0 10.3 1:09 0 postmaster
30575 postgres 15 0 189M 189M 173M S 2.0 6.2 0:07 2 postmaster
32157 postgres 15 0 174M 174M 169M S 2.0 5.7 0:01 2 postmaster
32201 postgres 15 0 53552 52M 52144 S 2.0 1.7 0:02 1 postmaster

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?
>
> 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend