Best tools for PHP, MySQL and Apache optimization

SpeedProfs covers the best tools to help you making websites/applications faster. In this section we cover tools that can help in the optimization of PHP, MySQL and Apache.

PHP

Measuring the performance of PHP comes down to one thing, is your code as fast as it can be? Of course other things then sprout from this; where is your code taking most if its time, how much memory its using, which sections of code are dead, among others. Some PHP developers will write their own classes for measuring time taken to execute, many more such classes are available on the net. Some of the popular frameworks such as CodeIgniter, Zend Framework, Kohana and the others came with inbuilt profiler classes. PHP itself has its own core functions for measuring how it performs.

• Xdebug
  As one of the most popular PHP profiling tools, Xdebug has a lot of powerful features. Working as a Zend engine extension allows it to work transparently.
  Activating the profiling log collects a wealth of information on each and every function and method call. You can then use a log analysis software such as Kcachegrind, Valgrind or WinCacheGrind to drill down to the slow function, allowing you to optimize them. For a more detailed introduction to Xdebug, see the Xdebug series of articles on Zend Developer Zone.xDebug wins the Editors Choice award because it will profile your code from the PHP engine itself, thereby giving you a whole lot more information than you can probably get from your own classes.
• DBG
  DBG differs from Benchmark and APD in that you do not need to modify your code at all, i.e. it works transparently. With its internal profiler, it can identify parts of code which take longer than defined time to execute, thus allow you to quickly identify bottlenecks. Of course, it has a whole lot more features both in the free and commercial versions, such as just-in-time debugging, step-by-step debugging as well as breakpoints, among other features.
• Benchmark
  This PEAR package provides the capability to measure the performance of PHP scripts and functions. By allowing the user to set markers in the code, it can then measure the performance between various markers or between the start timer and make it relative to the performance of the entire page.
  Essentially then, you can measure specific bits of your code.
• Advanced PHP Debugger
  As stated by Linuxjournal.com, “…APD is a debugger written in C by George Schlossnagle and Daniel Cowgill that loads as an extension to the Zend Engine. It works by hooking into the Zend internals and intercepting PHP function calls, allowing it to do things like measure function execution time, count function calls, perform stack backtraces and other funky things.”
• PHP Quick Profiler
  By including a small PHP file, this relatively new profiler allows you to measure execution time, analyze queries, show memory usage and included files.

MySQL

Profiling MySQL is often described as a dark art simply because what works in one situation can wreck things in another situation. Infact, the best approach to profiling MySQL is to learn the basics, then apply them sparingly to your database while at the same time, watching it like a hawk, that way, you learn what works for you and what doesn’t.

• Explain & Explain Extended
  MySQL comes with a set of powerful profiling tools. Suppose you have the query below (from the world database http://dev.mysql.com/doc/index-other.html):
  mysql> select * from city where population>200000 order by name;You can profile it by running it thus:
  mysql> explain select * from city where population>200000 order by name;

  Appending a query with the explain keyword displays a wealth of information on its estimated execution plan. It will show the number of rows accessed, the indices used, the order in which joins are performed, and more.

  To get even more data on query performance, append the keywords explain extended.

• Show Profiles
  Another of MySQL’s inbuilt profiling tools, it works by measuring the time, memory and CPU usage of all queries run after its activated to the time its stopped.In a nutshell, you run the MySQL command below to start the profiler:
  mysql> SET profiling=1;

  Then you run all the queries you want, i.e. Use your application, then when done, you run the command below:
  mysql> SHOW PROFILES;

  That will yield a numbered list of all the queries you ran and their duration. You can then drill down to a particular query by running the command below:
  mysql> SHOW PROFILE FOR QUERY 1;

  That will display the time duration for all internal processes invoked by that particular query.

• Mk-query-digest
  Part of the Maatkit (http://www.maatkit.org/) collection of MySQL tools, this small and free open source program analyzes the MySQL slow query log to in a way which is easy to pinpoint queries which take longer than the predefined time to run.
  It is pretty useful as a way to measure whether optimizations done the in the queries actually worked.
• MySQL Query Analyzer
  Part of the commercial MySQL Enterprise Monitor, this tool provides a graphical way of monitoring the queries being executed by your MySQL server.We were a bit reluctant to award this tool the Editors Choice award because it is commercial and may not be immediately available to you, but after looking at the fundamentals of why a tool wins this award, the decision was unanimous. The problem is that EXPLAIN and EXPLAIN EXTENDED natively work on SELECT queries. The other problem is that SHOW PROFILES requires you to run the queries under analysis so as to gather data for performance analysis.

  MySQL Query Analyzer sort of combines the goal of these two; it will analyze your queries as you run them, combine that with information from the MySQL log files, then apply best practice knowledge to recommend to you specific actions you can take to make them faster. It does all this in one feature packed interface which even allows you to visualize performance stats in nice graphs, allowing you to see where the problems are.

  Go grab yourself a trial copy and tell us if it did or did not make your life easier.

• mysqlap
  Sometimes you want to measure how many users your database can service without breaking a sweat. In such a case, one of the easiest tools to use is mysqlap. This tools emulates normal or pre-defined database activity for as many clients as you tell it to.
  Not only will it generate the database activity, it will also time it so that you can tell how fast or slow your database is for a given number of users, and then use this information to tweak the server variables accordingly.

Apache

• Apache2::Profiler 
  This is a Perl module which integrates with Apache to measure the time taken to service each request received. Using this tool, you can then identify whether the bottleneck in your application is because of PHP, MySQL or Apache, and thereby focus your attention where it will have the biggest impact.
• Custom Access Log Format
  Apache allows for the customization of its access log by using predefined formats. Of particular interest is the %T string which records the time taken to serve each request in seconds. %D records the time in microseconds. Using the string %B records the size, in bytes, of the data sent by Apache for each request.This type of log format is typically called Blackbox, as detailed by Chris Josephes in his article on profiling web based applications.