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 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
