SMF Support > Server Performance and Configuration

slow sql query

(1/4) > >>

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 ?

--- End quote ---

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.

Navigation

[0] Message Index

[#] Next page

Go to full version