Posts by Caius Durling

MySQL slow query improvements 28 Jan 10

We’ve just pushed some updates to the MySQL slow query warning interface in the Brightbox control panel, which includes two main improvements.

Firstly, where the same (or very similar) query occurs more than once, they are aggregated together rather than being recorded as individual slow queries. The user interface displays the count so you can see exactly how many times this slow query occurred in the last 7 days.

Secondly, the full SQL query is now displayed, so customers now have more information with which to optimise their applications.

Brightbox Gem v2.3.6 released 27 Jan 10

Today I’m pleased to announce a new revision of the Brightbox Deployment Gem, version 2.3.6. This release fixes some of the issues reported to us by our users, along with a few extra features.

The documentation on the wiki has been updated with the new fixes and features. If you have any tips or find any errors just let us know.

NEW FEATURES

Apt Package Dependencies

Just as you can currently define gems your application relies on and have them automatically installed onto your servers, you can now do the same with apt packages. Define them in your deploy.rb and they’ll be installed before installing your required rubygems, which means you can use it to install apt packages that gems depend on. As an example, the nokogiri gem depends on a couple of apt packages.

depend :remote, :apt, "libxml2-dev"
depend :remote, :apt, "libxslt1-dev"
depend :remote, :gem, "nokogiri", ">= 0"

Intermediate SSL Certificates

Support for Intermediate SSL Certificates in Apache has been added, meaning you no longer need to manually edit the Apache config files directly. Just add one line to your deploy.rb and the gem now takes care of updating the Apache config for you. An example SSL configuration:

set :ssl_certificate, "my_cert.pem"
set :ssl_key, "my_cert.key"
set :ssl_intermediate, "intermediate.crt"

See the wiki page for Adding SSL Support to Apache for more information.

Deploy.local.rb

A common issue we’ve seen with is setting the deploy password. If you put it in deploy.rb, then it inevitably ends up in your source control repository, which is a bad idea. A workaround we’ve suggested in the past is to have a deploy.local.rb file, which isn’t tracked by source control, and load that from within your deploy.rb file. This allows you to have the password (or any other sensitive settings) defined, but kept outside your source control.

As part of this release, the gem now has official support for a deploy.local.rb file. If it exists, the gem will load it in for you.

For more information see the Advanced Gem Settings page on the wiki.

Disable (re)generating webserver config

If you’ve made manual changes to your webserver (apache or nginx) configuration, then you don’t want an accidental deploy:setup to overwrite your configs. Up until now you just had to avoid running the command, but now there’s a setting to disable it for you.

set :generate_webserver_config, false

FIXES AND ENHANCEMENTS

• Max Age setting now accepts a number as well as a string
• deploy:initial reloads Apache so the new config is loaded

Clearing out Rails Sessions 19 Nov 09

As I’m sure you’re aware, Rails can be told to use ActiveRecord (and hence the MySQL database) to store session data. (New Rails apps use the cookie store by default—See the rails sessions guide for more info on both.)

However, this session data is never deleted, which means your session table continues to grow and grow forevermore. Your old sessions are left stored in the database, and although the table is indexed to help with finding sessions, it will eventually fill the disk up.

At this point you might be thinking the solution is obvious, just empty the sessions table after so long and let it fill up again. Rails even provides a rake task that does this for us, rake db:sessions:clear. The problem with taking this approach is that any active sessions get lost as well, which could be people with items in their baskets, currently logged in users, etc.

There is another solution, which is to only delete sessions that we consider to no longer be active. The updated_at column in the sessions table has an index on it, and thus looks designed for this type of query to be run. On the Brightbox control panel, we’ve decided this is session data that hasn’t been updated for over 24 hours. (A side effect of this is customers that haven’t visited the control panel in the last 24 hours are logged out. We’ve decided this is ok, but YMMV of course.)

We use the following rake task that clears out sessions 24 hours or older, which is run via cron at 3am every morning. You can change the threshold by editing "1 DAY" in the query, see the MySQL DATE_ADD() docs for valid values.

desc "Clear expired sessions"
task :clear_expired_sessions => :environment do
    sql = 'DELETE FROM sessions WHERE updated_at < DATE_SUB(NOW(), INTERVAL 1 DAY);'
    ActiveRecord::Base.connection.execute(sql)
end

As you have deleted quite a large number of rows, the sessions table may now be fragmented and affect performance. Fragmentation also means the table may hold a piece of the disk larger than it should for the number of rows it holds.

De-fragmenting the table does introduce locks, so we suggest running this when your site is quiet. Though, it is most likely the command below will only take 1 or 2 seconds to complete.

To de-fragment your table login to your Brightbox, and use the MySQL client to connect to your database. You can find out your database server, and password from the left hand menu item “MySQL” in your Control Panel.

mysql -h mysqlserver -u username -p

Select the database which holds the sessions table.

use database_name;

Then run the optimize command to de-fragment the table.

optimize table sessions;

You should now have a smaller, and faster sessions table.

The Brightbox Toolkit 21 Oct 09

It’s been quite interesting to read the recent “Tools of the Trade” meme where people are blogging what they use to do their job. I found it so interesting in fact, that I decided to find out what tools we all use at Brightbox. Here’s our list.

Caius

Hardware

• 15” Macbook Pro (Unibody) with 4GB ram, 7200rpm 320GB HD
• 48GB SSD Expresscard
• 24” Samsung External Monitor

Software

• TextMate – My third arm.
• iTerm – Gives me back cmd-<num> for switching tabs
• Adium / Tweetie / Limechat for communication
• Safari – Eats ram, but I still prefer it to any other mac browsers
• HTTP Client – Graphical curl, great for debugging XML calls
• Markdown – I write everything in it.

Read the rest of this entry »

Leeds Ruby Thing 24 Nov 08

If you’re in or around Leeds this Thursday (27th November), pop along to Mr Foleys Cask Ale House at 7pm for yet another Leeds Ruby Thing. Its a fun and informal social event for anyone interested in the Ruby programming language. You don’t have to be an expert in Ruby—we’re just a friendly bunch who enjoy beer and geeky chat.

And this month Brightbox is giving away our famous t-shirts and stickers, as well as plenty of beer!

Add yourself to the event on upcoming, or just feel free to turn up on the night.

http://upcoming.yahoo.com/event/1337490/