posted 19 years ago
Ok, I have investigated a lot more as to what is going on with the forums slow response and performance issues.
There is a serious problem with the search function as it times out connections big time. Here is a glimpse of the slow queries...
UPDATE jforum_topics_watch SET is_read = 1 WHERE topic_id = 2577 AND user_id = 8181;
# Time: 050718 19:45:49
# User@Host: db[db] @ localhost [127.0.0.1]
# Query_time: 25 Lock_time: 0 Rows_sent: 1673 Rows_examined: 3076476
SELECT post_id FROM jforum_search_wordmatch wm, jforum_search_words w WHERE wm.word_id = w.word_id AND LOWER(w.word) = LOWER('after');
# Time: 050718 19:45:58
# User@Host: db[db] @ localhost [127.0.0.1]
# Query_time: 3 Lock_time: 0 Rows_sent: 43 Rows_examined: 10310
SELECT COUNT(pm.privmsgs_to_userid) AS private_messages, u.* FROM jforum_users u LEFT JOIN jforum_privmsgs pm ON pm.privmsgs_type = 1 AND pm.privmsgs_to_userid = u.user_id WHERE u.user_id = 7421 GROUP BY pm.privmsgs_to_userid;
# Time: 050718 19:46:03
# User@Host: db[db] @ localhost [127.0.0.1]
# Query_time: 25 Lock_time: 0 Rows_sent: 0 Rows_examined: 3076542
SELECT post_id FROM jforum_search_wordmatch wm, jforum_search_words w WHERE wm.word_id = w.word_id AND LOWER(w.word) = LOWER('gurl/guy');
# Time: 050718 19:46:04
# User@Host: db[db] @ localhost [127.0.0.1]
# Query_time: 23 Lock_time: 0 Rows_sent: 1673 Rows_examined: 3076476
SELECT post_id FROM jforum_search_wordmatch wm, jforum_search_words w WHERE wm.word_id = w.word_id AND LOWER(w.word) = LOWER('after');
# Time: 050718 19:46:12
# User@Host: db[db] @ localhost [127.0.0.1]
# Query_time: 23 Lock_time: 0 Rows_sent: 0 Rows_examined: 3076476
SELECT post_id FROM jforum_search_wordmatch wm, jforum_search_words w WHERE wm.word_id = w.word_id AND LOWER(w.word) = LOWER('a');
# Time: 050718 19:46:15
# User@Host: db[db] @ localhost [127.0.0.1]
# Query_time: 3 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
SELECT post_id FROM jforum_search_wordmatch wm, jforum_search_words w WHERE wm.word_id = w.word_id AND LOWER(w.word) = LOWER('after');
# Time: 050718 19:46:28
# User@Host: db[db] @ localhost [127.0.0.1]
# Query_time: 23 Lock_time: 0 Rows_sent: 0 Rows_examined: 3076476
SELECT post_id FROM jforum_search_wordmatch wm, jforum_search_words w WHERE wm.word_id = w.word_id AND LOWER(w.word) = LOWER('a');
# Time: 050718 19:46:31
# User@Host: db[db] @ localhost [127.0.0.1]
# Query_time: 31 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
UPDATE jforum_topics SET topic_views = topic_views + 1 WHERE topic_id = 2798;
# User@Host: db[db] @ localhost [127.0.0.1]
# Query_time: 33 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
UPDATE jforum_topics_watch SET is_read = 1 WHERE topic_id = 2802 AND user_id = 6788;
# Time: 050718 19:46:36
# User@Host: db[db] @ localhost [127.0.0.1]
# Query_time: 31 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
UPDATE jforum_topics_watch SET is_read = 1 WHERE topic_id = 2577 AND user_id = 8181;
# Time: 050718 19:46:37
# User@Host: db[db] @ localhost [127.0.0.1]
# Query_time: 4 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
SELECT post_id FROM jforum_search_wordmatch wm, jforum_search_words w WHERE wm.word_id = w.word_id AND LOWER(w.word) = LOWER('one');
# Time: 050718 19:46:42
# User@Host: db[db] @ localhost [127.0.0.1]
# Query_time: 13 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
INSERT INTO jforum_sessions ( session_id, session_user_id, session_start ) VALUES ('B65F302751D859E584206780B17128B1', 11094, '2005-07-18 19:25:19');
# Time: 050718 19:46:50
# User@Host: db[db] @ localhost [127.0.0.1]
# Query_time: 23 Lock_time: 0 Rows_sent: 0 Rows_examined: 3076542
SELECT post_id FROM jforum_search_wordmatch wm, jforum_search_words w WHERE wm.word_id = w.word_id AND LOWER(w.word) = LOWER('a');
# Time: 050718 19:46:51
# User@Host: db[db] @ localhost [127.0.0.1]
# Query_time: 23 Lock_time: 0 Rows_sent: 6726 Rows_examined: 3076476
SELECT post_id FROM jforum_search_wordmatch wm, jforum_search_words w WHERE wm.word_id = w.word_id AND LOWER(w.word) = LOWER('one');
# Time: 050718 19:46:55
# User@Host: db[db] @ localhost [127.0.0.1]
# Query_time: 26 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
UPDATE jforum_topics SET topic_views = topic_views + 1 WHERE topic_id = 2798;
# Time: 050718 19:47:00
# User@Host: db[db] @ localhost [127.0.0.1]
# Query_time: 40 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
UPDATE jforum_sessions SET session_start = '2005-07-18 19:20:43', session_time = 266859, session_id = 'A6729DC4218EBF5263983EAD966BF420' WHERE session_user_id = 4142;
# Time: 050718 19:47:01
# User@Host: db[db] @ localhost [127.0.0.1]
# Query_time: 24 Lock_time: 0 Rows_sent: 954 Rows_examined: 3076476
SELECT post_id FROM jforum_search_wordmatch wm, jforum_search_words w WHERE wm.word_id = w.word_id AND LOWER(w.word) = LOWER('night');
# User@Host: db[db] @ localhost [127.0.0.1]
# Query_time: 18 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
UPDATE jforum_sessions SET session_start = '2005-07-18 19:25:06', session_time = 8547, session_id = 'ECAC7070A4A8C7FB57D32F51644BE6C2' WHERE session_user_id = 1480;
# Time: 050718 19:47:15
# User@Host: db[db] @ localhost [127.0.0.1]
# Query_time: 24 Lock_time: 0 Rows_sent: 6726 Rows_examined: 3076542
SELECT post_id FROM jforum_search_wordmatch wm, jforum_search_words w WHERE wm.word_id = w.word_id AND LOWER(w.word) = LOWER('one');
# Time: 050718 19:47:22
# User@Host: db[db] @ localhost [127.0.0.1]
# Query_time: 24 Lock_time: 0 Rows_sent: 954 Rows_examined: 3076476
SELECT post_id FROM jforum_search_wordmatch wm, jforum_search_words w WHERE wm.word_id = w.word_id AND LOWER(w.word) = LOWER('night');
# Time: 050718 19:47:25
# User@Host: db[db] @ localhost [127.0.0.1]
# Query_time: 24 Lock_time: 0 Rows_sent: 1 Rows_examined: 3076476
SELECT post_id FROM jforum_search_wordmatch wm, jforum_search_words w WHERE wm.word_id = w.word_id AND LOWER(w.word) = LOWER('stand?\"');
# Time: 050718 19:47:32
# User@Host: db[db] @ localhost [127.0.0.1]
# Query_time: 31 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
INSERT INTO jforum_sessions ( session_id, session_user_id, session_start ) VALUES ('B9CAFD5FED449DF798621F89BDD9B0B5', 11095, '2005-07-18 19:26:30');
# Time: 050718 19:47:36
# User@Host: db[db] @ localhost [127.0.0.1]
# Query_time: 21 Lock_time: 0 Rows_sent: 954 Rows_examined: 3076542
SELECT post_id FROM jforum_search_wordmatch wm, jforum_search_words w WHERE wm.word_id = w.word_id AND LOWER(w.word) = LOWER('night');
# Time: 050718 19:47:41
# User@Host: db[db] @ localhost [127.0.0.1]
# Query_time: 19 Lock_time: 0 Rows_sent: 76 Rows_examined: 3076476
SELECT post_id FROM jforum_search_wordmatch wm, jforum_search_words w WHERE wm.word_id = w.word_id AND LOWER(w.word) = LOWER('stand?');
# Time: 050718 19:47:49
# User@Host: db[db] @ localhost [127.0.0.1]
# Query_time: 13 Lock_time: 0 Rows_sent: 76 Rows_examined: 3076542
SELECT post_id FROM jforum_search_wordmatch wm, jforum_search_words w WHERE wm.word_id = w.word_id AND LOWER(w.word) = LOWER('stand?');
# Time: 050718 19:49:03
# User@Host: db[db] @ localhost [127.0.0.1]
# Query_time: 31 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
UPDATE jforum_sessions SET session_start = '2005-07-18 19:27:46', session_time = 344, session_id = 'D29B79B6789497FEDA6C0AA067758D05' WHERE session_user_id = 5660;
MySql, Version: 4.1.12a-nt-max-log. started with:
TCP Port: 3306, Named Pipe: /tmp/mysql.sock
Time Id Command Argument
Keep in mind this is a dual xeon.
Brakker
[originally posted on jforum.net by coolbreeze]