Announcement

Collapse
No announcement yet.

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

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

  • Floris
    replied
    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

    Leave a comment:


  • chobo
    replied
    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`;

    Leave a comment:


  • Floris
    replied
    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.

    Leave a comment:


  • Wayne Luke
    replied
    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

    Leave a comment:


  • chobo
    started a topic MYSQL query overload.... uh... what???

    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?
widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
Working...
X