Advertisement:

Author Topic: Problem with INCREDIBLY slow mysql queries  (Read 13437 times)

Offline bob

  • Newbie
  • *
  • Posts: 9
Problem with INCREDIBLY slow mysql queries
« on: December 10, 2004, 03:47:26 PM »
I just updated this forum hxxp:yabb.jriver.com/interact [nonactive] 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??


Offline Grudge

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 10,742
  • Gender: Male
  • Unofficial nuisance
Re: Problem with INCREDIBLY slow mysql queries
« Reply #1 on: December 10, 2004, 04:59:31 PM »
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
I'm only a half geek really...

Offline [Unknown]

  • SMF Friend
  • SMF Master
  • *
  • Posts: 36,102
  • Gender: Male
Re: Problem with INCREDIBLY slow mysql queries
« Reply #2 on: December 10, 2004, 08:18:57 PM »
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.

Code: [Select]
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]

Offline bob

  • Newbie
  • *
  • Posts: 9
Re: Problem with INCREDIBLY slow mysql queries
« Reply #3 on: December 13, 2004, 04:31:41 PM »
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....