什么是关于在性能方面对脚本进行故障排除的好方法（php / mysql）？

Question

I've written a site CMS from scratch, and now that the site is slowly starting to get traffic (30-40k/day) Im seeing the server load a lot higher than it should be. It hovers around 6-9 all the time, on a quad core machine with 8gb of ram. I've written scripts that performed beautifully on 400-500k/day sites, so I'd like to think Im not totally incompetent.

I've reduce numbers of queries that are done on every page by nearly 60% by combining queries, eliminating some mysql calls completely, and replacing some sections of the site with static TXT files that are updated with php when necessary. All these changes affected the page execution time (index loads in 0.3s, instead of 1.7 as before).

There is virtually no IOwait, and the mysql DB is just 30mb. The site runs lighttpd, php 5.2.9, mysql 5.0.77

What can I do to get to the bottom of what exactly is causing the high load? I really wanna localize the problem, since "top" just tells me its mysql, which hovers between 50-95% CPU usage at all times.

Answer 1

The best thing you can do is to profile your application code. Find out which calls are consuming so much of your resources. Here are some options (the first three Google hits for "php profiler"):

• Xdebug
• NuSphere PhpED
• DBG

You might have some SQL queries that are very slow, but if they are run infrequently, they probably aren't a major cause of your performance problems. It may be that you have SQL queries that are more speedy, but they are run so often that their net impact to performance is greater. Profiling the application will help identify these.

The most general-purpose advice for improving application performance with respect to database usage is to identify data that changes infrequently, and put that data in a cache for speedier retrieval. It's up to you to identify what data would benefit from this the most, since it's very dependent on your application usage patterns.

As far as technology for caching, APC and memcached are options with good support in PHP.

You can also read through the MySQL optimization chapter carefully to identify any improvements that are relevant to your application.

Other good resources are MySQL Performance Blog, and the book "High Performance MySQL." If you're serious about running a MySQL-based website, you should be consulting these resources frequently.