Speeding up Drupal 5.x on Dreamhost, slow no more!

I'm sure you all love Drupal however there are a couple of design decisions that are less than ideal in all circumstances.

One of them is the path alias handling, you know - the stuff that lets you have a page accessible via mysite.com/fruit/bananas instead of mysite.com/node/1234

If you run a Drupal instance, where your mysql server is not on the same machine as your Drupal code, then you should read below.. that mean's you dreamhost people, and could help to alleviate some issues with Drupal being slow on dreamhost.

See, there are two things the pathalias module performs that makes all this happen, and subsequently adds possibly hundreds of extra mysql calls.

One - when you load a page like mysite.com/fruit/bananas Drupal must check the DB to find the real path for this, such as /node/1234 this is done with a single mysql lookup.

Two - when that page loads, it may contain 10 links in the content and a bunch of blocks around it like tag cloud, comments, etc, for each link, generally Drupal will also try to find if it should present a real world path like fruit/apples instead of node/1235

Now, *performing* a MySQL call is very slow, there are a LOT of overheads involved with making the actual call, this is where we attack Drupal's slowness on Dreamhost.

To prove my point about Drupal slowlness on Dreamhost, we'll use one of my favourite sites metal tshirt gallery

With my patch the devel module says

Executed 78 queries in 172.41 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted. Page execution time was 1323.73 ms.

Without it says..

Executed 302 queries in 1585.87 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted. Page execution time was 1996.97 ms.

yes that's right, 224 calls no wonder Drupal is slow on dreamhost

This patch simply downloads all of the path alias table to cache when Drupal run's instead of hitting the mysql everytime, right now Dreamhost server seems to be running pretty quick, but sometimes depending on a bunch of factors it can come to a screaming holt.

Now my sites arent huge, just a few hundred to thousands of url aliases, obviously this patch wouldnt be very good for sites with 10,000+ nodes..

A simple test..

Some raw PHP/Mysql code to get all the results in one hit, and then again 224 (from above) is attached to this article.

You should definitely run this script several times to get an average reading..
Also note - You will need to decide for yourself your break-even point at which it is better to start retrieving each alias individually again, i expect this to be about the 30,000 mark

$ cat cache-test-php.txt |php
Retrieving all 1326 records took 0.018508 seconds
Retrieving 224 records individually took 0.492533 seconds

(cache-test-php.txt is attached to this article, see below)

there you have it, shave 0.47 seconds (half a second) off your Drupal load times for small to medium size sites on Dreamhost to stop Drupal from being so damn slow on Dreamhost. Also probably saves the network admins at dreamhost a bit of stress too :)

Some simple maths to figure out when you should stop using this patch..

>>> (0.492533 / 0.018508) * 1326
35287.37616165982

if you have 35000 aliases, please don't use this patch :)

How to apply the patch

SSH into your dreamhost account, then 'cd' to your Drupal install and run the lynx + patch command line to install the patch from my site.

myuser@dreamhost:~$ cd drupal-5
myuser@dreamhost:~/drupal-5$   
myuser@dreamhost:~/drupal-5$ lynx -source "http://dgtlmoon.com/pathfullcache.patch.txt"|patch -p1
patching file includes/cache.inc
patching file includes/path.inc

have fun!