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.

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

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 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-general/2004-03/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

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:

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:

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

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?

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.

hi,

I enabled logging for a while just to see what statement is taking too much
time.. listed below are some parts of the log.. im wondering why this sql
takes to much time.. they have indexes in place.. or it might be my config?

tcpip_socket = true
max_connections = 260
superuser_reserved_connections = 2

port = 5432
#shared_buffers = 1000
shared_buffers = 40102
sort_mem = 4096
effective_cache_size = 4000

max_fsm_pages = 20000
max_fsm_relations = 1000
#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'

LOG: query: SELECT * FROM phpbb_smilies
LOG: duration: 0.005410 sec
LOG: duration: 390.731807 sec
-- this table has only 295 rows

....
LOG: query: SELECT *
FROM phpbb_config
LOG: duration: 50.752599 sec
.......
LOG: query: UPDATE phpbb_sessions
SET session_user_id = -1, session_start = 1116932825,
session_time = 1116932825, session_page = 1, session_l
ogged_in = 0
WHERE session_id = '8a7fe41e58077d2f8cececdc23ab9f80'
AND session_ip = 'd2d58db2'
LOG: duration: 86.218839 sec

On Tue, 2005-05-24 at 07:19, Jerome Macaranas wrote:

hi,

I enabled logging for a while just to see what statement is taking too much
time.. listed below are some parts of the log.. im wondering why this sql
takes to much time.. they have indexes in place.. or it might be my config?

SNIP

LOG: query: SELECT * FROM phpbb_smilies
LOG: duration: 0.005410 sec
LOG: duration: 390.731807 sec
-- this table has only 295 rows

That you can see. I still think there's got to be a bunch of dead
tuples for it to be this slow, or there's something very wrong with your
I/O subsystem.

....
LOG: query: SELECT *
FROM phpbb_config
LOG: duration: 50.752599 sec
.......

Indexes won't help here, as they're asking for the shole table in each
time. What does 'explain analyze select * From phpbb_config' from the
psql command prompt say? What does vacuum verbose phpbb_config say?
Are you getting any weird messages in the system logs when this is
happening, like maybe disk timeouts?

How long does it take to copy a hundred or so megabytes on this same
drive.

Jerome Macaranas <jerome@gmanmi.tv> writes:

LOG: query: SELECT * FROM phpbb_smilies
LOG: duration: 0.005410 sec
LOG: duration: 390.731807 sec
-- this table has only 295 rows

I think this is an artifact of your lack-of-vacuuming problems;
the table has become hugely bloated and it takes a long time to
scan through it to find the few live rows. You'll need to do
a VACUUM FULL (or possibly CLUSTER) to shrink the table back
to a reasonable size.

regards, tom lane

On Tuesday 24 May 2005 22:33, Tom Lane wrote:

Jerome Macaranas <jerome@gmanmi.tv> writes:

LOG: query: SELECT * FROM phpbb_smilies
LOG: duration: 0.005410 sec
LOG: duration: 390.731807 sec
-- this table has only 295 rows

I think this is an artifact of your lack-of-vacuuming problems;
the table has become hugely bloated and it takes a long time to
scan through it to find the few live rows. You'll need to do
a VACUUM FULL (or possibly CLUSTER) to shrink the table back
to a reasonable size.

im doing a vacuum analyze every hour and doing a full vacuum every midnight..

On Tuesday 24 May 2005 21:38, Scott Marlowe wrote:

On Tue, 2005-05-24 at 07:19, Jerome Macaranas wrote:

hi,

I enabled logging for a while just to see what statement is taking too
much time.. listed below are some parts of the log.. im wondering why
this sql takes to much time.. they have indexes in place.. or it might be
my config?

SNIP

LOG: query: SELECT * FROM phpbb_smilies
LOG: duration: 0.005410 sec
LOG: duration: 390.731807 sec
-- this table has only 295 rows

That you can see. I still think there's got to be a bunch of dead
tuples for it to be this slow, or there's something very wrong with your
I/O subsystem.

vacuum anaylze output:

INFO: Analyzing public.phpbb_sessions
INFO: --Relation public.phpbb_smilies--
INFO: Pages 3: Changed 0, reaped 1, Empty 0, New 0; Tup 295: Vac 0, Keep/VTL
0/0, UnUsed 2, MinLen 58, MaxLen 97; Re-using:
Free/Avail. Space 1384/1340; EndEmpty/Avail. Pages 0/1.
CPU 0.00s/0.00u sec elapsed 0.26 sec.
INFO: Index phpbb_smilies_pkey: Pages 2; Tuples 295: Deleted 0.
CPU 0.00s/0.00u sec elapsed 0.20 sec.
INFO: Rel phpbb_smilies: Pages: 3 --> 3; Tuple(s) moved: 0.
CPU 0.00s/0.00u sec elapsed 3.31 sec.
INFO: Analyzing public.phpbb_smilies
INFO: --Relation pg_catalog.pg_statistic--
INFO: Pages 72: Changed 58, reaped 71, Empty 0, New 0; Tup 874: Vac 571,
Keep/VTL 156/156, UnUsed 2554, MinLen 72, MaxLen 1
952; Re-using: Free/Avail. Space 365232/365168; EndEmpty/Avail. Pages 0/71.
CPU 0.00s/0.00u sec elapsed 0.33 sec.
INFO: Index pg_statistic_relid_att_index: Pages 32; Tuples 874: Deleted 571.
CPU 0.00s/0.01u sec elapsed 4.09 sec.
INFO: Rel pg_statistic: Pages: 72 --> 27; Tuple(s) moved: 453.
CPU 0.00s/0.02u sec elapsed 0.95 sec.
INFO: Index pg_statistic_relid_att_index: Pages 32; Tuples 874: Deleted 452.
CPU 0.00s/0.00u sec elapsed 0.00 sec.