News:

SMF 2.1.4 has been released! Take it for a spin! Read more.

Main Menu

slow sql query

Started by blinddruid, February 13, 2012, 08:36:32 AM

Previous topic - Next topic

blinddruid

My hosting company was complaining about slow sql queries from my database.  I switched the database engine from MyISAM  to Innodb which has made a huge difference except for one specific query which is:

# Time: 120212 22:55:34
# User@Host: *******@ localhost []
# Thread_id: 1839721 Schema: ******
# Query_time: 4.280776 Lock_time: 0.000051 Rows_sent: 1 Rows_examined: 11724 Rows_affected: 0 Rows_read: 17
use ******;
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 = 78
AND m.approved = 1;

# Time: 120212 23:22:06
# User@Host: ***********@ localhost []
# Thread_id: 1851972 Schema: *********
# Query_time: 17.417454 Lock_time: 0.000113 Rows_sent: 1 Rows_examined: 11724 Rows_affected: 0 Rows_read: 17
use *********;
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 = 78
AND m.approved = 1;
# Time: 120212 23:34:33


Any ideas as to why this one query is causing issues?

Thanks.


butch2k

Could you post the query plan ? (using explain)

Could you check the existence of the showPosts index on smf_messages ?

blinddruid

Quote from: butch2k on February 13, 2012, 09:26:08 AM
Could you post the query plan ? (using explain)

Could you check the existence of the showPosts index on smf_messages ?

Thanks for the response.  Having somewhat limited experience with mysql could you please explain your first request? 

The showPosts index does exist.

Thanks.




butch2k

run the query through phpmyadmin or the command line with the keyword "explain" in front of it like:
explain select count(*) ....

As a result, you should obtain a table listing the various indexes and options used by the query.

Having the result of status.php would help as well.

blinddruid

Status.php

Basic Information
February 13, 2012, 08:57:50 AM
Operating System:    CentOS release 5.7 (Final)
Processor:    Intel® Xeon® CPU E5520 @ 2.27GHz (2261.076MHz)
Load averages:    2.34, 5.52, 5.94
Current processes:    5 (4 sleeping, 1 running, 0 zombie)
Processes by CPU:    (other) (4) 0.0%
Memory usage:    73.337% (9019716k / 12298916k)
Swap: 6.039% (379800k / 6289408k)

MySQL processes
Total processes:    2 (1 sleeping, 1 running, 0 locked)

MySQL Statistics
MySQL 5.0.x
Connections per second:    7.9732
Kilobytes received per second:    77.722
Kilobytes sent per second:    1436.5823
Queries per second:    369.1764
Percentage of slow queries:    0.0001
Opened vs. Open tables:
(table_cache)    382.4338 (should be <= 80)
Table cache usage:
(table_cache)    1 (should be >= 0.5 and <= 0.9)
Key buffer read hit rate:
(key_buffer_size)    0.0044 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size)    0.0893 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size)    820.5439 (should be >= 30 )
Thread cache usage:
(thread_cache_size)    0.875 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:
(tmp_table_size)    0.3807 (should be <= 0.5)
Sort merge pass rate:
(sort_buffer)    0 (should be <= 0.001)
Query cache enabled:
(query_cache_type)    1 (should be >= 1 and <= 1)
Query cache miss rate:
(query_cache_limit)    0.2439 (should be <= 0.5)
Query cache prune rate:
(query_cache_size)    0.4597 (should be <= 0.05)

I could show the rest but it would make for a very long post.

Thanks.

butch2k

Except for a full query cache, there is not too many issues with the SQL server, the load average is correct as well.

So an explained query would be interesting.

Illori

name of your host could help as well. also what type of server do you have shared/vps/dedicated?

blinddruid

here is the explain query
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,approved,id_member_msg,related_ip     showPosts    5          const,sensnetw_forum.b.id_board    27    Using where


Using site5 as the host with  reseller account on a shared host.

Thanks again.
 

Illori

your host is an overseller, if you look at their shared packages. i am not sure how much help you would get by looking into this issue other then to move to a new host that is not an overseller.

butch2k

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.

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.


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.

blinddruid

Quote from: butch2k on February 15, 2012, 02:51:31 AM
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.

I will give that a try.

Thank for all the help.  It was much appreciated.


Aleksi "Lex" Kilpinen

I'm afraid there's probably very little you can do. The messages table only keeps getting bigger, and if your host is already complaining - you might be best off starting to seek a better host.
Slava
Ukraini!


"Before you allow people access to your forum, especially in an administrative position, you must be aware that that person can seriously damage your forum. Therefore, you should only allow people that you trust, implicitly, to have such access." -Douglas

How you can help SMF

Advertisement: