Bloggy Hell

TwitThis || StumbleUpon || Digg it || del.icio.us || Reddit

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*

TwitThis || StumbleUpon || Digg it || del.icio.us || Reddit

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):

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

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.

TwitThis || StumbleUpon || Digg it || del.icio.us || Reddit

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


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…

TwitThis || StumbleUpon || Digg it || del.icio.us || Reddit

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

TwitThis || StumbleUpon || Digg it || del.icio.us || Reddit

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

It’s once again at the Silicon Beach House:

Ruby on Rails User Group Perth second edition: 24 Apr 2008 at 5:30 until about 7:30 at the Level 2, 90 King St Perth WA

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.