@madpilot makes

If you are a DBA, and your reading this – look away now, because I’m pretty sure they covered this in Database Optimisation 101 and you WILL laugh at me having this revelation. 88 Miles hasn’t been the snappiest web application around lately thanks mainly to an influx of users (NOT that I’m complaining :P). I’d successfully added some views to speed up some of the reporting recently, and I went through today and optimised a lot of code, but it still wasn’t as quick as I would have liked it (A page load in the main index page was taking on average 1.5 seconds – down from the 4 seconds pre-optimisations).

I was googling the performance differences between INNER and LEFT joins (INNER wins most of the time for those of you playing at home), and came across a word that I vaguley remembered between dozing off in my Database class at university – indexes. Now, don’t get me wrong, I KNEW these things existed, I even knew what they did, but because I don’t use them regularly, I didn’t even think to look at them. As all of the primary IDswere already primary keys, my gaze turned to the foreign keys (I use the term relatively loosely – they were foreign keys in the sense that they referred to another table ID, not because they had been explicity setup that way).

I added indexes to the foreign keys on the three main tables, and voila! A ~10x speed increase on that front page! It’s such a simple optimisation too! *Slaps head*

I’ve never been particularly happy with proxying Mongrel processes behind Apache – for one if makes it really hard to scale without using something like God (which adds yet ANOTHER process your website is dependent on) and having separate services means multiple points of failure.

PHP has had mod_php which makes PHP a first class citizen in Apache land, and with the release of Passenger (aka mod_rails) a couple of months ago Rails can now get the same privileges. As most of my production Rails apps (both for me and my clients) run on Joyent, here is a quick recipe for setting up passenger on the newer pkg-src accelerators. You need to be root to do a lot of this, so it might be easiest to

su

before you start.

1. Getting passenger. DON’T use the gem, as it won’t work on Solaris – you need to pull it from git (I think it makes sense to put it in /usr/local):

cd /usr/local
git clone git://github.com/FooBarWidget/passenger.git

2. Run the apache module installer:

cd passenger/
bin/passenger-install-apache2-module

3. Create a configuration file for apache:

cd /opt/local/etc/httpd
echo "LoadModule passenger_module /usr/local/passenger/ext/apache2/mod_passenger.so
PassengerRoot /usr/local/passenger
PassengerRuby /opt/local/bin/ruby18" > includes/passenger.conf

4. Add the following line to /opt/local/etc/httpd/httpd.conf:

Include etc/httpd/includes/passenger.conf

5. Make Apache a little less strict on what it can run. Open /opt/local/etc/httpd/includes/directory.conf and change the Directory directive to (Apache security geeks will probably lynch me at this point – please suggest a more secure setup):

<directory>
Options +FollowSymLinks -SymLinksIfOwnerMatch +MultiViews -Indexes -ExecCGI
AllowOverride ALL
Order allow,deny
Allow from all
</directory>

6. Restart Apache:

svcadm restart /network/http:apache

At this point Apache will be mod_rails enabled. Now to update your application. There is two ways to do this: manually or via the Joyent capistrano receipe. I’ll outline the latter, and you should be able to work out the former for these instructions (Hint: the virtual host descriptions are in /opt/local/etc/httpd/virtualhosts/). Edit config/accelerator/apache_vhosts.erb and find the VirtualHost directive and make it look something like this:

<VirtualHost <%= public_ip %>:80>
  ServerName <%= server_name %>
  RailsBaseURI /
  DocumentRoot <%= public_path %>
</VirtualHost>

Then run

cap accelerator:create_vhost

followed by

cap accelerator:restart_apache

If all went well, your site should be now running via passenger! If all is well, we should remove the mongrel service and update our capistrano receipe, as the restart options are now different. To remove the the mongrel config, run cap accelerator:smf_delete. Finally open config/deploy.rb and remove the start and stop deploy tasks, and replace the restart task with:

deploy.task :restart do
  run "touch #{current_path}/tmp/restart.txt"
end

That should be it. Next time: How to do it on the older Joyent accelerators.

Whilst not being one to make gross generalisations (heh!) I like to think there are two schools thoughts on databases – those that use “extended” features such as triggers, views and temporary tables, and those that don’t. I, for one fall firmly in the latter – usually.

However over the past few days a major project that I’ve been working on brought forth a requirement for some hardcore reporting. Due to the database structure that was required (there was a lot of dynamic fields and association tables) doing it in ActiveRecord was near impossible – in fact, doing it in native SQL was equally painful.

Quite jokingly, MySQL views were suggested, but then in a cold flash back to my days working in Government environments, reminded me that DBAs used views for this stuff all the time, so time to investigate.

The idea is pretty simple: a view is a virtual table that is based on a SQL query that you can run queries against, which means you can easily flatten associated tables and turn complex search queries into simple ones. Example…

Say you have a structure that looks something like this:

(Excuse the diagram – My windows laptop is in the other room and the graphics editors in Linux are balls)

