Today we are happy to present , an ActiveRecord 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 . Percona Migrator adapter Redbooth Motivation It was back in early 2013 when we started using 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. Soundcloud’s LHM 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 commands. To run a migration the developer had to execute a rake task which would return a 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. pt-online-schema-change pt-online-schema-change Although this process was a bit tedious, it allowed us to apply schema changes to our without any problem, which brought our weekends back. database Online schema migration tools Percona’s , like most of online schema migration tools such as Github's , Facebook's or Soundcloud's alters tables without locking them by means of a new table. pt-online-schema-change gh-ost OSC LHM 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 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, is command line tool that acts as database client, sending statements to the database. pt-online-schema-change pt-online-schema-change The fact that 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. pt-online-schema-change An ActiveRecord adapter for migrations A year after, tired of copy pasting 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. pt-online-schema-change 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 and executes the MySQL statements through Percona's . This provides the best of without having to change the way you write Rails migrations. config/databases.yml pt-online-schema-change Percona Toolkit If you were a 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. pt-online-schema-change Give ❤️ to your database It’s dead simple to start using it. Add this to your Gemfile: https://gist.github.com/712aaa37606dd1594a9e2f36c7ac6e33 And run . Nothing else. bundle install For a simple migration like this one: https://gist.github.com/1f60c16668dbc39fcca2ccd83e4c2361 The output when running will change to: bundle exec rake db:migrate:up As a result, your migrations benefit from 's features such as: pt-online-schema-change 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 contribution we recently got and we plan to have support for Rails 4.1 real soon! Adrian Serafin’s support for Rails 4.2 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!

Percona Migrator Announcement

Too Long; Didn't Read

Company Mentioned

@prez_pau

RELATED STORIES