Announcement

Collapse
No announcement yet.

Any idea what could be causing this query/locking issues?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Any idea what could be causing this query/locking issues?

    From show processlist;

    It's happened twice today (and it locked for 240 seconds and I just killed mysqld and restarted it)... Why is there no WHERE clause on this SELECT? Any idea what would generate such a query? With almost 350k threads, that'd be quite a tmp table....

    PHP Code:
    Query   |   61 Copying to tmp table SELECT
                            DISTINCT thread
    .threadidthread.forumidpost.userid
                            FROM thread 
    AS thread 
    Webkinz

  • #2
    That query is not familiar. If you can find a specific URL or file in the logs then that will help to identify it.

    Comment


    • #3
      Yikes, my log files are a good 3GB every day. That would be very difficult to track down. So SELECT DISTINCT on the thread table is not a vB standard query? Must be some hack then. Thanks!

      ETA: I just duplicated the error myself and figured it out... When I upgraded to 3.7.3 it re-wrote my search.php which was using sphinx (have about 8.5M posts). In overwriting my search.php, it reverted to FULLTEXT search and when searching a VERY common term, it will lock up the database for 5-6 minutes.
      Last edited by jawatkin; Sat 13th Sep '08, 6:02am.
      Webkinz

      Comment


      • #4
        You may want to adjust your MySQL settings for the fulltext search to restrict it further. For example, increase the minimum word length:

        http://www.vbulletin.com/docs/html/main/search_type

        Comment


        • #5
          Originally posted by Jake Bunce View Post
          You may want to adjust your MySQL settings for the fulltext search to restrict it further. For example, increase the minimum word length:

          http://www.vbulletin.com/docs/html/main/search_type
          Thanks Jake, it already is fulltext, sphinx does all the heavy-lifting and allows me to use the search function. With the # of posts I have, there's just no way fulltext can even handle the load for commonly-used words.
          Webkinz

          Comment


          • #6
            I am having the same problem. This query is killing the server.

            SELECT
            DISTINCT thread.threadid
            FROM vb_thread AS thread
            INNER JOIN vb_post AS post ON(thread.threadid = post.threadid )
            WHERE MATCH(post.title, post.pagetext) AGAINST ('hair')
            AND thread.forumid NOT IN (0,26,15,5,13,18,25)
            AND thread.forumid IN(27) AND post.visible = 1
            LIMIT 200;
            We have close to 4M posts and have changed to FULLTEXT when we upgraded to 3.7+

            Would reverting back to the old search method make things faster then and rectify the problems we have? Changing the minimum word length is not an option for us since our content has a lot of 3 character length words.

            Comment


            • #7
              Another question, what is the preferred storage engine? MyISAM or InnoDB? I know MyISAM is faster but InnoDB allows transactions. Is there a preference considering performance?

              Comment


              • #8
                The only two options are:

                1) Try the other search engine.

                2) Disable the search feature.

                Unfortunately the current search engines in vBulletin don't scale well on forums with excessive numbers of posts.

                I know that MyISAM is recommended, but I am not familiar with the benefits of both table types. You may want to ask this in the server forum:

                http://www.vbulletin.com/forum/forumdisplay.php?f=14

                Comment


                • #9
                  Thank you very much for the reply.

                  Comment


                  • #10
                    What is the solution if already on fulltext search? Disable search? Seriously?

                    Comment

                    widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                    Working...
                    X