So as you could imagine, you may have companies that have many projects which in turn have many shifts. Who would you calculate all the shifts from a particular company? You would probably end up with something like this:

SELECT shifts.id, shifts.start, shifts.end FROM shifts INNER JOIN projects ON shifts.project_id = projects.id INNER JOIN companies ON projects.company_id = company.id WHERE company_id = 4

Which, whilst fairly simple, is a pain to write – and it can only get worse if the data model becomes more complex. This is where views make life so much easier. By creating a view using a simple query:

CREATE OR REPLACE VIEW shift_reports AS  SELECT company.id AS company_id, company.name AS company_name, project.id AS project_id, projects.name AS project_name, shifts.id AS shift_id, shifts.start AS shift_start, shifts.end AS shift_end FROM shifts INNER JOIN projects ON shifts.project_id = projects.id INNER JOIN companies ON projects.company_id = company.id</p>

we now have a virtual table called shift_reports with columns: company_id, company_name, project_id, project_name, shift_id, shift_start and shift_end ehic you can query just like any other table. (I am aware that the query is much longer than the one we are trying to replace, but you only do it once per database, and the example is contrived – humour me). An example query would be: SELECT * FROM shifts_reports WHERE company_id = 4 – much cleaner! Where this becomes even cleaner is if you are trying to link this up to a search form – everything matches up with a much bigger bow (especially if you are using a framework like Rails).

Whilst on the topic of frameworks (like Rails) – because it is exposed as a regular table, you can point ActiveRecord at it – just create a corresponding model and find until your heart is content, just don’t try to modify the records, as it will fail miserably (views are read-only).

So next time a client asks you to create an impossible report, your cold sweat may be slightly less shiver-inducing…

I was browsing through some ruby blogs and came across this crazy meme – shell command history distributions.

By running the following command in bash (zsh needs a -n 1000 after history apparently)

history|awk '{a[$2]++} END{for(i in a){printf "%5d\t%s \n",a[i],i}}'|sort -rn|head

and you’ll get the top ten most used shell commands. Mine are:

259 ENV=test  
45 cd  
36 vi  
36 ls  
34 svn  
18 script/spec  
12 rake  
9 fg  
8 cap  
7 script/generate

I’ve obviously been doing a lot of testing on a project I’m working on (Alas, it’s PHP). Most of the other calls are pretty rails centric though :)

I wouldn’t mind seeing what the rest of the Perth Ruby developer’s histories look like :)

Time for the second installment of Ruby on Rails Oceania – Perth Edition.

It’s once again at the Silicon Beach House:

I think we should have some presentations this month, so bring you laptop and your speaking shoes. Oh, and if someone wants to put their hand up to buy a carton, that would be much appreciated.

See you then.

Ok, I REALLY have to stop coming up with hare-brained ideas, and then listening to others when they convince me to implement them.

Spurred on my @cybner‘s new avatar, I thought it would be cool if you could slime people in Twitter if the say “I don’t know”.

So after a couple of hours of ruby hacking, I came up with: You can’t do that on twitter. Eveytime someone says the magic words, you should see their avatar get slimed! You can even try the public timeline or your own timeline (Click here and and change madpilot in the url to your username). Note though, that because this isn’t authenticated, your timeline will need to be public for it to work.

How it works

It is all very simple, using Ruby CGI, Mechanize and Hpricot. The steps are:

1. Work out what page is requested (uses apache mod_rewrite for this)
2. Build the twitter URL, and request it using Mechanize
3. Parse the HTML using Hpricot
4. Append a new JavaScript file, which inserts the slime image based on an inserted class name
5. Iterate through the tweets, grepping for the term “I don’t know”. If it finds it, it inserts the “slime” class
6. On window.load, the inserted JavaScript file finds all images with that class and dynamically inserts the slime.png image above it. Using the twitter prototype instance, it gets placed over the avatar image.

I started off using Mechanize, then realised I didn’t really need it, but left it in there as it does do the HTTP request thing quite well. Download the source. Enjoy!

Update: Some good old You can’t do that on television sliming:

Have you ever had had an inkling to interpret JavaScript with in your Ruby code? Well, I have, so I did some investigating and found Spidermonkey – the API that drives Firefox and Thunderbird’s JavaScript Engine. Being a C-library, it would be (relatively) trivial to write a Ruby interface – and thanks to the wonderful wold of the internets, someone has done just that. Unfortunately, the author is Japanese, and the documentation is sparse. Also, the code hasn’t really been updated since 2006 – but it still works, and thankfully someone else has decided to do a little work on it.

Anyway, here my instructions to install spidermonkey and the ruby library on Gentoo. These instructions may or may not work on other Linux distros or OSX (besides the obvious Gentoo specific emerge function).

Install Spidermonkey

Although the libraries links against Spidermonkey 1.7, running the tests ends in a segfault, so 1.6 is the way to go. If you have updated your portage tree, you will need to lock to the specific version:

