I just updated this forum http://yabb.jriver.com/interact from yabbse 1.5 to SMF 1.0rc2. SE was working well but I was concerned about future security risks. The upgrade went well for the most part but I'm getting some seemingly random LONG pauses, like a minute or so ocassionally. This wasn't a problem with SE. I turned on the slow-queries log in mysql and am seeing stuff like the following in it:
# Query_time: 58 Lock_time: 0 Rows_sent: 0 Rows_examined: 167349
INSERT INTO interact_matches
SELECT DISTINCT t.ID_TOPIC, t.ID_FIRST_MSG, t.numReplies, 0 AS is_subject
FROM interact_topics AS t, interact_messages AS m
WHERE t.ID_TOPIC = m.ID_TOPIC
AND m.ID_BOARD IN (2, 3)
AND m.body RLIKE '[[:<:]]install[[:>:]]';
I believe this means 58 SECONDS on the query which would appear to match what I'm seeing.
This is running on an AIX box with MySQL 3.23.58-Max-log, same as what SE was using.
Forum Stats
Total Topics: 24604 Total Posts: 170636
DNS resolution is off everywhere.
Any ideas about what could be causing this hesitation??
I believe it's that you are using MySQL 3 and SMF is optimised for version 4. The actual problem is in the search which would be consistant. I believe RC2 has an option in "Feature Settings and Options" to disable caching is results, so turn this off
You may want to turn off the option to do "whole word search" under "Search" options. It will perform better.
I also recommend upgrading to MySQL 4 if at all possible.
SELECT DISTINCT t.ID_TOPIC, t.ID_FIRST_MSG, t.numReplies, 0 AS is_subject
FROM interact_topics AS t, interact_messages AS m
WHERE t.ID_TOPIC = m.ID_TOPIC
AND m.ID_BOARD IN (2, 3)
AND m.body RLIKE '[[:<:]]install[[:>:]]';
Thinking out loud, I wonder if an index on both ID_BOARD and ID_TOPIC would help this query? Probably not much, because we have body in there anyway...
-[Unknown]
I did both of the suggestions and it seems like everything is ok for now. I've got other stuff on that mysql server so I'm not hurrying to install version 4 yet....