Re: Need input on postgres used for phpBB - Mailing list pgsql-general

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@postgresql.org so that your
>       message can get through to the mailing list cleanly