SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,027
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    How can one see which MySQL command(s) is causing the server load avg to go crazy

    Hello,

    I have a very important/critical question.

    What is the best method for seeing via remote control, that is like Putty, what MySQL command is misfiring and causing server load average to go crazy?

    I mean if there is a MySQL command that is written bad which is causing the load average of server spike up to crazy levels, how can one find out via the processors running which one can see via Putty as the Root as to which MySQL command(s) are causing the problem?

    Best Regards,

    Anoox search engine volunteer

    www.anoox.com

  2. #2
    SitePoint Wizard chris_fuel's Avatar
    Join Date
    May 2006
    Location
    Ventura, CA
    Posts
    2,751
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can use mysqladmin like so:

    Code:
    mysqladmin -u root -h localhost -p processlist
    This will give you a list of processes, where you can see how long they've been running and what the status is. Here's an example output:

    Code:
    +------+------+-----------+----+---------+------+-------+------------------+
    | Id   | User | Host      | db | Command | Time | State | Info             |
    +------+------+-----------+----+---------+------+-------+------------------+
    | 3917 | root | localhost |    | Query   | 0    |       | show processlist |
    +------+------+-----------+----+---------+------+-------+------------------+
    Then you can kill the process that's causing total disaster:

    Code:
    mysqladmin -u root -h localhost -p kill 3917

  3. #3
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,027
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Greetings,

    I entered the command as you suggested and see the table, but how
    do I know which process is causing the problem?
    I mean what value(s) should I be looking for as being cause of the problem?

    Also, this table is wrapping around, making it hard to read. Do you have
    a suggestion to make the table to fit in the window without wrapping?

    Regards,
    Last edited by longneck; Apr 15, 2008 at 12:38. Reason: please don't needlessly quote previous posts

    Anoox search engine volunteer

    www.anoox.com

  4. #4
    SitePoint Wizard chris_fuel's Avatar
    Join Date
    May 2006
    Location
    Ventura, CA
    Posts
    2,751
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Check the time and State columns. If the time is ridiculously long and the State is "Sending Data", you've got a really slow query on your hands. See alternate values for the State column:

    http://dev.mysql.com/doc/refman/5.0/...ad-states.html

    Note that if you login to mysql you can also run the SHOW PROCESSLIST (not sure of permissions around this, but I'd say root at least), which you can add \G at the end of it to drop column => value entries to a single line. From there, you simply apply the KILL command to the thread in question. Also note that Time is measured in seconds.

  5. #5
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,027
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Greetings Chris,

    1st, Thanx for info you have sent to date.

    So let me ask you some follow up questions:
    1- What value for the Time column would indicate that that thread is running bad or has a bug in it?

    2- Similarly what are some warning "State" column values to look for?

    3- How can I best map a badly/buggy running thread to the MySQL code (lines of codes) in the program that is causing this problem?

    4- And should I notice a badly/buggy running thread, how do I best Kill just that thread?

    Best Regards,
    Last edited by longneck; Apr 15, 2008 at 12:40. Reason: please don't needlessly quote previous posts

    Anoox search engine volunteer

    www.anoox.com

  6. #6
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,027
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hello,

    I was wondering if you got my follow up questions to your reply.

    I would appreciate your reply ASAP since I need to address critical
    problems being caused by some buggy mySQL code.

    Regards,
    Last edited by longneck; Apr 15, 2008 at 12:40. Reason: please don't needlessly quote previous posts

    Anoox search engine volunteer

    www.anoox.com

  7. #7
    secure webapps for all Aleksejs's Avatar
    Join Date
    Apr 2008
    Location
    Riga, Latvia
    Posts
    755
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try enabling slow-query-log

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by WorldNews View Post
    1- What value for the Time column would indicate that that thread is running bad or has a bug in it?
    anything more than a few seconds.
    Quote Originally Posted by WorldNews View Post
    2- Similarly what are some warning "State" column values to look for?
    generally, the ones you will see are either "sending data" or "copying...", and either of those can mean that your query is either returning or processing large amounts of data, possibly inefficiently.
    Quote Originally Posted by WorldNews View Post
    3- How can I best map a badly/buggy running thread to the MySQL code (lines of codes) in the program that is causing this problem?
    the simplest way is to add comments to all of your SQL queries so you know where the queries came from. that's not so simple if you're not using a DB abstraction layer. alternatively, if you KILL a query then an error should be logged in your webserver or application log and you can track down the line of code that way.
    Quote Originally Posted by WorldNews View Post
    4- And should I notice a badly/buggy running thread, how do I best Kill just that thread?
    with the KILL command.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  9. #9
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,027
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks for this detailed reply.

    Some more related questions:

    1- What is the best Kill command for killing a MySQL thread?
    just "Kill thread-id"?
    Or you suggest something else.

    2- Is it best to issue such command while loged in as MySQL root or server root?

    Regards,
    Last edited by longneck; Apr 15, 2008 at 12:41. Reason: please don't needlessly quote previous posts

    Anoox search engine volunteer

    www.anoox.com

  10. #10
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    please be more judicious in your use of the quote button. quoting the entire message you're replying to makes the thread difficult to read. i've edited your posts to remove the unneeded quote boxes.
    Quote Originally Posted by WorldNews View Post
    1- What is the best Kill command for killing a MySQL thread? just "Kill thread-id"?
    that's the only command for killing a thread.
    Quote Originally Posted by WorldNews View Post
    2- Is it best to issue such command while loged in as MySQL root or server root?
    root or not does not matter. whoever issues the command must have the PROCESS or SUPER privilege.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  11. #11
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,027
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    please be more judicious in your use of the quote button. quoting the entire message you're replying to makes the thread difficult to read. i've edited your posts to remove the unneeded quote boxes.

    Yes, I will try to be next time.

    that's the only command for killing a thread.

    Ok.

    root or not does not matter. whoever issues the command must have the PROCESS or SUPER privilege.
    Ok, so user needs to be Root.

    One related question:

    what is the command for continuously seeing what Process (MySQL commands) are executing?

    I mean "Show processlist" shows one static page of the most recent set of processlist. Is there a command that shows a continuous updating list of the processlists? Something like "Top", but one that shows what MySQL commands or like commands are executing?

    I am hope you know what I mean.

    Regards,

    Anoox search engine volunteer

    www.anoox.com

  12. #12
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    there is none.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  13. #13
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,027
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hum!
    That is a real shame.
    Such as software, info, is really needed.
    I am surprised no one has taken the initiative to develop such a
    software, specially given all the open source software that is
    available today.

    Maybe we should


    Quote Originally Posted by longneck View Post
    there is none.

    Anoox search engine volunteer

    www.anoox.com

  14. #14
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    there is a third-party program call mysql top that you might be interested in.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  15. #15
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,027
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    What is the URL to where I can get this?

    Regards,

    Quote Originally Posted by longneck View Post
    there is a third-party program call mysql top that you might be interested in.

    Anoox search engine volunteer

    www.anoox.com

  16. #16
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  17. #17
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,027
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Thanks for this info.
    But this is a software that runs on the server side!
    Is there a software that runs on client side, I mean on the desktop like
    Putty, which allows us to keep an eye on the MySQL lines executing like
    Top allows us to see all processes running on the server.

    Regards,

    Quote Originally Posted by longneck View Post

    Anoox search engine volunteer

    www.anoox.com


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •