I’ve previously talked about tuning Drupal for weblogs here previously. I’d like to elaborate more today, on specifically running Drupal on Dreamhost.

If you are on Dreamhost, you are at one key disadvantage when it comes to using Drupal. First and foremost, you need to be very aware of query performance. Dreamhost hosts their MySQL databases on separate servers than their web servers. This, in itself, is a quite scalable architecture, and I have no honest problem with it. Most enterprise Java applications I have worked on over the years have been configured in this manner. It has a down side, however, in that there is a base cost in communicating with your application to the database, as they exist on different physical hardware, so there is a network hop involved, and a lot of address translation going on. In the end, we’re only talking about a handful of milliseconds, but that needs to happen for every query.

Frankly, Drupal installs can get out of hand with the number of queries they run. This is not a Drupal problem in itself (I’m not trying to incite a Drupal flame-fest here), it is simply a fact of having a technology so modular with so many diverse plug-ins, all running on top of a very thin stack, directly above the database. Coming from the Java world, I’ve gotten comfortable with striving to run only a handful of queries to render any one page. I’ve worked on systems that executed 30+ queries to render a page, but that’s not something I’m necessarily proud of, as most of the time the reason for the multitude queries was simply a lack of coordination (aka N+1 problems!).

Truth be told, that same lack of coordination can haunt Drupal. Case in point, RealJenius.com uses the ‘URL Alias’ functionality in the Drupal core. In Drupal 5, every node-based link on the page to be rendered will go through a function that in turn looks for a URL alias to render as opposed to the raw node URL. This in turn triggers an SQL query for every link. Therefore, if you have a heavy 30 links to nodes on the page, you could have 30 SQL queries issued, just to determine if they have URL aliases.

These queries are super easy for MySQL to run, and the amount of data in the result set is very minimal, but, the network traffic is going to cost you one way or another, and database indexes can’t speed up network latency.

For a largely anonymous website (like RealJenius.com), fixing this slowdown for the majority of users is relatively easy: turn on caching. I’ve written about this previously here.

In the end, though, you still likely need to consider how you can track query performance. There are a couple tools you may be prepared to use, but not so fast. Here’s why:

• MySQL Slow Query Log - Dreamhost doesn’t give it to you, so in the end you can’t use it on your Prod server.
• Drupal Devel Plug-in - Once again, you can’t use it. If you install it on a Dreamhost Drupal install (as of 9/19/07), you will experience a totally corrupt Drupal install, with a very cautious back-out process. Need to understand more? Here’s the quote from the Devel site (that wasn’t there when I tried this back in April):

  Compatibility Note: The Devel module will not work with Zend optimizer 3.2.5 and below. If you have one of those versions of the Zend optimizer and enable query logging it will break your entire site and may end in a blank screen. To fix the problem see this issue. You can also fix it by upgrading to a more modern version of Zend Optimizer. Note that this problem specifically affects customers of DreamHost, HostMonster/BlueHost, and possibly other hosts.

Dreamhost is currently (as of 9/19/07) running 3.2.2 Zend, and despite my vote here, they haven’t shown any motivation to upgrade. If you want to know more, here is the bug describing the problem.

So, what do you do? Well, there are few options as I see it:

1. You can run your Drupal site locally, where you can get to the Devel plug-in as well as the MySQL Slow Query Log. Here’s the potential problems with that, however: your local won’t have anywhere the performance heuristics of your hosting provider.
2. You can hack the Devel plug-in to support these bad versions of Zend. Frankly, the Drupal developers have been very stand-offish about trying to work-around this Zend Optimizer bug, but that doesn’t mean the adventurous can’t try. Of course, this puts you in a weird way because you now have a fork of Devel to maintain.
3. Switch hosts - if you are like me, this is more trouble than it’s probably worth.
4. Petition Dreamhost to Provide MySQL Slow Query and a new Zend - Already done it!
5. Manually modify your shared instance on Dreamhost to have a newer Zend version - I’m considering this one, but haven’t explored it yet simply because of the complexity. All indicators seem to show this is possible.