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

#fsync = true
#wal_sync_method = fsync

#log_statement = true
#log_duration = true

#syslog = 0 # range 0-2
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'

#
# Locale 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..


tia,

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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

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.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

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@postgresql.org

On Tue, 10 May 2005 12:19:43 +0800 Jerome Macaranas <jerome@gmanmi.tv> thought long, then sat down and wrote:


> im using RH9
>
> postgres 7.3.4


Maybe you ran into the same problem I had about a year ago. See http://archives.postgresql.org/pgsql...3/msg00810.php and thread. My database became extremely slow, vacuum itself needed several hours instead of several minutes, after doing some weeks of heavy inserting.

Vaccum obviously does not release all unused memory in 7.3.x.
--
Frank Finner

Invenius - Lösungen mit Linux
Köpfchenstraße 36
57072 Siegen
Telefon: 0271 231 8606 Mail: frank.finner@invenius.de
Telefax: 0271 231 8608 Web: http://www.invenius.de
Key fingerprint = 90DF FF40 582E 6D6B BADF 6E6A A74E 67E4 E788 2651

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (GNU/Linux)

iD8DBQFCgFXa9yTJ83o5N2cRAkCMAJ9pIDCSdYPJIaSBTyInPE B4PNECewCfV0oq
IUKEA/rhY2lEV6rTWkP5CQY=
=PZeH
-----END PGP SIGNATURE-----

my data directory is just 1.7G



On Tuesday 10 May 2005 14:33, Frank Finner wrote:
> On Tue, 10 May 2005 12:19:43 +0800 Jerome Macaranas <jerome@gmanmi.tv>
thought long, then sat down and wrote:
> > im using RH9
> >
> > postgres 7.3.4
>
> Maybe you ran into the same problem I had about a year ago. See
> http://archives.postgresql.org/pgsql...3/msg00810.php and
> thread. My database became extremely slow, vacuum itself needed several
> hours instead of several minutes, after doing some weeks of heavy
> inserting.
>
> Vaccum obviously does not release all unused memory in 7.3.x.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Hi,

what fix did you do? go for 7.4?

tia,

On Tuesday 10 May 2005 14:33, Frank Finner wrote:
> On Tue, 10 May 2005 12:19:43 +0800 Jerome Macaranas <jerome@gmanmi.tv>
thought long, then sat down and wrote:
> > im using RH9
> >
> > postgres 7.3.4
>
> Maybe you ran into the same problem I had about a year ago. See
> http://archives.postgresql.org/pgsql...3/msg00810.php and
> thread. My database became extremely slow, vacuum itself needed several
> hours instead of several minutes, after doing some weeks of heavy
> inserting.
>
> Vaccum obviously does not release all unused memory in 7.3.x.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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
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.

>
> 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

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On Thu, 2005-05-12 at 00:33, Jerome Macaranas wrote:
> 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..

You might want to run one of those vacuums, like the one at 1700 by hand
and do a vacuum verbose to see how many tuples are being reclaimed and
how many, if any, are getting left behind etc...

> > 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?

Everything but some system indexes and tables, i believe.


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org