PHP + MYSQL site perfomance

I have to manage a site which wasn't developed by me. It is in PHP using a mysql database, which is located in the web server. The site, sometimes (when the visitors increase too much) stops responding, or respond too slow.

I have developed some sites in PHP but never took care of the management so really don't know where to start. The server (the hard) seems to be fine, when the web stops responding the cpu is being used at about 55% and has a lot of memory.

I'm not asking someone to solve this issue. I only would really like if someone could give me a few tips about where can I find logs and how should I read and interpret them. So, that way I would be able to know if its the net traffic, the database (which queries), or what.

Thanks!

Update: Forgot to say: it is a Windows Server 2003.

Note: I've recorded about a day with Jet Profiler. I don't really understand all the information it provides but there is one query which it marks as really slow. It makes sense because it is a select with a where clause which has three like condition. Initially I didn't include this in my question because when I run the query from MySQL Query Browser it doesn't take any long. It is under 0.01 seconds.

Solution 1:

It is usually the database that slows down a site. More than likely you have a handful of queries that are poorly written / not indexed and are what is causing the slowdown. In MySQL, you can find the queries that are taking too long to respond by turning on the Slow Query Log.

Usually this involves the following steps: 1. Create a file named something like slowqueries.log in the MySQL log folder, usually /var/log/mysql/. Change its permissions to the user mysql using chmod (assuming you are on Linux). 2. Log into my sql as root and issue the following queries: set GLOBAL slow_query_log_file='/var/log/mysql/slowqueries.log'; -- sets the log file SET GLOBAL slow_query_log=1; -- turns on slow query logging

Once you identify the queries that are taking too long to execute, you can use EXPLAIN or EXPLAIN EXTENDED to further analyze them and see how they can be optimized, usually by adding indexes / improving joins.

Solution 2:

I had similar problems with a website I was using written in PHP with a separate server running mysql. I was able to use Jmeter and a handy plugin that will give you a graph based breakdown of your servers health: code.google.com/p/jmeter-plugins/wiki/PerfMon#Installation

This can really help you diagnose the issue as you will be able to simulate a different number of users. On a side note, I was able to use APC plugin for PHP which optimized the code. Just a thought :-)

Solution 3:

Check the MySQL queries, if you have access to the panel in your server see what's going on. Non optimized sql queries can bring your server down (happened to me). You should start by that, there must be something that is not optimized enough and slowing it down, that or your server is not working well.