[FIXED] Profiling and optimising PHP / MySQL websites

Issue

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

Solution

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.

Answered By - Carsten