Purchase Linux CDs / DVDs / Flash Drives at OSDisc.com

Welcome to Our Community

While Linux.org has been around for a while, we recently changed management and had to purge most of the content (including users). If you signed up before April 23rd, 2017 please sign up again. Thanks!

  1. More ways to get the info! - we shoot all of our new original content out as well as random messages on Twitter and our newsletter!. Twitter | Newsletter
    Dismiss Notice

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

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

  1. Gerad

    Gerad Guest

    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?

    (Log in to hide this advertisement)

  2. giggity

    giggity Guest

    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 Guest

    To expand on Giggity's answer...

    Log into MySql and run the following command.
    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:
    # 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.
    2 people like this.
  4. gcawood

    gcawood Guest

    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
    [[email protected] ~]# 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. Gerad

    Gerad Guest

    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