July 31, 2014

Integrating pt-online-schema-change with a Scripted Deployment

Recently, I helped a client that was having issues with deployments causing locking in their production databases.  At a high level, the two key components used in the environment were:

  • Capistrano (scripted deployments) [website]
  • Liquibase (database version control) [website]

At a high level, they currently used a CLI call to Liquibase as a sub-task within a larger deployment task.  The goal of this engagement was to modify that sub-task to run Liquibase in a non-blocking fashion as opposed to the default that just runs native ALTERS against the database.

As I wasn’t very familiar with Liquibase, I took this opportunity to learn more about it and it seems like a very valuable tool.  Essentially, it does the following:

  • Adds two version control tables to your schema
  • Reads an XML “changelog” file that has the schema changes
  • Verifies which changes have yet to be applied
  • Applies the changes in serial to the tables
  • Records each change in the version control table as it happens

While this is a very powerful tool, as you dataset grows, this can be problematic.  As each change is run as an independent ALTER statement, consider a large (several million row) table that you are updating with multiple indexes – this can result in several lengthy blocking operations that can impact your application in other ways.

My first thought when hearing that table alters were locking up the production application was naturally to use Percona Toolkit’s pt-online-schema-change – but the challenge lied with the integration of the tools.  After some investigation and discussion, I found two approaches that seemed feasible to get pt-osc integrated:

  1. Write a custom plugin for Liquibase
  2. Utilize the updateSQL action to fetch the raw SQL and parse/process each statement

Due to time constraints, we landed on option 2.  In pseudocode, here is the approach that we took:

This turned out to be exactly what the client needed as it allowed the code deployment to still utilize the version control methods that were understood, but did so in a non-blocking manner.  The main caveat with this approach is that the application code needs to be backwards and forwards compatible with the changes.  As you now may see a long running background process, depending on when your code is actually deployed in the complete process, it may need to handle the database in different states.

Although this is a frequent scenario with deployments anyway, I just wanted make note that often times when changing schema, the application must be able to handle the database in different states.

Here is the link to the (sanitized) proof-of-concept code and some samples I used for testing: https://github.com/mbenshoof/liqui-cap-online.  Please note that I am by no means a Ruby developer so I assume there are plenty of coding optimizations that can be made – this was more of an exercise in combining the tools as opposed to a Ruby showcase.

About Mike Benshoof

Michael joined Percona in 2012 as a US based consultant. Prior to joining Percona, Michael spent several years in a DevOps role maintaining a SaaS application specializing in social networking. His experiences include application development and scaling, systems administration, along with database administration and design. He enjoys designing extensible and flexible solutions to problems.

When not working, he enjoys golfing, grilling, watching sports, and spending time with the family.

Comments

  1. Hi Mike,

    I wrote also some tool to completely replace Liquibase that allows you to migrate your schema but also to rollback or verify.

    It can be integrated easily with puppet to automate all the process. It uses Oracle’s MySQL-utilities and pt-online-schema-change.

    If you are interested, I did a presentation at PLUK: http://www.percona.com/live/london-2013/sessions/mysql-schemas-versioning-and-deployment-automation-production

    It’s also hosted on github: https://github.com/lefred/online-migration

    Cheers ;)

  2. Jim says:

    Another option is using liquibase’s execute command from within a changeSet: http://www.liquibase.org/documentation/changes/execute_command.html

Speak Your Mind

*