Re: Need input on postgres used for phpBB

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.

> > ....
> > 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.
>
> > 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly