Board using disk when searching

[useless01]

SMF Version: SMF 1.0.7
I recently moved my board to a new host and noticed that the search will make the server start swapping.

The new server uses MySQL 4.0 while the old one was still using 3.23.

I also recently manually upgraded to SMF 1.0.7 (I didn't run any update script).

This is the output from status.php:

Opened vs. Open tables:
(table_cache)    1.3077 (should be <= 80)
Table cache usage:
(table_cache)    0.1016 (should be >= 0.5 and <= 0.9)
Key buffer read hit rate:
(key_buffer_size)    0.0491 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size)    0.0101 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size)    21.5 (should be >= 30 )
Thread cache usage:
(thread_cache_size)    0.25 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:
(tmp_table_size)    0.9412 (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.4776 (should be <= 0.1)
Query cache prune rate:
(query_cache_size)    0 (should be <= 0.05)



tmp_table_size is set to 134217728 and table_cache 256


This is what happens when searching:


INSERT INTO smf_matches
                SELECT DISTINCT hxxp:t.id [nonactive]_TOPIC, hxxp:t.id [nonactive]_FIRST_MSG, t.numReplies, 0 AS is_subject
                FROM smf_topics AS t, smf_messages AS m
                WHERE hxxp:t.id [nonactive]_TOPIC = hxxp:m.id [nonactive]_TOPIC
                   AND m.body LIKE '%searchedword%' in /....../...../......./......//Sources/Search.php line 425, which took 62.4652540684 seconds.


INSERT IGNORE INTO smf_log_search
                      (ID_SEARCH, ID_TOPIC, relevance, ID_MSG, num_matches)
                   SELECT 12,
             hxxp:t.id [nonactive]_TOPIC,
             30 * IF(m.ID_MSG IS NOT NULL, COUNT(m.ID_MSG) / (t.numReplies + 1), 0) +
             25 * IF(m.ID_MSG IS NULL OR MAX(m.ID_MSG) < 0, 0, (MAX(m.ID_MSG) - 0) / 528747) +
             20 * IF(t.numReplies < 200, t.numReplies / 200, 1) +
             15 * hxxp:t.is [nonactive]_subject +
             10 * IF(MIN(m.ID_MSG) = hxxp:t.id [nonactive]_FIRST_MSG, 1, 0) AS relevance,
             IF(COUNT(m.ID_MSG) = 0, hxxp:t.id [nonactive]_FIRST_MSG, MAX(m.ID_MSG)) AS ID_MSG, COUNT(m.ID_MSG) AS num_matches
          FROM smf_matches AS t
             LEFT JOIN smf_messages AS m ON (m.ID_TOPIC = hxxp:t.id [nonactive]_TOPIC AND m.body LIKE '%searchedword%')
          GROUP BY hxxp:t.id [nonactive]_TOPIC in /....../...../......./....../Sources/Search.php line 495, which took 60.1984839439 seconds.





Any idea what to tweak to make the server use memory and not start swapping when somebody does a search?

Thanks! 

[minskog]

The same problem here 

[Ben_S]

You might want to try using 1.1 which has an optiont to use fulltext search.