Using Siege and KCacheGrind to Optimize SolutionPHP’s ORM

Some time ago we posted about how to install XDebug and KCacheGrind.  I think anyone serious about PHP performance should keep those tools handy and in regular use.

And it just so happens they helped us uncover a huge performance bug.

We recently started developing our own MVC framework (SolutionPHP) for use on our company projects.  One of our decisions was to include an ORM implementation.  Even though we’re generally not fans of ORMs, we’ll easily agree that there are times when it makes sense to use them.  Adding the base orm functionality has been pretty straightforward and a nice learning experience.

After our first pass at SolutionPHP’s ORM, we wanted to gauge its performance trade offs.  To do so, we benchmarked 2 branches of a site’s home page.  Both branches produce identical page markup and fetch the same data from the database, the only difference being that one uses the ORM and one does not.

To start, here’s SolutionPHP’s performance baseline of the sites’s home page:

SolutionPHP's performance baseline: 468 requests/sec.

SolutionPHP’s performance baseline: 468 requests/sec.

This is w/out the user of the ORM; we see about 468 requests/sec.  This is very fast for an MVC framework that’s hitting a database!  You can view performance for other frameworks here.  Note those benchmarks are on minimal Hello World sites that don’t touch the database.  We immediately see that SolutionPHP’s performance is going to be one of it’s stand out features.

Next up we switched to the ORM branch and ran a baseline:

SolutionPHP performance baseline, with use of its ORM.

SolutionPHP performance baseline, with use of its ORM.

Yikes, a measly 59 requests/sec!  And just like that, the framework’s performance is kaput.  Let’s take a look at where things go wrong.

Here’s a profile screen shot in KCacheGrind:

KCacheGrind showing SolutionPHP's ORM profile, before optimizations.

KCacheGrind showing SolutionPHP’s ORM profile, before optimizations.

The entries worth noting are boxed in red.  There looks to be 30-31 recursive calls on certain methods.  Sounds like the typical N + 1 query problem ORMs are notorious for, and one of the primary reasons why they cause performance problems.

And brief investigation confirmed that.  Those recursive calls are firing once for each row found in a result set.  Of those methods, the one I was concerned with was list_columns(), the second call from the top.

list_columns() is responsible for reading a table’s columns so that the ORM knows which properties to attach to a model object.  For example, assume a user model based on a user database table that has id, first_name, and last_name columns.  list_columns() would query the database for the user table’s columns and inform the ORM that a user model object should have id, first_name, and last_name properties attached to it.  Its code and database query are simple enough:

public function list_columns($table_name)
{
    return $this->fetch_all('SHOW FULL COLUMNS FROM '.$table_name);
}

Back to why this causes an N + 1 query problem …

Such logic makes sense, at least as explained above – models knowing which attributes it needs to be aware of is a good thing (although there are other ways to accomplish the same thing, such as hard coding and maintaining a list of attributes directly in each model, perhaps via a private or protected array property).  But! … taking a step back, it makes sense that a model shouldn’t have to be reinformed of its attributes multiple times for a given result; certainly not once per row, especially if doing so requires a database query each time!  It should only happen once.

And the fix was simple enough.  Instead of calling list_columns() N times, we made two revisions:

  1. Models now call list_columns() in their constructor, then cache the result in a local member variable, and
  2. The ORM base class now references that member variable when mapping database columns to model attributes – no more calling list_columns() multiple times for this!

The difference in performance is rather incredible.  Running the same benchmark now brings us up to 380 requests/sec!

Requests/sec after the ORM optimization.

Requests/sec after the ORM optimization.

Conclusion

Making simple performance mistakes is easy, especially when you’re working with something like an ORM that interfaces with a database.  However, tools like XDebug and KCacheGrind can provide visibility into those problems in a way that makes them readily apparent and therefore easier to address.

This entry was posted in Database, General, Performance, PHP. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *