Whats a good way about troubleshooting a script in terms of performance (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

mytop is a good place to start. It's basically top for MySQL, and will give you a window into what exactly your DB is doing:

http://jeremy.zawodny.com/mysql/mytop/

Noah

Answer 2

It could be any number of reasons, so it could take a lot of proding. A good first step would be to turn on the slow query log, and go over it by hand or with a parser. You can pick specific heavily used, slow queries to optimize (perhaps ones that hit something unindexed)

Answer 3

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.

Answer 4

Use EXPLAIN to help you optimize/troubleshoot your queries. It will show you how tables are referenced and how many rows are being read. It's very useful.

Also if you've made any modifications to your MySQL configuration, you may want to revisit that.