Re: Need input on postgres used for phpBB

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 Thursday 12 May 2005 23:11, Scott Marlowe wrote:
> 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