Announcement

Collapse
No announcement yet.

Help IDing SQL Problem

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

  • Help IDing SQL Problem

    My host just suspended my account because my site caused server performance problems.

    I have FTP and cPanel access, so if there is anything that I can do, please let me know. They sent me the SQL query that caused the problems.

    I need some help identifying what the problem is with this - what part of the site could be causing the problems.

    My site usually has between 5 - 15 members on at once, and I have vbAdvanced running.

    * Note: I believe the first number in the lines are log line numbers. *

    Here is the SQL query:
    Code:
    3343 newurba1_ localhost newurba1_v 1088 Query SELECT searchid, dateline FROM search AS search WHERE userid = 11 ORDER BY dateline DESC LIMIT 1
    3335 newurba1_ localhost newurba1_v 1090 Query SELECT searchid, dateline FROM search AS search WHERE userid = 11 ORDER BY dateline DESC LIMIT 1
    3323 newurba1_ localhost newurba1_v 1092 Query SELECT searchid, dateline FROM search AS search WHERE userid = 11 ORDER BY dateline DESC LIMIT 1
    3294 newurba1_ localhost newurba1_v 1098 Query SELECT searchid, dateline FROM search AS search WHERE userid = 11 ORDER BY dateline DESC LIMIT 1
    3166 newurba1_ localhost newurba1_v 1119 Query SELECT searchid, dateline FROM search AS search WHERE userid = 11 ORDER BY dateline DESC LIMIT 1
    3154 newurba1_ localhost newurba1_v 1121 Query SELECT searchid, dateline FROM search AS search WHERE userid = 11 ORDER BY dateline DESC LIMIT 1
    3120 newurba1_ localhost newurba1_v 1128 Query SELECT searchid, dateline FROM search AS search WHERE userid = 11 ORDER BY dateline DESC LIMIT 1
    3027 newurba1_ localhost newurba1_v 1150 Query SELECT searchid, dateline FROM search AS search WHERE userid = 11 ORDER BY dateline DESC LIMIT 1
    3017 newurba1_ localhost newurba1_v 1152 Query SELECT searchid, dateline FROM search AS search WHERE userid = 11 ORDER BY dateline DESC LIMIT 1
    2967 newurba1_ localhost newurba1_v 1158 Query SELECT searchid, dateline FROM search AS search WHERE userid = 11 ORDER BY dateline DESC LIMIT 1
    2893 newurba1_ localhost newurba1_v 1171 Query SELECT searchid, dateline FROM search AS search WHERE userid = 26 ORDER BY dateline DESC LIMIT 1
    2523 newurba1_ localhost newurba1_v 1233 Query SELECT searchid, dateline FROM search AS search WHERE userid = 7 ORDER BY dateline DESC LIMIT 1
    1931 newurba1_ localhost newurba1_v 1332 Query SELECT searchid, dateline FROM search AS search WHERE userid = 26 ORDER BY dateline DESC LIMIT 1
    1366 newurba1_ localhost newurba1_v 1433 Query SELECT searchid, dateline FROM search AS search WHERE userid = 26 ORDER BY dateline DESC LIMIT 1
    1344 newurba1_ localhost newurba1_v 1436 Query SELECT searchid, dateline FROM search AS search WHERE userid = 26 ORDER BY dateline DESC LIMIT 1
    1006 newurba1_ localhost newurba1_v 1495 Query SELECT searchid, dateline FROM search AS search WHERE userid = 26 ORDER BY dateline DESC LIMIT 1
    926 newurba1_ localhost newurba1_v 1515 Query SELECT searchid, dateline FROM search AS search WHERE userid = 26 ORDER BY dateline DESC LIMIT 1
    777 newurba1_ localhost newurba1_v 1551 Query SELECT searchid, dateline FROM search AS search WHERE userid = 26 ORDER BY dateline DESC LIMIT 1
    685 newurba1_ localhost newurba1_v 1566 Query SELECT searchid, dateline FROM search AS search WHERE userid = 26 ORDER BY dateline DESC LIMIT 1
    591 newurba1_ localhost newurba1_v 1585 Query SELECT searchid, dateline FROM search AS search WHERE userid = 26 ORDER BY dateline DESC LIMIT 1
    I am running 3.6.5

  • #2
    First, reupload all the original vB non-image files (except install.php). Make sure you upload these in ASCII format and overwrite the ones on the server. Also be sure to upload the admincp files to whichever directory you have set in your config.php file. Then run 'Suspect File Versions' in Diagnostics to make sure you have all the original files for your version:

    Admin CP -> Maintenance -> Diagnostics -> Suspect File Versions

    [Note: In some cases you may also need to remove any of the listed .xml files in the includes/xml directory.]

    Next, disable all plugins.

    Note: To temporarily disable the plugin system, edit config.php and add this line right under <?php

    define('DISABLE_HOOKS', true);

    Then if you still have this problem, create a new style and choose no parent style. This will force it to use the default templates. Finally empty your browser cache, close all browser windows then try again. Make sure you change to the new style and view your forums with it. Now check again.

    Do you still have the same problem?


    Also, you should follow the instructions in this thread
    How To Reduce Server Load
    Kerry-Anne :)

    Twitter Blog

    www.peterska2.com www.worldnewszone.com www.popularusenetgroups.com www.superclickers.co.uk www.forumsforchrist.com www.browsergameplanet.com

    Comment


    • #3
      Thanks for the tips, Kerry-Anne. Unfortuantely, I am unable to access my site (at the moment, I will try and get them to reinstant it so I can work on it).

      Any idea as to what caused those SQL queries? It looks like folks possibly looking for New Posts. But that shouldn't bring down the barn.

      Comment


      • #4
        They are all searches. If your server is overloaded or improperly configured or you have too little resources alloted then it can cause problems. We can't really tell what the real problem is by the log though. Something with timestamps and being able to look at server setup would be more helpful. The log the sent you tell a lot of nothing really.

        Sorry.
        Translations provided by Google.

        Wayne Luke
        The Rabid Badger - a vBulletin Cloud demonstration site.
        vBulletin 5 API - Full / Mobile
        Vote for your favorite feature requests and the bugs you want to see fixed.

        Comment


        • #5
          I have raw access logs, slow sql query logs, and cpu exceeded quote logs to go off of.

          Looking at the slow sql query logs, people were having trouble with the getnew search.

          I do have some plugins installed - but nothing to do with retreiving new posts - I don't think?

          Here is a snippet from the sloq sql queries log:

          Code:
          # Wed Jun 13 14:46:22 2007
          # Query_time: 28  Lock_time: 24  Rows_sent: 1  Rows_examined: 0
          use newurba1_vbulletin;
          SELECT *
            FROM session
            WHERE sessionhash = '9444c3511bed90c6439f5f699272762c'
            AND lastactivity > 1181766654
            AND idhash = 'ec2892f426e46a5d3f3ca2a7223731f4'
           
          # Wed Jun 13 14:46:22 2007
          # Query_time: 27  Lock_time: 24  Rows_sent: 1  Rows_examined: 0
          use newurba1_vbulletin;
          SELECT *
            FROM session
            WHERE sessionhash = '9444c3511bed90c6439f5f699272762c'
            AND lastactivity > 1181766655
            AND idhash = 'ec2892f426e46a5d3f3ca2a7223731f4'
           
          # Wed Jun 13 15:52:55 2007
          # Query_time: 1186  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
          SELECT searchid, dateline
            FROM search AS search
            WHERE userid = 11
            ORDER BY dateline DESC LIMIT 1
           
          # Wed Jun 13 15:52:58 2007
          # Query_time: 1187  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
          SELECT searchid, dateline
            FROM search AS search
            WHERE userid = 11
            ORDER BY dateline DESC LIMIT 1
          From the raw access logs, here is what was going on during those times (I've removed the lines retreiving graphics from my theme, removed references to my URL, and IP addresses):

          Code:
          14:46
          [13/Jun/2007:14:46:27 -0600] "POST /pager.php?action=pager&do=readpager& HTTP/1.1" 200 33 "http://www.mywebsite.com/showthread.php?t=1061&page=7" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)"
          [13/Jun/2007:14:46:22 -0600] "POST /pager.php?action=pager&do=readpager& HTTP/1.1" 200 33 "http://www.mywebsite.com/private.php...owpm&pmid=2859" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322; .NET CLR 2.0.50727)"
          [13/Jun/2007:14:46:22 -0600] "POST /pager.php?action=pager&do=readpager& HTTP/1.1" 200 33 "http://www.mywebsite.com/showthread.php?t=1061&page=7" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)"
          [13/Jun/2007:14:46:22 -0600] "POST /pager.php?action=pager&do=readpager& HTTP/1.1" 200 33 "http://www.mywebsite.com/" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322; .NET CLR 2.0.50727)"
          [13/Jun/2007:14:46:32 -0600] "GET /search.php?do=getnew HTTP/1.1" 301 5 "http://www.mywebsite.com/" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322; .NET CLR 2.0.50727)"
          [13/Jun/2007:14:46:33 -0600] "POST /pager.php?action=pager&do=readpager& HTTP/1.1" 200 33 "http://www.mywebsite.com/" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322; .NET CLR 2.0.50727)"
          [13/Jun/2007:14:46:33 -0600] "POST /pager.php?action=pager&do=readpager& HTTP/1.1" 200 33 "http://www.mywebsite.com/showthread.php?t=1061&page=7" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)"
          [13/Jun/2007:14:46:33 -0600] "GET /search.php?do=getnew HTTP/1.1" 301 5 "http://www.mywebsite.com/" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322; .NET CLR 2.0.50727)"
          [13/Jun/2007:14:46:34 -0600] "GET /search.php?searchid=12494 HTTP/1.1" 200 82866 "http://www.mywebsite.com/" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322; .NET CLR 2.0.50727)"
          [13/Jun/2007:14:46:35 -0600] "POST /pager.php?action=pager&do=readpager& HTTP/1.1" 200 33 "http://www.mywebsite.com/search.php?searchid=12494" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322; .NET CLR 2.0.50727)"
          [13/Jun/2007:14:46:38 -0600] "GET /search.php?do=getnew HTTP/1.1" 301 5 "-" "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.0.04506; .NET CLR 1.1.4322)"
          [13/Jun/2007:14:46:38 -0600] "POST /pager.php?action=pager&do=readpager& HTTP/1.1" 200 34 "http://www.mywebsite.com/showthread.php?t=1061&page=7" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)"
          [13/Jun/2007:14:46:39 -0600] "GET /search.php?searchid=12490 HTTP/1.1" 200 11602 "-" "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.0.04506; .NET CLR 1.1.4322)"
          [13/Jun/2007:14:46:39 -0600] "GET /clientscript/vbulletin_global.js?v=365 HTTP/1.1" 304 0 "http://www.mywebsite.com/search.php?searchid=12490" "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.0.04506; .NET CLR 1.1.4322)"
          [13/Jun/2007:14:46:39 -0600] "GET /clientscript/vbulletin_menu.js?v=365 HTTP/1.1" 304 0 "http://www.mywebsite.com/search.php?searchid=12490" "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.0.04506; .NET CLR 1.1.4322)"
          [13/Jun/2007:14:46:40 -0600] "POST /pager.php?action=pager&do=readpager& HTTP/1.1" 200 34 "http://www.mywebsite.com/search.php?searchid=12494" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322; .NET CLR 2.0.50727)"
          [13/Jun/2007:14:46:41 -0600] "GET /vbspell.js HTTP/1.1" 304 0 "http://www.mywebsite.com/search.php?searchid=12490" "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.0.04506; .NET CLR 1.1.4322)"
          [13/Jun/2007:14:46:41 -0600] "GET /clientscript/vbulletin_inlinemod.js?v=365 HTTP/1.1" 304 0 "http://www.mywebsite.com/search.php?searchid=12490" "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.0.04506; .NET CLR 1.1.4322)"
          [13/Jun/2007:14:46:43 -0600] "GET /clientscript/vbulletin_ajax_threadslist.js?v=365 HTTP/1.1" 304 0 "http://www.mywebsite.com/search.php?searchid=12490" "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.0.04506; .NET CLR 1.1.4322)"
          [13/Jun/2007:14:46:44 -0600] "POST /pager.php?action=pager&do=readpager& HTTP/1.1" 200 33 "http://www.mywebsite.com/showthread.php?t=1061&page=7" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)"
          [13/Jun/2007:14:46:44 -0600] "POST /pager.php?action=pager&do=readpager& HTTP/1.1" 200 33 "http://www.mywebsite.com/search.php?searchid=12490" "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.0.04506; .NET CLR 1.1.4322)"
          [13/Jun/2007:14:46:46 -0600] "POST /pager.php?action=pager&do=readpager& HTTP/1.1" 200 33 "http://www.mywebsite.com/search.php?searchid=12494" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322; .NET CLR 2.0.50727)"
          [13/Jun/2007:14:46:49 -0600] "POST /pager.php?action=pager&do=readpager& HTTP/1.1" 200 34 "http://www.mywebsite.com/search.php?searchid=12490" "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.0.04506; .NET CLR 1.1.4322)"
          [13/Jun/2007:14:46:49 -0600] "POST /pager.php?action=pager&do=readpager& HTTP/1.1" 200 33 "http://www.mywebsite.com/showthread.php?t=1061&page=7" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)"
          [13/Jun/2007:14:46:51 -0600] "POST /pager.php?action=pager&do=readpager& HTTP/1.1" 200 33 "http://www.mywebsite.com/search.php?searchid=12494" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322; .NET CLR 2.0.50727)"
          [13/Jun/2007:14:46:55 -0600] "GET /photopost/images/overlay-round.gif HTTP/1.1" 304 0 "http://www.mywebsite.com/index.php?" "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.0.04506; .NET CLR 1.1.4322)"
          [13/Jun/2007:14:46:55 -0600] "GET /photopost/data/500/thumbs/DSCN0502.JPG HTTP/1.1" 200 1708 "http://www.mywebsite.com/index.php?" "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.0.04506; .NET CLR 1.1.4322)"
          Code:
          15:52
          [13/Jun/2007:15:52:02 -0600] "POST /pager.php?action=pager&do=readpager& HTTP/1.1" 200 34 "-" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.9) Gecko/20061206 Firefox/1.5.0.9"
          [13/Jun/2007:15:52:07 -0600] "POST /pager.php?action=pager&do=readpager& HTTP/1.1" 200 33 "-" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.9) Gecko/20061206 Firefox/1.5.0.9"
          [13/Jun/2007:15:52:12 -0600] "POST /pager.php?action=pager&do=readpager& HTTP/1.1" 200 33 "-" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.9) Gecko/20061206 Firefox/1.5.0.9"
          [13/Jun/2007:15:52:19 -0600] "POST /pager.php?action=pager&do=readpager& HTTP/1.1" 200 33 "-" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.9) Gecko/20061206 Firefox/1.5.0.9"
          [13/Jun/2007:15:52:24 -0600] "POST /pager.php?action=pager&do=readpager& HTTP/1.1" 200 33 "-" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.9) Gecko/20061206 Firefox/1.5.0.9"
          [13/Jun/2007:15:52:30 -0600] "POST /pager.php?action=pager&do=readpager& HTTP/1.1" 200 34 "-" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.9) Gecko/20061206 Firefox/1.5.0.9"
          [13/Jun/2007:15:52:35 -0600] "POST /pager.php?action=pager&do=readpager& HTTP/1.1" 200 33 "-" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.9) Gecko/20061206 Firefox/1.5.0.9"
          [13/Jun/2007:15:52:41 -0600] "POST /pager.php?action=pager&do=readpager& HTTP/1.1" 200 33 "-" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.9) Gecko/20061206 Firefox/1.5.0.9"
          [13/Jun/2007:15:52:47 -0600] "POST /pager.php?action=pager&do=readpager& HTTP/1.1" 200 34 "-" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.9) Gecko/20061206 Firefox/1.5.0.9"
          [13/Jun/2007:15:52:52 -0600] "GET /calendar.php?do=getinfo&day=2007-6-13&e=108&c=1 HTTP/1.1" 301 5 "http://www.mywebsite.com/forums.php" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.9) Gecko/20061206 Firefox/1.5.0.9"
          [13/Jun/2007:15:52:52 -0600] "POST /pager.php?action=pager&do=readpager& HTTP/1.1" 200 32 "-" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.9) Gecko/20061206 Firefox/1.5.0.9"
          [13/Jun/2007:15:52:54 -0600] "GET /showthread.php?t=1325 HTTP/1.1" 200 18635 "http://www.mywebsite.com/forums.php" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.9) Gecko/20061206 Firefox/1.5.0.9"
          [13/Jun/2007:15:52:54 -0600] "GET /clientscript/vbulletin_textedit.js?v=365 HTTP/1.1" 304 0 "http://www.mywebsite.com/showthread.php?t=1325" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.9) Gecko/20061206 Firefox/1.5.0.9"
          [13/Jun/2007:15:52:55 -0600] "POST /pager.php?action=pager&do=readpager& HTTP/1.1" 200 33 "-" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.9) Gecko/20061206 Firefox/1.5.0.9"

          Comment


          • #6
            Not sure if this will help or not, but this is the info given to me in cPanel:

            General Server Setup
            Operating system: Linux
            Kernel version: 2.6.21-3_1.BHsmp
            Apache version: 1.3.37 (Unix)
            PERL version: 5.8.7
            Path to PERL: /usr/bin/perl
            Path to sendmail: /usr/sbin/sendmail
            PHP version: 4.4.6
            MySQL version: 4.1.21-standard-log
            cPanel Build: 10.9.0-RELEASE 7965
            Theme: BlueHost X v2.6.0
            cPanel: Pro 1.0 (RC36)

            Comment


            • #7
              I read the post Kerry-Anne referenced. Unfortunately, my host is not giving me access to my AdminCP to change those options. They are denying me access until I fix the problem.

              edit: Never mind, thank you so much for making table names and variable names that actually make sense

              I will be contacting my host to ask them to re-enable my site. Hopefully you won't hear from me again
              Last edited by Elenna; Wed 13 Jun '07, 5:05pm.

              Comment

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