Home / server / Questions / 316922
In Process
chubbyk
Asked: 2011-09-30 09:18:32 +0800 CST

How to detect the process (and mysql query) that makes high load on server?

  • 772

From time to time load on my server (centos 4)jumps to 15 or more. I need to discover what process causes this high load on server. It there any script which can handle this for me ? I think its caused by mysql so even to discover the sql query would be great help.

mysql high-load

3 Answers

  1. High server load typically caused by disk I/O. There are some ways to identify which process is waiting for disk I/O:

    • iotop
    • top -b -i -n 10
    • atop and press D
    • htop -> F2 to add 2 columns IO_RBYTES and IO_WBYTES, then F6 to sort
    • ...

    You should especially pay attention to the processes in D state (uninterruptible sleep):

    watch "ps -eo pid,user,state,command | awk '\$3 == /D/ { print \$0 }'"
    • 6 
  2. SHOW FULL PROCESSLIST\G

    look for something with a long runtime.

    then EXPLAIN to see if it's making full table scans, generating temp tables, etc.

    Otherwise, you can use something like the free version of Jet ( http://www.jetprofiler.com/ )

    Garrett

    • 2

  3. If it is the case that mysql is the cause of the high load, then Mysql supports a slow_query_log argument, and once enabled it will log any particularly slow queries to a file;
    http://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

    The slow query log consists of SQL statements that took more than long_query_time seconds to execute and required at least min_examined_row_limit rows to be examined. The minimum and default values of long_query_time are 0 and 10, respectively.

    • 0