Help! - High Database Load: How do you know you know which query is causing it?

Discussion in 'Database' started by gcawood, Nov 3, 2011.

  1. gcawood

    gcawood Administrator Staff Member

    Messages:
    49
    Likes Received:
    19
    Trophy Points:
    0
    I have a MySQL database server and the load really spiked for about 30 minutes or so... I have no clue how or why it happened. Any thoughts on how I can track down the query that was causing the problem?

  2. giggity

    giggity New Member

    Messages:
    6
    Likes Received:
    1
    Trophy Points:
    0
    You can look at processlist to see what's running.. also take a look at slow queries if you have a slow query log going..
    1 person likes this.
  3. gcawood

    gcawood Administrator Staff Member

    Messages:
    49
    Likes Received:
    19
    Trophy Points:
    0
    To expand on Giggity's answer...

    Log into MySql and run the following command.
    Code:
    mysql> SHOW FULL PROCESSLIST\G
    while the server is under load, you will see a listing of queries that are currently running. It is likely that a heavy hitting query will show up there at some point.

    Take a look at the `time` field and see if you see a query is taking a couple of seconds to execute, but not in "sleep" state. If you see something keep popping up while that load is high, then there is a good chance that it will turn out to be your problem query.

    Here is a chunk of code that will show you a listing of active queries that are being run, sorted by the `time` field, along with the `host` and `database` that are in question. Type this at a shell prompt:
    Code:
    # mysql -u root -p -e 'show FULL processlist;' | grep -vi sleep | awk 'BEGIN { FS = "\t" } ; {print $6,$3,$4}' | sort -n
    Hopefully, you should be able to spot the offending query.
    Rob and (deleted user) like this.
  4. gcawood

    gcawood Administrator Staff Member

    Messages:
    49
    Likes Received:
    19
    Trophy Points:
    0
    Also, take a look at the `host` field more carefully and you will see that each one has a unique connection string like ":78983036"

    Assuming that you have access to the server in the `host` field, you should be able to log into it and run
    Code:
    [root@delta ~]# netstat -ntp | grep 78983036
    And find out exactly which Apache process is causing you problems! Once you know what the process is, then you can take whatever action you feel is necessary.

    Good luck!
  5. gcawood

    gcawood Administrator Staff Member

    Messages:
    49
    Likes Received:
    19
    Trophy Points:
    0
    Thanks so much for all the responses. The load is back down to normal now, and I didn't get a chance to run any of the suggested commands before it cleared up on it's own. However, I'll keep them handy for the next time.

Share This Page