Introducing the Large Hadron Migrator

Rails style database migrations are a useful way to evolve your data schema in an agile manner. Most Rails projects start like this, and at first, making changes is fast and easy.

That is until your tables grow to millions of records. At this point, the locking nature of ALTER TABLE may take your site down for an hour our more while critical tables are migrated. In order to avoid this, developers begin to design around the problem by introducing join tables or moving the data into another layer. Development gets less and less agile as tables grow and grow. To make the problem worse, adding or changing indices to optimize data access becomes just as difficult.

Side effects may include black holes and universe implosion.

There are few things that can be done at the server or engine level. It is possible to change default values in an ALTER TABLE without locking the table. The InnoDB Plugin provides facilities for online index creation, which is great if you are using this engine, but only solves half the problem.

At SoundCloud we started having migration pains quite a while ago, and after looking around for third party solutions [0] 2, we decided to create our own. We called it Large Hadron Migrator, and it is a gem for online ActiveRecord migrations.

The Large Hadron collider at CERN

The idea

The basic idea is to perform the migration online while the system is live, without locking the table. Similar to OAK (online alter table) 2 and the facebook tool [0], we use a copy table, triggers and a journal table.

We copy successive ranges of data from the original table to a copy table and then rename both at the end. Since UPDATE, DELETE and CREATE statements continue to hit the original table while doing this, we add triggers to capture these changes into a journal table.

At the end of the copying process, the journal table is replayed so that none of these intervening mutations are lost.

The Large Hadron is a test driven Ruby solution which can easily be dropped into an ActiveRecord migration. It presumes a single auto incremented numerical primary key called id as per the Rails convention. Unlike the twitter solution [1], it does not require the presence of an indexed updated_at column.

Usage

Large Hadron Migrator is currently implemented as a Rails ActiveRecord Migration.

class AddIndexToEmails < LargeHadronMigration
  def self.up
    large_hadron_migrate :emails, :wait => 0.2 do |table_name|
      execute %Q{
        alter table %s
          add index index_emails_on_hashed_address (hashed_address)
      } % table_name
    end
  end
end

Migration phases

LHM runs through the following phases during a migration.

1. Get the maximum primary key value for the table

When starting the migration, we remember the last insert id on the original table. When the original table is copied into the new table, we stop at this id. The rest of the records will be found in the journal table – see below.

2. Create new table and journal table

The two tables are cloned using SHOW CREATE TABLE. The journal table has an extra action field (update, delete, insert).

3. Activate journalling with triggers

Triggers are created for each of the action types ‘create’, ‘update’ and ‘delete’. Triggers are responsible for filling the journal table.

Because the journal table has the same primary key as the original table, there can only ever be one version of the record in the journal table.

If the journalling trigger hits an already persisted record, it will be replaced with the latest data and action. ON DUPLICATE KEY comes in handy here. This insures that all journal records will be consistent with the original table.

4. Perform alter statement on new table

The user supplied ALTER TABLE statement(s) or index changes are applied to the new table. Our tests using InnodDB showed this to be faster than adding the indexes at the end of the copying process.

5. Copy in chunks up to max primary key value to new table

Currently InnoDB aquires a read lock on the source rows in INSERT INTO... SELECT. LHM reads 35K ranges and pauses for a specified number of milliseconds so that contention can be minimized.

6. Switch new and original table names and remove triggers

The orignal and copy table are now atomically switched with RENAME TABLE original TO archive_original, copy_table TO original. The triggers are removed so that journalling stops and all mutations and reads now go against the original table.

7. Replay journal: insert, update, deletes

Because the chunked copy stops at the intial maximum id, we can simply replay all inserts in the journal table without worrying about collisions.

Updates and deletes are then replayed.

Potential issues

Locks could be avoided during the copy phase by loading records into an outfile and then reading them back into the copy table. The facebook solution does this and reads in 500000 rows and is faster for this reason. We plan to add this optimization to LHM.

Data is eventually consistent while replaying the journal, so there may be delays while the journal is replayed. The journal is replayed in a single pass, so this will be quite short compared to the copy phase. The inconsistency during replay is similar in effect to a slave which is slightly behind master.

There is also caveat with the current journalling scheme; stale journal ‘update’ entries are still replayed. Imagine an update to the a record in the migrated table while the journal is replaying. The journal may already contain an update for this record, which becomes stale now. When it is replayed, the second change will be lost. So if a record is updated twice, once before and once during the replay window, the second update will be lost.

There are several ways this edge case could be resolved. One way would be to add an UPDATE trigger to the main table, and delete corresponding records from the journal while replaying. This would ensure that the journal does not contain stale update entries.

Near disaster at the collider

Having scratched our itch, we went ahead and got ready to roll schema and index changes that would impact hundreds of millions of records across many tables. There was a backlog of changes we rolled out in one go.

At the time, our MySQL slaves were regularly struggling with their replication thread. They were often far behind master. Some of the changes were designed to relieve this situation. Because of the slave lag, we configured the LHM to add a bit more wait time between chunks, which made the total migration time quite long. After running some rehersals, we agreed on the settings and rolled out to live, expecting 5 – 7 hours to complete the migrations.

Several hours into the migration, a critical fix had to be deployed to the site. We rolled out the fix and restarted the app servers in mid migration. This was not a good idea.

The lesson: never restart during migrations when removing columns with large hadron. You can restart while adding migrations as long as active record reads column definitions from the slave.

The information below is only relevant if you want to restart your app servers while migrating in a master slave setup.

If you anticipate the need to restart your application servers while migrating, then check the comments in large_hadron_migrator.rb for detailed instructions on how to do this.

Future work

The following improvements should be made at some point:

• Loading data into outfile instead of INSERT INTO... SELECT. Avoid contention and increase speed.
• Handle invalidation of ‘update’ entries in journal while replaying. Avoid stale update replays.

Some other optimizations:

• Deletions create gaps in the primary key id integer column. LHM has no problems with this, but the chunked copy could be sped up by factoring this in. Currently a copy range may be completely empty, but there will still be a INSERT INTO... SELECT.
• Records inserted after the last insert id is retrieved and before the triggers are created are currently lost. The table should be briefly locked while id is read and triggers are applied.

Footnotes [0] Facebook Online Schema Change [1] Twitter Table Migrator 2 OAK online alter table The Large Hadron Migrator on Github