Today we are happy to present Percona Migrator, an ActiveRecord adapter to run online MySQL schema migrations in Rails. Since we implemented it back in December 2014 it has gone through few stages but it has always been an essential part of our tooling at Redbooth.
Motivation
It was back in early 2013 when we started using Soundcloud’s LHM to overcome the limitation of MySQL 5.5 where schema changes lock the table for the duration of the change. This led to a very bad experience for highly concurrent and large tables, which was the case already back in that time.
Unfortunately a year after, a bug with single-record tables plus the lack of support for foreign key constraints made us create our own fork that we forgot to maintain.
In the second half of 2014, LHM turned out to be insufficient for our production database, which kept growing in size and load. Running a migration against comments and tasks, the tables that experience the highest level of concurrency, caused dead-locks unless we ran them on weekends. Even back in 2014 no-one would expect to have zero-downtime migrations and deploys.
Not less important was the fact that LHM forced us to give up on Rails migrations DSL, which we believe is a joy to use. We ended up with less understandable migrations and hardcoded values for the throttling.
So, we decided to work on a tool that could fix these issues and allowed us to have relaxed weekends again.
Percona Migrator
It first started as a library in our Rails 3.2 app that parsed LHM’s migrations DSL and returned Percona’s pt-online-schema-change commands. To run a migration the developer had to execute a rake task which would return a pt-online-schema-change command with arguments, copy and paste it and execute the command. Then, we had a couple other rake tasks to mark the migration as up or down.
Although this process was a bit tedious, it allowed us to apply schema changes to our database without any problem, which brought our weekends back.
Online schema migration tools
Percona’s pt-online-schema-change, like most of online schema migration tools such as Github's gh-ost, Facebook's OSC or Soundcloud's LHM alters tables without locking them by means of a new table.
A copy of the table is created with the new schema and rows are copied to it by chunks without affecting the databases’ load too much. When done, this table replaces the old one.
The main difference between pt-online-schema-change and LHM lies in the level of the stack they work on. While LHM is a Ruby gem that makes you write Rails migrations with its own DSL, pt-online-schema-change is command line tool that acts as database client, sending statements to the database.
The fact that pt-online-schema-change copies the data to the new table in dinamically adjusted chunks by automatically checking server load, saved us from increasing the database server load too much, making migrations just a bit longer but much safer.
An ActiveRecord adapter for migrations
A year after, tired of copy pasting pt-online-schema-change commands, we decided to finally move it to a ruby gem that automatically executed them and updated the migration's status for us, but still as rake task.
Then we decided to open-source it, started to write the docs and found out some flaws that delayed the release even more. We saw the oportunity to make things even better by implementing it as an ActiveRecord adapter.
It sounded crazy at first, but we started working on an adaper for ActiveRecord 3.2, the Rails version our app is still running on.
It replaces the adapter you configure in config/databases.yml and executes the MySQL statements through Percona's pt-online-schema-change. This provides the best of Percona Toolkit without having to change the way you write Rails migrations.
If you were a pt-online-schema-change user before, we got you covered. You can pass arguments to it through an environment variable. If you come from LHM like us, you don't have to rewrite your migrations either; the Percona Migrator translates LHM's DSL to the Rails one.
Give ❤️ to your database
It’s dead simple to start using it. Add this to your Gemfile:
https://gist.github.com/712aaa37606dd1594a9e2f36c7ac6e33
And run bundle install. Nothing else.
For a simple migration like this one:
https://gist.github.com/1f60c16668dbc39fcca2ccd83e4c2361
The output when running bundle exec rake db:migrate:up will change to:
As a result, your migrations benefit from pt-online-schema-change's features such as:
- Automatic migration abort when the server load is too high or the replica lag is too large
- Dinamically adjusted chunk size so data-copy queries don’t take too long
Check the full list in its documentation
Thanks to Adrian Serafin’s contribution we recently got support for Rails 4.2 and we plan to have support for Rails 4.1 real soon!
You like it but your app is on Rails 5? don’t hesitate to open a PR and dig into ActiveRecord! you will find more details in the README.
Happy migrations!