Re: [GENERAL] Need input on postgres used for phpBB

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: [GENERAL] Need input on postgres used for phpBB

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [hidden email] so that your
      message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: [GENERAL] Need input on postgres used for phpBB

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

> 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 [hidden email] so that your
      message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: [GENERAL] Need input on postgres used for phpBB

Tom Lane-2
In reply to this post by Jerome-2
Jerome Macaranas <[hidden email]> 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

---------------------------(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
Reply | Threaded
Open this post in threaded view
|

Re: [GENERAL] Need input on postgres used for phpBB

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

> Jerome Macaranas <[hidden email]> 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..

>
> regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: [GENERAL] Need input on postgres used for phpBB

Jerome-2
In reply to this post by Scott Marlowe
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 [hidden email] so that your
>       message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [hidden email]