My linux server websites keep heading down over and over but SSH, FTP, etc are alive. And So I had phone server through SSH and used top command which lists all of the processes. It implies that when some PHP pages are performed, mysql CPU usage reaches 100%. Same with there any command/log which may be used to discover which PHP pages take up a lot of mysql usage? Thanks...
You might want to have a look at the Apache log format to ascertain if it offers the %D parameter because this signifies how long come to for everyone a request in microseconds.
Should you exclude not demands to PHP scripts, you need to get a concept of which scripts take a long recommending high execution time. Clearly this might also mean a really large response payload...
You will find multiple aspects to resource consumption.
As mobius pointed out, you should use SHOW FULL PROCESSLIST in MySQL to determine what's presently running. Consider the processed taking more than you realized and look for the query to locate hints about where it comes from the application.
The issue might not be using the application. It could just be dependent on tuning MySQL, which is about adding or altering indexes more often than not. EXPLAIN may be the command which will you help evaluate the execution plan MySQL made the decision to make use of. Reading through EXPLAIN takes some practice. The very best reference I've is High Performance MySQL.
You may also make use of the MySQL slow query log to obtain details about the slow queries happening when you're not while watching server.
If MySQL is running at 100%, you'll most likely discover the problem after that. If you want to trace the usage from PHP, you are able to setup XHProf, a higher performance profiler produced by Facebook to operate on production sites. You are able to arrange it to sample one request from 100 and obtain a problem from the performance of the site. You will find a couple of articles available that let you know that to put it together.
Finally, XDebug and KCacheGrind may be used in development to profile one request at any given time.
If MySQL gets stuck at 100% then you need most likely got a bit of badly updated MySQL queries inside your PHP programs. This time around will clock up within the MySQL daemon and thus will not display in the %D value. This may be indexes outdated.
If you can get the D/B through in the command prompt through SSH then you may try doing an Evaluate TABLE and OPTIMIZE TABLE on any large tables. Also take a look at "The Slow Query Log" within the MySQL documentation.
Regrettably fixing this can most likely need enter into the applying internals.