emerge =spidermonkey-1.6

Install the library

I installed the updated version from matthewd’s git repository, although the extconf.rb file still needs a little tweaking. Also there are a few tests that will still fail – probably nothing that a little C-hacking can’t fix.

Anyway, open extconf.rb and make it look like this:

require 'mkmf'
require 'pkg-config'

def find_smjs(mozjs)
  dir_config(mozjs)
  #$CFLAGS += " -gdbg"
  case CONFIG['target_os']
  when /mswin32|mingw|bccwin32/
    $defs << " -DXP_WIN"
    lib = "js32"
  else
    $defs << " -DXP_UNIX"
    lib = mozjs
  end

  $defs << " -DNEED_#{mozjs.upcase}_PREFIX"
  have_library(lib)
end

if find_smjs('js') or find_smjs('mozjs') or (CONFIG['target_os'] =~ /mswin32|mingw|bccwin32/ and (find_smjs('mozjs') or find_smjs('smjs') or find_smjs('js'))) or
  %w(xulrunner-js thunderbird-js mozilla-js).any? do |package|
    PKGConfig.have_package(package)
  end

  create_makefile("spidermonkey")
else
  exit 1
end

Finally open spidermonkey.c and find the header include files. Change add these lines before the #else directive

#elif defined NEED_JS_PREFIX
#  include <js/jsapi.h>;
#  include <js/jshash.h>;
#  include <js/jsobj.h>;

Now run the following to build:

ruby extconf.rb
make
sudo make install

If you didn’t have any errors, you should sweet.

Using the library

Now, to parse, compile and execute some javascript code, you need to create a Context, then evaluate the code. So drop this into irb:

require 'spidermonkey'
js = << -JS
function test() {
  return 1 + 2;
}
JS

context = SpiderMonkey::Context.new
context.evaluate(js);
context.evaluate('test();')

Irb should output 3, which, last time I checked is still equal to 1+2!

As you can see, you can make multiple calls within the given context, and all of the functions and variables are persistent. I’ll post more notes as I find them.

My follow up RESTful Rails blog post-cum-article has been released into the wild. If you have been looking at RESTful Rails, and wondering what the hell is going on, go and have a read. If you don’t know what REST is, go and check out the earlier blog post to get up to speed.

Personal promotion over.

Matt Didcoe has finally gotten the ball rolling on a Perth Ruby on Rails user group with the inaugural meetup happening at the Silicon Beach House on Wednesday 20 February 2008 from 5:30pm. The first one will be pretty informal – no talks (although, I’m sure there will be plenty of taking).

So if you you use Rails, have tried Rails, are thinking about learning Rails or have only just heard of Rails, come along and watch me and Matt try to out geek each other :P

Details in a nutshell:

Anyone in the Rails community would have read Zed Shaw’s rant about Rails. For those of you who don’t know Zed, he wrote Mongrel, which is the default web server library used in Rails. It has blown up and been discussed on just about every list, including Rails Oceania. I’m not going to discuss what he said, or his tone, as it has been done to death, and he seems like the type of guy that you need to know to understand where he is coming from.

What did hit home from me was what he said out enterprise Rails. To frame this correctly, have a listen to the first half of this podcast from RailsConf.

As a rubyist, I could never understand why projects like JRuby or IronRuby existed. Why would you want to run another language in a different virtual machine? After reading and listening to Zed, the answer is obvious – integration for enterprise. If you look at existing enterprise systems they will run on technologies such as Java and ASP.NET and with good reason: Support. You can go to certified training courses and become a certified engineer, which makes hiring for these large corporates easy. There is also a a large number of consultants that have based their business models on these technologies. These guys know things like Tomcat and IIS – they don’t know (or care) about Mongrel or Lighttpd or even Apache.

Web 2.0 is not going to be around forever – regardless of whether you think the web-o-sphere is in a bubble at the moment, it will cycle at some point, and there is going to be a hell of a lot Rails developers out of jobs. Software-as-a-Service† will alleviate some of this, but there is one BIG area that has been ignored – and that is corporate and government applications. The beauty of targeting this audience is that it is big and constant – their will always be big business that need IT systems. There is a number of reasons why this segment has been ignored by Rails coders that Zed rattles off – issues with connecting to legacy databases, lack of LDAP/central login system access – but these are technical issues, which are easy for programmers to fix.

I think the bigger issue is the complexity culture of enterprise systems – DRY isn’t something is in their vocabularies, so we don’t like to talk to them, because it makes our lives harder, and they don’t want to talk to us, because they don’t think the systems we build will do what they are asking. Having said that, I’ve seen enough hacked up Access databases in government and the education system to realise that this is complexity, for complexity’s sake.

Things like JRuby and IronRuby are going to make the the integration side of things easier – now we need to start getting back in to the corporate space.

†SaaS is basically the same thing as ASP – ASP is what it was called during the last dot-com boom.