分析和优化PHP / MySQL网站

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

Yes, there is a way! MySQL has a built-in feature for this. You can set up a log file to log slow queries.

Other general advice would of course be to use EXPLAIN on common queries and check if everything is indexed properly.

Start from the firebug's Net tab and see what resources have slowest response.

But I can tell you even without any profiling that

serving ... images from MySQL databases

being at least one of the reasons.

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:

XDebug has a profiling tool that gives you a lot of information about the bottlenecks in your code. Check out http://xdebug.org/docs/profiler.