Profiling and optimising PHP / MySQL websites

Question

I have a server (VPS) that hosts numerous PHP / MySQL websites. Most are quite similar in that they are all hand-coded websites serving text and images from MySQL databases.

Server traffic has increased a fair amount recently and the server is experiencing some slow down. As such I want to try and identify bottle necks in the server so that I can improve the server's speed.

Does anyone have any tips on how to do this? I have setup timing scripts on some of my larger sites to see how long it takes for the webpages to be created but its always a really low figure. According to the server stats the main issue seems to be CPU / MySQL usage. Is there anyway to identify queries that are taking a long time?

Thanks Chris

Answer 1

If youre using mysql >= 5.1, you can use mysql_query("set profiling=1"); in your script, like this:

mysql_query("set profiling_history_size=100");
mysql_query("set profiling=1");

....
....
any mysql query
....
....

$rs = mysql_query("show profiles");
while($rd = mysql_fetch_object($rs))
{
    echo $rd->Query_ID.' - '.round($rd->Duration,4) * 1000 .' ms - '.$rd->Query.'<br />';
}

Example output: