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

Question

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.

Solution 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 }'"

Solution 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

Comments

[slillibri]

Another tool to look at is Innotop (code.google.com/p/innotop) a top clone for MySQL. Also check out the Percona Toolkit (percona.com/software/percona-toolkit)