6

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.

msanford
  • 1,477
  • 15
  • 28
Diego
  • 163
  • 5
  • Key questions before offering any diagnosis/recommendations: What level of traffic are you talking about here? Is it anything with significantly large traffic spikes e.g. triggered by sporting events etc.? What sort of content is there - is there a lot of small static objects like graphics, or are there large objects like audio/video clips - or both? How much of the content is dynamically generated by PHP/MySQL? –  Jun 14 '12 at 17:28
  • What - if anything - is shown in the server event logs (eventvwr is your friend on windows). Response times from the webserver would help, you don't mention Apache, so is it being served by IIS? It's not necessarily the DB server, this is a frequent misdiagnosis on high traffic systems. Usually the web server itself is overloaded delivering content it's not optimised for. This is where reverse proxies come in. –  Jun 14 '12 at 17:28
  • I just answered for below question. it may useful. http://stackoverflow.com/questions/9954926/configuring-mysql-on-my-windows-server-2003 – Senthil Oct 02 '12 at 01:03

3 Answers3

3

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 :-)

JMeterX
  • 3,387
  • 16
  • 31
2

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.

jeshurun
  • 254
  • 1
  • 2
  • 7
  • 1
    I would also take a minute and run SHOW PROCESSLIST against your database when the app becomes unresponsive. This will quickly show you if any tables are locked or if any queries have been running for a lengthy period of time. –  Jun 12 '12 at 17:17
  • Thanks! I will try to follow your instructions. In the meanwhile please read the note I added to the question. – Diego Jun 12 '12 at 17:35
1

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.