Announcement

Collapse
No announcement yet.

Large Message Board, Slow Query Killing Performance

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

  • Large Message Board, Slow Query Killing Performance

    I admin a large message board with over 10 million posts. We recently upgraded to vBulletin 3.7.3. Within a couple of hours we fell afoul of the following query in forumdisplay.php:

    Code:
    $hook_query_fields = $hook_query_joins = $hook_query_where = '';
    ($hook = vBulletinHook::fetch_hook('forumdisplay_query_threadscount')) ? eval($hook) : false;
    
    # Include visible IN (0,1,2) in order to hit upon the 4 column index
    $threadscount = $db->query_first_slave("
             SELECT COUNT(*) AS threads, SUM(IF(thread.lastpost > $lastread AND open <> 10, 1, 0)) AS newthread
             $hook_query_fields
             FROM " . TABLE_PREFIX . "thread AS thread
             $tachyjoin
             $hook_query_joins
             WHERE forumid = $foruminfo[forumid]
                     AND sticky = 0
                     $prefix_filter
                     $visiblethreads
                     $globalignore
                     $limitothers
                     $datecut
                     $hook_query_where
    ");
    $totalthreads = $threadscount['threads'];
    $newthreads = $threadscount['newthread'];
    During the busiest times on the message board these queries would stack up eventually causing other queries to be locked out. A series of interlocking read and write locks grinding everything to a halt.

    I've hacked this code setting $totalthreads to 200 and $newthreads to 0 so this query does not have to be run. Setting $totalthreads to 200 means people can only browse back 200 threads in any forum. Setting $newthreads to 0 screws around with the whether or not to highlight a thread's folder icon in the forum display. Obviously not the best solution but at least the message board doesn't get driven to it's knees.

    Any suggestions on how I can hack this query to get it to be more efficient? It's the slowest query on our message board and once it starts to stack up everything eventually goes down.

  • #2
    Since it tries to load a hook, could it be that it's the plugin using that hook that's causing it?

    Comment


    • #3
      How do I determine what plugin would be using that hook? I'm afraid I don't have any familiarity with either the hook concept or what the plugins are.

      Comment


      • #4
        Besides the blog from vb and/or the PT (project manager) from vb, do you have any plugins from vbulletin.org installed?

        Comment


        • #5
          Originally posted by Floris View Post
          Besides the blog from vb and/or the PT (project manager) from vb, do you have any plugins from vbulletin.org installed?
          Nothing that I'm aware of. We had been running stock vBulletin 3.0.7 for years and then recently upgraded to stock 3.7.3. No plugins of any kind.

          I don't believe the hook call is significant unless it's a conditional on whether or not the SQL query runs. When the SQL query stacks up we then see this behavior in a MySQL processlist:

          Code:
          +--------+--------------+------------------+------+---------+-------+----------------+------------------------------------------------------------------------------------------------------+
          | Id     | User         | Host             | db   | Command | Time  | State          | Info                                                                                                 |
          +--------+--------------+------------------+------+---------+-------+----------------+------------------------------------------------------------------------------------------------------+
          | 259744 | yyyyyyyyyyyy | xx.xx.x.xx:50848 | zzzz | Sleep   | 10321 |                |                                                                                                      |
          | 327058 | yyyyyyyyyyyy | xx.xx.x.xx:63921 | zzzz | Query   | 3094  | Sending data   | SELECT COUNT(*) AS threads, SUM(IF(thread.lastpost > 1220492330 AND open <> 10, 1, 0)) AS newthread  |
          | 327173 | yyyyyyyyyyyy | xx.xx.x.xx:58387 | zzzz | Query   | 3054  | Sending data   | SELECT COUNT(*) AS threads, SUM(IF(thread.lastpost > 1220488275 AND open <> 10, 1, 0)) AS newthread  |
          | 327244 | yyyyyyyyyyyy | xx.xx.x.xx:64113 | zzzz | Query   | 3032  | Sending data   | SELECT COUNT(*) AS threads, SUM(IF(thread.lastpost > 1220492330 AND open <> 10, 1, 0)) AS newthread  |
          ...
          | 329924 | yyyyyyyyyyyy | xx.xx.x.xx:61719 | zzzz | Query   | 1703  | Locked         | SELECT  thread.threadid, thread.lastpost, thread.lastposter, thread.lastpostid, replycount, IF(vi    |
          All it takes is for a THREAD write lock to come in when one of these slow THREAD read lock queries is running and then subsequent queries get locked out until MySQL chokes on the number of threads.
          Last edited by Jerry Davis; Sun 5 Oct '08, 5:38pm.

          Comment


          • #6
            Go change these settings:

            Admin Control Panel > vBulletin Options > vBulletin Options > Hook System: Active? NO (turn it off)

            Admin Control Panel > vBulletin Options > vBulletin Options > Forum Display Options > Update Thread Views Immediately? Set this to no.

            Comment


            • #7
              I've checked our vBulletin options and both of those values are already set to no.

              We have 478,775 threads currently. Any idea how that factors into the performance of the query?

              Comment


              • #8
                Then I do not know, could be reported as a bug so a developer can look into it. Sorry that I can't help any further.

                Comment

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