No announcement yet.

MySQL-query overloading our server

  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL-query overloading our server

    We have a constantly recurring overloading issue with our server. The cause of the problem seems to be some kind of bottleneck with the mysql server. The simultaneous mysql connections oscillates normally around 3-10, but hits the roof (300) in a couple of seconds between 8-9 P.M. This causes the server to overload completely.

    I have followed the mysql process list during just before the crashes, and noticed some queries that seem to piling up the following ones. Especially queries marked with state "sorting result" and "copying to tmp" seem to be the problematic ones.

    Take a look at the following one, it showed up in my process list this evening, just before the server went down.

         Id: 1712173
       User: forum
       Host: localhost
         db: forum_h
    Command: Query
       Time: 29
      State: Sorting result
       Info: SELECT distinct *  
        FROM thread  
        WHERE forumid IN (6,56,55,54,74,30,27,29,20,24,28,113,92,49,1,8,9,22,21,23,10,11,12,13,25,14,26,15,17,94,19,95,16,110,18,111,7,57,62,66,61,67,63,65,58,59,60,64,5,52,53,50,51,107,4,73,93,48,47,70,71,72,3,38,109,41,39,112,42,108,43,40,46,45,44,36,37,115,116,134,117,135,118,138,120,127,128,131,2,32,34) AND open=1 
        ORDER BY replycount DESC  
        LIMIT 10
    What could be wrong? Could it be this query? Or is it something else?

  • #2
    I don't recognize that specific query. If you can get a corresponding file name or URL from your logs then we might be able to identify it. Otherwise here is my canned response regarding performance problems:


    Here are some intensive features you can toggle to help performance:

    Admin CP -> vBulletin Options -> General Settings -> Thread/Forum Read Marking Type

    Admin CP -> vBulletin Options -> Forum Display Options (forumdisplay) -> Show Users Browsing Forums

    Admin CP -> vBulletin Options -> Forums Home Page Options -> Display Logged in Users?

    Admin CP -> vBulletin Options -> Message Searching Options -> Search Engine Enabled

    Admin CP -> vBulletin Options -> Email Options -> Use Mailqueue System

    Admin CP -> vBulletin Options -> Server Settings and Optimization Options -> Update Thread / Attachment Views Immediately

    Admin CP -> vBulletin Options -> Plugin/Hook System -> Enable Plugin/Hook System

    If you continue to have slowness problems then I recommend you post in the Server Configuration forum. Post the info requested in this thread and they will be able to make recommendations:


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