Moving a site to The Cloud

Last week I did a lot of reading and research into cloud hosting. The "Cloud" has been a buzzword for a while now, often bandied about by those who know no better as a simple sprinkle-on solution for all of your scale problems - much in the same way as AJAX was touted around a few years ago as a magic solution to all of your interface problems.

The perception can sometimes seem to be Hey, if we just shift X to The Cloud, we can scale it infinitely!. The reality, of course, is something rather more qualified. Yes, in theory, the cloud has all the capacity you're likely to need, unless you're going to be bigger than, say, Amazon - (are you? Are you reeeeeeeally? C'mon, be honest...) - provided - and it's a big proviso - that you architect it correctly. You can't just take an existing application and dump it into the cloud and expect to never have a transaction deadlock again, for instance. That's an application usage pattern issue that needs to be dealt with in your application, and no amount of hardware, physical or virtual, will solve it.

There are also some constraints that you'll need to work around, that may seem a little confusing at first. But once I got it, the light went on, and I became increasingly of the opinion that the cloud architecture is just sheer bloody genius.

What kind of constraints are they? Well, let's focus on Amazon's EC2, as it's the most well-known....

• Your cloud-hosted servers are instances of an image
  They're not physical machines - you can think of them as copies of a template Virtual Machine, if you like. Like a VMWare image. OK, that one's fairly straightforward. Got it? Good. Next:


• Instances are transient - they do not live for ever
  Bit more of the same, this means that you create and destroy instances as you need. The flipside is that there is no guarantee that the instance you created yesterday will still be there today. It should be, but it might not be. EC2 instances do die, and when they do, they can't be brought back - you need to create a new one. This is by design. Honestly!


• Anything you write to an instance's disk after creation is non-persistent
  Now we're getting down to it. This means that if you create an instance of, say, a bare-bones Linux install, and then install some more software onto it, and set up a website, then the instance dies - everything you've written to that instance's disk is GONE. There are good strategies for dealing with this, which we'll come onto next, but this is also by design. Yes, it is...


• You can attach EBS persistent storage volumes to an instance - but only to one instance per volume
  This one is maybe the most obscure constraint but is quite significant. Take a common architecture of two load-balanced web servers with a separate database server. It's obvious that the database needs to be stored on a persistent EBS volume - but what if the site involves users uploading files? Where do they live? A common pattern would be to have a shared file storage area mounted onto both web servers - but if an EBS volume can only be attached to one instance, you can't do that.

Think about that for a few seconds - this has some pretty serious implications for the architecture of a cloud-hosted site. BUT - and here's the sheer bloody genius - these are the kind of things you'd have to deal with for scaling out a site on physical servers anyway. Physical hardware and especially disks are not infallible and shouldn't be relied on. Servers can and do go down. Disks conk out. Scaling out horizontally needs up-front thought put into the architecture. The cloud constraints simply force you to accept that, and deal with it by designing your applications with horizontal scaling in mind from the start. And, coincidentally, provide some kick-ass tools to help you do that.

Take, for example, the last bullet point above - that EBS volumes can only be attached to one instance. So how do you have file storage shared between N load-balanced web servers? Well, the logical thing to do is to have a separate instance with a big persistent EBS volume attached to it, and have the web servers access it by some defined API - WebDAV, say, or something more application-specific.

But hang on.... isn't that what you should be doing anyway?. Isn't that a more scalable model? So that when your fileserver load becomes large, you could, say, create more instances to service your file requests, and maybe load-balance those, and....

See? It forces you to do the right thing - or, at least, put in the thought up front as to how you'll handle it. And if you then decide to stubbornly go ahead and do the wrong thing, then that's up to you... :)

So, anyway, I wanted to get my head round it, and thought I'd start by shifting Cragwag and Sybilline onto Amazons EC2 cloud hosting service. I did this over a two day period - most of which, it has to be said, was spent setting up Linux the way I was used to, rather than the cloud config - and I'll be blogging a few small, self-contained articles with handy tips I've learned along the way. Stay tuned....

