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.
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.
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
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:
bundle install. Nothing else.
For a simple migration like this one:
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
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.