Announcement

Collapse
No announcement yet.

MYSQL query overload.... uh... what???

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

  • MYSQL query overload.... uh... what???

    My host tells me this: This is the first time my forum has done this in my whole 2 years of vbulletin.. and I am not sure what to do.. any clue?





    We have conducted a detailed investigation of the reason for the high resource consumption and it turned out that a script executes slow queries towards its database, which eventually hogs the server. The server tries to execute your slow queries while making other processes stay in the queue until some memory is freed. While they are waiting however, they stack up and further deteriorate the problem.

    The reasons for such slow queries can be several:

    1. Large database
    2. Not well written scripts
    3. Large number of internal links that query the database directly.

    In order to have the limitations removed, please optimize your script and/or database. If this problem with your website is not resolved in 7 days we may need to SUSPEND YOUR ACCOUNT.

    All queries sent to your database that exceed 1 second are considered as slow. Such mysql queries increase the CPU and Memory overheat on server thus endanger its overall performance and the other customers web sites on the same machine.







    I'm not a database person at all and so I am unsure what to do.. my forum has 600 members, and growing but I dont know why this is happening.

    Any clue.? Is there any specific thing I can do, or any one I can find to help me fix this?

  • #2
    We won't be able to help unless we know specifically what queries are causing the problems. Your post gives no definitive information.

    Here are some tips to reduce server load:
    http://www.vbulletin.com/forum/showthread.php?t=224462
    Translations provided by Google.

    Wayne Luke
    The Rabid Badger - a vBulletin Cloud demonstration site.
    vBulletin 5 API

    Comment


    • #3
      Since they've done a detailed investigation could you ask them to provide the long queries log entries so we can see what is actually causing it? The vBulletin code and if so which, or a plugin to vBulletin, etc.

      Comment


      • #4
        Here is some things...


        # Time: 071023 7:23:29
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
        use c40kterr_40kterravbull;
        SELECT filedata, dateline, filename
        FROM sigpic
        WHERE userid = 158 AND visible = 1;
        # [email protected]: mbcohao1_wrdp2[mbcohao1_wrdp2] @ localhost []
        --
        SELECT id, CityCode, County, UrlPrefix, UrlSuffix, isComplete FROM agentname;
        # Time: 071023 8:20:20
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
        use c40kterr_40kterravbull;
        REPLACE INTO whoread
        (userid, threadid, dateline, ipaddress)
        VALUES
        (395, 3411, 1193145616, '24.2.119.87');
        --
        SELECT * , IF (DATE_ADD(link_updated, INTERVAL 120 MINUTE) >= NOW(), 1,0) as recently_updated FROM wp_links WHERE 1=1 AND link_visible = 'Y' ORDER BY link_name ASC;
        # Time: 071023 8:22:09
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
        use c40kterr_40kterravbull;
        REPLACE INTO whoread
        (userid, threadid, dateline, ipaddress)
        VALUES
        (570, 50, 1193145727, '213.249.143.246');
        --
        show databases;
        # Time: 071023 9:06:03
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
        use c40kterr_40kterravbull;
        SELECT flashchat_messages.* FROM flashchat_messages LEFT JOIN flashchat_ignors ON (flashchat_ignors.userid=479 AND flashchat_ignors.ignoreduserid=flashchat_messages.userid AND (flashchat_messages.command = 'msg' OR flashchat_messages.command = 'msgu')) WHERE (toconnid='0bdcb00e49bf0df20c07a23b3449d89a' OR touserid='479' OR toroomid='1' OR (toconnid IS NULL AND touserid IS NULL AND toroomid IS NULL)) AND id>='169096' AND flashchat_ignors.created IS NULL ORDER BY id;
        # Time: 071023 9:07:33
        # [email protected]: mbcohao1_wrdp2[mbcohao1_wrdp2] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
        --
        SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN(114,112,110,109,108,107,106,103,102,101) ORDER BY post_id, meta_key;
        # Time: 071023 9:09:17
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
        use c40kterr_40kterravbull;
        REPLACE INTO whoread
        (userid, threadid, dateline, ipaddress)
        VALUES
        (560, 3407, 1193148555, '83.100.131.210');
        --
        ORDER BY m.id DESC LIMIT 0,1;
        # Time: 071023 9:37:11
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
        use c40kterr_40kterravbull;
        SET insert_id=45985;
        INSERT INTO post
        (showsignature, allowsmilie, username, userid, title, pagetext, iconid, visible, parentid, threadid, dateline, ipaddress, attach, ame_flag)
        VALUES
        (1, 1, 'Lord Commander Erus', 479, '', 'Excellent! Looking forward to that!', 0, 1, 45983, 3219, 1193150228, '74.130.86.180', 0, 0);
        # Time: 071023 9:40:17
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
        UPDATE user
        SET lastactivity = 1193150415
        WHERE userid = 429;
        --
        SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN(114,112,110,109,108,107,106,103,102,101) ORDER BY post_id, meta_key;
        # Time: 071023 9:42:17
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
        use c40kterr_40kterravbull;
        SELECT filedata, dateline, filename
        FROM customavatar
        WHERE userid = 92 AND visible = 1;
        # Time: 071023 9:43:16
        --
        SELECT * FROM phpbb_smilies;
        # Time: 071023 10:32:43
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 3 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
        use c40kterr_40kterravbull;
        UPDATE user
        SET
        lastvisit = lastactivity,
        lastactivity = 1193153558
        --
        ORDER BY lastpost DESC LIMIT 0, 10;
        # Time: 071023 11:03:44
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
        use c40kterr_40kterravbull;
        SET timestamp=1193155424;
        # administrator command: Init DB;
        # Time: 071023 11:03:49
        # [email protected]: mbcohao1_wrdp2[mbcohao1_wrdp2] @ localhost []
        --
        SELECT * FROM phpbb_smilies;
        # Time: 071023 11:03:52
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 5 Lock_time: 0 Rows_sent: 20 Rows_examined: 3040
        use c40kterr_40kterravbull;
        SELECT thread.threadid, thread.title, thread.lastpost, thread.forumid, thread.replycount, thread.lastposter, thread.dateline, IF(views<=replycount, replycount+1, views) AS views, thread.visible, thread.open, user.username, user.userid, user.usergroupid, IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
        FROM thread AS thread
        LEFT JOIN user AS user ON (user.username = thread.lastposter)
        WHERE NOT ISNULL(thread.threadid) AND thread.visible = '1' AND thread.open!='10' AND thread.forumid NOT IN(25,141,139,82,85,167,185)
        --
        order by s.sid desc limit 20;
        # Time: 071023 11:05:02
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 3 Lock_time: 0 Rows_sent: 5 Rows_examined: 25
        use c40kterr_40kterravbull;
        SELECT COUNT(*) AS count, threadid, MAX(dateline) AS lastpost

        FROM post AS post

        --
        WHERE title IN ('','options','bitfields','attachmentcache','forumcache','usergroupcache','stylecache','la nguagecache','products','pluginlist','cron','profilefield');
        # Time: 071023 11:13:53
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 3 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
        use c40kterr_40kterravbull;
        UPDATE user SET lastactivity='1193156030' WHERE userid='129';
        # Time: 071023 11:13:54
        # [email protected]: reflect2[reflect2] @ localhost []
        # Query_time: 4 Lock_time: 0 Rows_sent: 12 Rows_examined: 12
        --
        WHERE BINARY `TABLE_SCHEMA` IN ('reflect2_radio');
        # Time: 071023 11:19:05
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 2 Rows_examined: 152
        use c40kterr_40kterravbull;
        SELECT COUNT(*) AS total, folderid
        FROM pm AS pm
        LEFT JOIN pmtext AS pmtext USING(pmtextid)
        WHERE userid = 177
        --
        INSERT INTO threadviews (threadid)
        VALUES (3237);
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
        use c40kterr_40kterravbull;
        SELECT flashchat_messages.* FROM flashchat_messages LEFT JOIN flashchat_ignors ON (flashchat_ignors.userid=73 AND flashchat_ignors.ignoreduserid=flashchat_messages.userid AND (flashchat_messages.command = 'msg' OR flashchat_messages.command = 'msgu')) WHERE (toconnid='2e92310733fb233306e0eac3f3f70eac' OR touserid='73' OR toroomid='1' OR (toconnid IS NULL AND touserid IS NULL AND toroomid IS NULL)) AND id>='169493' AND flashchat_ignors.created IS NULL ORDER BY id;
        # Time: 071023 11:21:59
        # [email protected]: mallutub_indian[mallutub_indian] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
        --
        SELECT comment_date FROM wp_comments WHERE comment_author_IP = '83.237.99.87' ORDER BY comment_date DESC LIMIT 1;
        # Time: 071023 11:22:09
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 13 Rows_examined: 13
        use c40kterr_40kterravbull;
        SELECT *
        FROM datastore
        WHERE title IN ('','options','bitfields','attachmentcache','forumcache','usergroupcache','stylecache','la nguagecache','products','pluginlist','cron','profilefield','iconcache','mailqueue');
        # Time: 071023 11:22:23
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 30 Rows_examined: 31
        SELECT flashchat_messages.* FROM flashchat_messages LEFT JOIN flashchat_ignors ON (flashchat_ignors.userid=129 AND flashchat_ignors.ignoreduserid=flashchat_messages.userid AND (flashchat_messages.command = 'msg' OR flashchat_messages.command = 'msgu')) WHERE (toconnid='4a30ec15536d481919cf1d1fc4a0145b' OR touserid='129' OR toroomid='1' OR (toconnid IS NULL AND touserid IS NULL AND toroomid IS NULL)) AND id>='169502' AND flashchat_ignors.created IS NULL ORDER BY id;
        # Time: 071023 11:22:25
        # [email protected]: mallutub_indian[mallutub_indian] @ localhost []
        --
        ORDER BY cat_id, forum_order;
        # Time: 071023 11:27:48
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 1 Rows_examined: 1
        use c40kterr_40kterravbull;
        SELECT flashchat_messages.* FROM flashchat_messages LEFT JOIN flashchat_ignors ON (flashchat_ignors.userid=425 AND flashchat_ignors.ignoreduserid=flashchat_messages.userid AND (flashchat_messages.command = 'msg' OR flashchat_messages.command = 'msgu')) WHERE (toconnid='8df57d9cc781efa3691cabf8de0d5e24' OR touserid='425' OR toroomid='1' OR (toconnid IS NULL AND touserid IS NULL AND toroomid IS NULL)) AND id>='169632' AND flashchat_ignors.created IS NULL ORDER BY id;
        # [email protected]: simplic1_bathcha[simplic1_bathcha] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
        use simplic1_bathmatechat;
        --
        page_id=64 AND mpvar_hash=222419149;
        # Time: 071023 11:35:09
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 3 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
        use c40kterr_40kterravbull;
        UPDATE user
        SET
        lastvisit = lastactivity,
        lastactivity = 1193157305
        --
        ORDER BY ordering;
        # Time: 071023 11:48:58
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
        use c40kterr_40kterravbull;
        SELECT data
        FROM datastore
        WHERE title = 'photoplog_dscat';
        # [email protected]: mallutub_indian[mallutub_indian] @ localhost []
        --
        SELECT * from bvars;
        # Time: 071023 11:56:31
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 16 Rows_examined: 32
        use c40kterr_40kterravbull;
        SELECT post.postid
        FROM post AS post

        WHERE post.threadid = 3169
        --
        LIMIT 0, 30;
        # Time: 071023 11:56:34
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
        REPLACE INTO whoread
        (userid, threadid, dateline, ipaddress)
        VALUES
        --
        SHOW TABLES;
        # Time: 071023 11:59:50
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 16 Rows_examined: 135
        use c40kterr_40kterravbull;
        SELECT
        post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS isdeleted,
        user.*, userfield.*, usertextfield.*,
        icon.title as icontitle, icon.iconpath,
        --
        WHERE post.postid IN (0,45932,45934,45937,45940,45942,45943,45944,45945,45996,46000,46004,46005,46017,46019,460 20,46021)
        ORDER BY post.dateline;
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 3 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
        UPDATE user
        SET lastactivity = 1193158786
        WHERE userid = 263;
        --
        use mbcohao1_wrdp2;
        SHOW TABLES;
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
        use c40kterr_40kterravbull;
        TRUNCATE TABLE attachmentviews;
        # Time: 071023 13:11:12
        # [email protected]: gamehu1[gamehu1] @ localhost []
        # Query_time: 6 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
        --
        SELECT id, CityCode, County, UrlPrefix, UrlSuffix, isComplete FROM agentname;
        # Time: 071023 13:21:10
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 7349
        use c40kterr_40kterravbull;
        SELECT pmtext.pmtextid
        FROM pmtext AS pmtext
        LEFT JOIN pm AS pm USING(pmtextid)
        WHERE pm.pmid IS NULL;
        --
        SELECT session_data FROM horde_sessionhandler WHERE session_id = '58c59a5f5b8b21245d4ceab46a7d9e54' AND session_lastmodified > 1193167116 FOR UPDATE;
        # Time: 071023 14:46:27
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 11 Rows_examined: 11
        use c40kterr_40kterravbull;
        SELECT *
        FROM datastore
        WHERE title IN ('','options','bitfields','attachmentcache','forumcache','usergroupcache','stylecache','la nguagecache','products','pluginlist','cron','profilefield');
        # Time: 071023 14:46:45
        --
        show databases;
        # Time: 071023 15:21:33
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
        use c40kterr_40kterravbull;
        UPDATE user
        SET lastactivity = 1193170889
        WHERE userid = 34;
        # Time: 071023 15:21:34
        --
        ORDER BY lastpost DESC LIMIT 0, 10;
        # Time: 071023 16:26:26
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 5 Lock_time: 0 Rows_sent: 0 Rows_examined: 7349
        use c40kterr_40kterravbull;
        SELECT pmtext.pmtextid
        FROM pmtext AS pmtext
        LEFT JOIN pm AS pm USING(pmtextid)
        WHERE pm.pmid IS NULL;
        --
        LIMIT 14;
        # Time: 071023 19:03:18
        # [email protected]: c40kterr_40kterr[c40kterr_40kterr] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
        use c40kterr_40kterravbull;
        SET timestamp=1193184198;
        DELETE FROM flashchat_bans WHERE created < DATE_SUB(NOW(),INTERVAL '36000' SECOND);
        # Time: 071023 19:04:20
        # [email protected]: mbcohao1_wrdp2[mbcohao1_wrdp2] @ localhost []
        --
        # [email protected]: root[root] @ localhost []
        # Query_time: 2 Lock_time: 0 Rows_sent: 44241 Rows_examined: 44241
        use c40kterr_40kterravbull;
        SELECT /*!40001 SQL_NO_CACHE */ * FROM `post`;

        Comment


        • #5
          REPLACE INTO whoread
          ### whoread is not a vBulletin table

          SELECT flashchat_messages.* FROM flashchat_messages
          ### flashchat is not a vBulletin table

          SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id
          ### wp_postmeta, I suspect this not to be a field from vBulletin

          SELECT * FROM phpbb_smilies;
          ### phpBB smilies is not from vBulletin

          WHERE BINARY `TABLE_SCHEMA` IN ('reflect2_radio');
          ### This is not a vBulletin field or table, probably a plugin

          INSERT INTO threadviews (threadid)
          VALUES (3237);
          ### Uses flashchat integration, no longer a default vBulletin query

          SELECT comment_date FROM wp_comments WHERE comment_author_IP
          ### I believe this is wordpress, and not vBulletin

          SELECT data
          FROM datastore
          WHERE title = 'photoplog_dscat';
          ### This is also a plugin, and no longer default vBulletin

          use mbcohao1_wrdp2;
          SHOW TABLES;
          ### This is not a vBulletin table or field

          DELETE FROM flashchat_bans WHERE created < DATE_SUB(NOW(),INTERVAL '36000' SECOND);
          ### flashchat addon again, not vBulletin

          Comment

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