SMF Support > Server Performance and Configuration

slow sql query

<< < (3/4) > >>

blinddruid:

--- Quote from: butch2k on February 13, 2012, 10:54:25 AM ---Ok the query is executing as it should, so the time is probably spent loading data into the innodb buffer pool. If your host is an overseller, the innodb buffer pool might be too small, as since smf_messages is rather large it's probably swapped out fairly often...

As a quick fix until you move to another host you should revert smf_messages back to myIsam. Moreover you should see an improvement in queries using count(*) on smf_messages. Indeed a full table scan is not always required to obtain those statistics using myIsam contrary to Innodb.


--- End quote ---

Thanks.  I will try reverting the smf_messages back to myIsam.  Will report back with the results.

blinddruid:
Unfortuantely changing the smf_messages back to myIsam  did not work.  The following were found in their slow querylog:
# Time: 120214 4:18:10
# User@Host: ******] @ localhost []
# Thread_id: 2777837 Schema: sensnetw_forum
# Query_time: 19.745062 Lock_time: 0.000064 Rows_sent: 1 Rows_examined: 17663 Rows_affected: 0 Rows_read: 7
use sensnetw_forum;
SELECT COUNT(*)
FROM smf_messages AS m
INNER JOIN smf_boards AS b ON (b.id_board = m.id_board AND (FIND_IN_SET(-1, b.member_groups) != 0))
WHERE m.id_member = 260
AND m.approved = 1;

# Time: 120214 4:39:14
# User@Host: ******] @ localhost []
# Thread_id: 2786821 Schema: sensnetw_forum
# Query_time: 10.206091 Lock_time: 0.007702 Rows_sent: 1 Rows_examined: 12374 Rows_affected: 0 Rows_read: 5
SELECT COUNT(*)
FROM smf_messages AS m
INNER JOIN smf_boards AS b ON (b.id_board = m.id_board AND (FIND_IN_SET(-1, b.member_groups) != 0))
WHERE m.id_member = 985
AND m.approved = 1;

# Time: 120214 5:59:06
# User@Host: ********] @ localhost []
# Thread_id: 2823083 Schema: sensnetw_forum
# Query_time: 5.939690 Lock_time: 0.000049 Rows_sent: 25 Rows_examined: 17446 Rows_affected: 0 Rows_read: 175
use sensnetw_forum;
SELECT t.id_topic
FROM smf_topics AS t
INNER JOIN smf_messages AS mf ON (mf.id_msg = t.id_first_msg)
WHERE t.id_board = 3
AND (t.approved = 1)
ORDER BY is_sticky DESC, mf.subject
LIMIT 150, 25;

They state that they need the queries to be under 3 seconds.  Are there any other suggestions? 


Thank you.

butch2k:
Could you run an "explain extended" on the queries above ?

Unfortunately Illori sounds right about you host being an overseller, and i fear there is not much to be done except changing your host. :(

blinddruid:
Sure...

id select_type table type possible_keys                                                                        key         key_len   ref                                           rows      Extra
1 SIMPLE         b      ALL PRIMARY    NULL   NULL   NULL   62    Using where
1 SIMPLE          m      ref  ID_BOARD,ID_MEMBER,participation,showPosts,approve...    showPosts        5    const,sensnetw_forum.b.id_board     68               Using where


id select_type table type possible_keys                                                                        key    key_len    ref                                           rows         Extra
1 SIMPLE            b    ALL   PRIMARY                                                                         NULL     NULL   NULL                                           62        Using where
1 SIMPLE            m    ref    ID_BOARD,ID_MEMBER,participation,showPosts,approve...       showPosts 5    const,sensnetw_forum.b.id_board     68        Using where

id select_type table type    possible_keys                                                                   key    key_len      ref      rows    Extra
1  SIMPLE            t    ref      ID_BOARD,approved,last_message_sticky,board_news     board_news  2     const    4620    Using where; Using temporary; Using filesort
1  SIMPLE          mf   eq_ref  PRIMARY                                                                  PRIMARY        4    sensnetw_forum.t.id_first_msg    1

butch2k:
try running analyze on the smf tables thru phpmyadmin, if you converted your tables w/o analyzing the index distribution afterwards this might cause some performance degradation.

besides this i do not see much more to be done.

Navigation

[0] Message Index

[#] Next page

[*] Previous page