OutOfMemoryError in ActiveRecord-JDBC on INSERT SELECT

During some scale testing the other day, we came across this unusual / mildly amusing error in a database-bound command that just funnels INSERT SELECT statements down the ActiveRecord JDBC driver:

java/util/Arrays.java:2734:in `copyOf': java.lang.OutOfMemoryError: Java heap space (NativeException)
 from java/util/ArrayList.java:167:in `ensureCapacity'
 from java/util/ArrayList.java:351:in `add'
 from com/mysql/jdbc/StatementImpl.java:1863:in `getGeneratedKeysInternal'
 from com/mysql/jdbc/StatementImpl.java:1818:in `getGeneratedKeys'
 from org/apache/commons/dbcp/DelegatingStatement.java:318:in `getGeneratedKeys'
 from jdbc_adapter/JdbcAdapterInternalService.java:668:in `call'
 from jdbc_adapter/JdbcAdapterInternalService.java:241:in `withConnectionAndRetry'
 from jdbc_adapter/JdbcAdapterInternalService.java:662:in `execute_insert'
  ... 25 levels...

Now, there's a couple of things here that are worth pointing out.

1. I REALLY LOVE the fact that it blew heap space in a method called ensureCapacity. That makes me smile.
2. Why is it calling getGeneratedKeys() for an INSERT SELECT?

The getGeneratedKeys() method retrieves all the primary keys that are generated when you execute an INSERT statement. Fair enough - BUT the issue here is that we'd specifically structured the process and the SQL statements involved so as to be done with INSERT SELECTS, and hence avoid great chunks of data being transferred backwards and forwards between the app and the database.

It turns out that the ActiveRecord JDBC adapter is doing this :
(lib/active_record/connection_adapters/JdbcAdapterInternalService.java)

@JRubyMethod(name = "execute_insert", required = 1)
    public static IRubyObject execute_insert(final IRubyObject recv, final IRubyObject sql) throws SQLException {
        return withConnectionAndRetry(recv, new SQLBlock() {
            public IRubyObject call(Connection c) throws SQLException {
                Statement stmt = null;
                try {
                    stmt = c.createStatement();
                    smt.executeUpdate(rubyApi.convertToRubyString(sql).getUnicodeValue(), Statement.RETURN_GENERATED_KEYS);
                    return unmarshal_id_result(recv.getRuntime(), stmt.getGeneratedKeys());
                } finally {
                    if (null != stmt) {
                        try {
                            stmt.close();
                        } catch (Exception e) {
                        }
                    }
                }
            }
        });
    }

...in other words, explicitly telling the driver to return all the generated keys.
Hmm, OK, can we get round this by NOT calling the execute_insert method, and instead calling a raw execute method that doesn't return all the keys?

Well, no, unfortunately, because it also turns out that the ruby code is doing this:
(activerecord-jdbc-adapter-0.9/lib/active_record/connection_adapters/jdbc_adapter.rb)

      # we need to do it this way, to allow Rails stupid tests to always work
      # even if we define a new execute method. Instead of mixing in a new
      # execute, an _execute should be mixed in.
      def _execute(sql, name = nil)
        if JdbcConnection::select?(sql)
          @connection.execute_query(sql)
        elsif JdbcConnection::insert?(sql)
          @connection.execute_insert(sql)
        else
          @connection.execute_update(sql)
        end
      end

...and the JdbcConnection::insert? method is detecting if something's an insert by doing this:
(JdbcAdapterInternalService.java again)

@JRubyMethod(name = "insert?", required = 1, meta = true)
    public static IRubyObject insert_p(IRubyObject recv, IRubyObject _sql) {
        ByteList bl = rubyApi.convertToRubyString(_sql).getByteList();

        int p = bl.begin;
        int pend = p + bl.realSize;

        p = whitespace(p, pend, bl);

        if(pend - p >= 6) {
            switch(bl.bytes[p++]) {
            case 'i':
            case 'I':
                switch(bl.bytes[p++]) {
                case 'n':
                case 'N':
                    switch(bl.bytes[p++]) {
                    case 's':
                    case 'S':
                        switch(bl.bytes[p++]) {
                        case 'e':
                        case 'E':
                            switch(bl.bytes[p++]) {
                            case 'r':
                            case 'R':
                                switch(bl.bytes[p++]) {
                                case 't':
                                case 'T':
                                    return recv.getRuntime().getTrue();
                                }
                            }
                        }
                    }
                }
            }
        }
        return recv.getRuntime().getFalse();
    }

...in other words, if the sql contains the word INSERT, then it's an INSERT, and should be executed with an execute_insert call.

So, looks like we're a bit knacked here. There are two possible solutions:

1. The proper solution - fix the AR JDBC adapter (and, arguably, the MySQL connector/J as well, to stop it blowing heap space), submit a patch, wait for it to be accepted and make it into the next release.


2. Or, the pragmatic solution - rewrite the SQL-heavy command as a stored procedure and just call it with an EXECUTE and sod the DHH dogma.

We went with option 2 :)

Big kudos to D. R. MacIver for tracking down the source of the fail in double-quick time.

Planning for a 1.5 Terabyte Database

Here's something to make you stroke your chin and stare into the distance for a moment or two -

A customer asked us about crunching a large amount of email. Some rough back-of-an-envelope calculations lead us to expect up to about 150GB of it in one go. Our experiments with the Enron emails on MySQL produced about a 1:10 ratio of data-in to database size, a ratio of roughly 1:1 on data-in to language model size and full-text index, and a roughly linear (not 1:1 though!) increase in processing time per email with elapsed cumulative crunching time.

So that means we can expect a database size of up to 1.5 Terabytes, plus another 300GB of language models and full-text index...

(We'd be using Oracle for the DB, as it comes with some very handy out of the box management and monitoring tools, performance advisor alerts, and all that kaboodle, and it may be that the 1:10 ratio is different on Oracle - we're looking at that at the moment)

So how do you go about planning for a database of that size?

We can't even defer the question and scale-as-we-go, as it's going to be growing to that size very quickly, within days of kicking off. It's got to be right, straight from the word go. I've worked with very large email datasets before, on Smartgroups, but in Freeserve we had a big team of specialist UNIX engineers to manage it.

How do you spec up the disk configuration, knowing that the database files are going to be that huge? We've tended to go for RAID 1 (mirrored) by default, as an until-now acceptable balance between simplicity and resilience. But this means that if you have, say, 2 x 500GB disks, you only actually get 500GB of storage. Dell provide up to 1TB disks on some servers, but damn it's pricey... we'll no doubt end up going for a combination of mirrored, striped configs, but that means more complexity of course.

On that subject, how do you organise the file system? And, crucially, how do you go about arranging back-ups for all that data? The last thing you want is for 1.5TB of data to get lost with no backup, and have to be re-calculated from scratch. The backups have to be stored somewhere, and even just the process of shifting that amount of data from one storage device to another is non-trivial. I mean, shifting 1.5x10¹³ bits, even over a dedicated, max-ed out Gigabit ethernet is going to take at least 1.5x10⁴ seconds - or just over 4hrs...

Hmm, questions, questions, questions, chin stroke, thousand-yard-stare, tap mouth absently... I'll be pondering this for a while, I think.

Mind you, my old university mate Dan once emailed me back in his PhD days, saying "Hey, you know about databases, don't you? Could you give me a quick intro? I've got to write one to handle data from the SLAC particle accelerator - it's got to handle terabytes of data per day...."
- and that was way back in about 1997 or so, when a 4GB drive would cost you nearly $500. Maybe time to give him a ping on Facebook....