Mysql – Determine php script causing MySQL huge load

centos6high-loadMySQLPHP

I'm running a young non-profit organization aiming at providing free and low cost web hosting.

Since few days, our server load is around 1.5, sometimes raising to 7. top shows me that MySQL is the bad boy:

PID  USER   PRI  NI  VIRT   RES   SHR  S CPU%  MEM%  TIME+   Command
26362 mysql 20   0   2757M  762M  5872 S 16.0  7.7   3h54:51 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/shadow.err --open-files-limit=50000 --pid-file=/var/lib/mysql/shadow.pid

Do you have an idea of how can I determine the script running the query ?
Thank you for your help.

Best Answer

As mysql root user, try:

 mysql> show processlist;

That will allow you to identify the query causing the load and enable you to trace it back to php script you're looking for.

Note: If the CPU usage of the mysql-process is low (as shown in the top output) but you're experiencing a >1 load it's likely that mysql is causing too much IO.

Related Topic