CODEWITHSUNDEEP
phpmysqloptimizationquery-optimizationlamp
Chris
Chris

Reputation: 27384

Profiling and optimising PHP / MySQL websites

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

Upvotes: 2

Views: 5718

Answers (4)

Zul
Zul

Reputation: 3608

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:

enter image description here

Upvotes: 10

moteutsch
moteutsch

Reputation: 3831

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.

Upvotes: 1

Carsten
Carsten

Reputation: 18446

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.

Upvotes: 3

Your Common Sense
Your Common Sense

Reputation: 157828

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.

Upvotes: 4

Related Questions