Monitoring server performance issues

WebMonkey27

In my new job I have inherited quite a large and complex (an in a few places badly written) PHP & MySQL website. It also handles a lot more traffic than I have been used to in my other jobs.

The problem I have now is chasing down performance bottlenecks and slow queries, and other glitches that cause the site to hang, be unavailable or have weird load average / memory usage spikes on the webservers and the mysql servers

I was wondering what other people use for sniffing out these problems, I was thinking about Zend platform, but that is looking like it might be too expensive (we are mostly advertisement funded, so margins are low despite high traffic)

thanks
Monkey

MarkR

Your first best bet would be to enable slow query logging on your production mysql server(s). This feature is described in the manual.

You can also get Apache to time (in miliseconds I think) how long each request took - this is particularly interesting for small PHP pages which take a long time. This log will of course be quite large, you might need to write your own tools to analyse it.

Other, cleverer options will involve doing some more performance testing in a non-production environment. You will need a data set of the same size as your production data in such an environment - if your organisation permits it and it's possible, I recommend that you take a dump of the production data into your test environment.

Your test environment should obviously consist of production-grade hardware (and NOT be virtualised, e.g. with vmware).

You can then throw requests at it and measure in a production-like environment, how long they take. You can also add instrumentation either in your code, or using something like APD (whcih is a zend extension), neither of which you would be able to do safely in a production environment.

Moreover, a performance test environment allows you to check whether a proposed improvement will actually help, before putting it live. This is pretty much vital.

If you are doing frequent builds and it's an important site, I really recommend that you build some kind of performance regression testing facility - this will not be easy, but it will enable detection of (some) newly introduced performance problems before they reach production.

Finally, you should probably have a procedure for rolling back each software change to your production environment (in case it turns out to be disasterous despite the above testing), and/or a facility to turn off specific features in production.

Mark

WebMonkey27

thanks for the tips 🙂

I have managed to get a slow query log going now, but unfortunately it doesn't say which page the query is running from (which was a long shot with it being a mysql log I suppose)

I have tried doing a search through the source code to find them, but a lot of the queries are dynamically generated, being built with ifs and having table names in variables etc... so do you have any idea how to find them?

cheers 🙂

etully

I agree with MarkR that one of the best things you can do right now is get a copy of the web site running on a non-production box. You can bombard the test box with hits using a program called AB (Apache Benchmark). If you see any new entries in the slow query log when you bombard page X, then you will have a better idea of where they are coming from.

MySQL teaches a great class on optimizing the database - and there is a great book by O'Reilly called High Performance MySQL (they strongly recommend the book in the class).

But don't be too quick to blame MySQL - it's definitely the first place to look but problems can come from other sources too. Having a test box will help you isolate the problems.

By the way, we found that by increasing the query cache size in MySQL, a large percentage of our problems went away. We also found that some of our tables had millions of records (old stuff for reporting purposes) - when we moved very old data to a backup table, the site sped up by 50%. (9 second page loads became 4-5 seconds).

Using Ajax also made many of our pages easier to use - and that put less load on the database which sped up the non-Ajax pages too.

MarkR

etully wrote:
I agree with MarkR that one of the best things you can do right now is get a copy of the web site running on a non-production box.

I didn't mean a development box. Obviously you should have one of those, but for practical reasons, it may not be production spec - for example, you may not even have enough disc space for the production DB, or it might be in (e.g.) vmware or something.

A performance test box needs to be at production spec if at all possible. This is not normally a problem for any organisation with reasonable funding (i.e. they can afford hiring a PHP developer) - unless the production spec is ludicrous.

Depending on your application architecture, you might need multiple boxes. If you need multiple boxes for multiple developers, all production spec, that's where it starts getting expensive.

Mark