Announcement

Collapse
No announcement yet.

What is this query from? It's spiking my server!

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

  • What is this query from? It's spiking my server!

    I noticed a recurring spike on my server around 16:15 each day on my server. I have slow query logging on in MySQL, so I perused through and found this lovely gem:

    Code:
    # Query_time: 52  Lock_time: 0  Rows_sent: 1000  Rows_examined: 5528
    SELECT postid
                    FROM post AS post
                     INNER JOIN thread AS thread ON(thread.threadid = post.threadid)
                    LEFT JOIN deletionlog AS delthread ON(delthread.primaryid = post.threadid AND delthread.type = 'thread')
                    LEFT JOIN deletionlog AS delpost ON(delpost.primaryid = post.postid AND delpost.type = 'post')
                    WHERE post.userid = 1405
                    AND delpost.primaryid IS NULL
                     AND delthread.primaryid IS NULL
                    AND post.visible = 1
                    AND thread.visible = 1
                    AND thread.forumid IN(0,35,36,37,39,47,38,3,4,6,7,8,9,5,11,10,12,13,46,14,20,21,15,16,22,18,19,23,43,24,29,25,26,27,30,31,42,40,41)
                    ORDER BY post.dateline DESC
                    LIMIT 1000;
    Well, a 52 second query is *highly* suspect, so it has to be avoided. Here's the explain on it:

    Code:
    +-----------+--------+-----------------+---------+---------+---------------------+------+--------------------------------------+
    | table     | type   | possible_keys   | key     | key_len | ref                 | rows | Extra                                |
    +-----------+--------+-----------------+---------+---------+---------------------+------+--------------------------------------+
    | post      | ref    | userid,threadid | userid  |       4 | const               | 2892 | Using where; Using filesort          |
    | thread    | eq_ref | PRIMARY,forumid | PRIMARY |       4 | post.threadid       |    1 | Using where                          |
    | delthread | eq_ref | PRIMARY         | PRIMARY |       5 | post.threadid,const |    1 | Using where; Using index; Not exists |
    | delpost   | eq_ref | PRIMARY         | PRIMARY |       5 | post.postid,const   |    1 | Using where; Using index; Not exists |
    +-----------+--------+-----------------+---------+---------+---------------------+------+--------------------------------------+
    4 rows in set (0.02 sec)
    A) What causes it? I'm pretty sure it's a cron, but which one? Any vB devs remember which one it is?
    B) I'm going to see about adding some indexes to fix it, but is there anythign else anyone sees to optimize the query?
    C) Are there any server-level optimizations I could make to buffer sizes to help this query out a bit, too?

    Thanks
    asmallorange
    web hosting, software, and more!

  • #2
    Grep saves all.

    It's in search.php, line 2190. So, next step: speed it up :S
    asmallorange
    web hosting, software, and more!

    Comment


    • #3
      Added an extra index on userid and dateline. That sped it up A LOT

      Edit: If you want to optimize things yourself and don't mind some extra database size (nothing major, just a few MB on large databases), run this query in your vB database:

      Code:
      ALTER TABLE `post` ADD INDEX `datelineuserid` ( `userid` , `dateline` );
      Last edited by timdorr; Wed 19 May '04, 6:49pm.
      asmallorange
      web hosting, software, and more!

      Comment


      • #4
        so whats exactly does this xtra query do? and does any of the vb staff recommend it?

        Comment


        • #5
          Originally posted by timdorr
          Added an extra index on userid and dateline. That sped it up A LOT

          Edit: If you want to optimize things yourself and don't mind some extra database size (nothing major, just a few MB on large databases), run this query in your vB database:

          Code:
          ALTER TABLE `post` ADD INDEX `datelineuserid` ( `userid` , `dateline` );
          You still haven't indicated EXACTLY what causes it and what EXACTLY your fix does to correct it.

          Comment


          • #6
            Someone did a search in all forums, vB must scan the full table for all releveant data back to 1000 positive returns, if the search return limiteris set lower, like 500 or 200 it wouldnt take as long.

            Comment


            • #7
              Sorry, I wasn't monitoring the thread

              The problem is the filesort used on the query originally. That means it's scanning the file, not in-memory data, to order the data for output. This is *really* slow.

              That query adds an index that the server can use to resort the data in memory, rather than making a second pass on the data.
              asmallorange
              web hosting, software, and more!

              Comment


              • #8
                I think that this should be in the future performance release I read about a while back...

                Comment


                • #9
                  Is this still applicable for vb 3.5?

                  Comment


                  • #10
                    anybody?

                    Comment


                    • #11
                      Perhaps you should start your own thread with all the info about the specific problem you are having.
                      Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
                      Change CKEditor Colors to Match Style (for 4.1.4 and above)

                      Steve Machol Photography


                      Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


                      Comment


                      • #12
                        My site is running vBulletin 3.5.4 with this index. Although, I am not sure if this was made a stock index in vBulletin. You might want to check the database with PHPMyAdmin and see if the index is there. If it is not, adding it should not hurt. You can see before and after results using the microstats plugin at vBulletin.org:

                        http://www.vbulletin.org/forum/showthread.php?t=82900

                        If you do this, please report back with the results (if the index is there by default or not, if adding it still makes a difference in performance).

                        Comment

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