5 ms·

This article seems to be about really fundamental changes to your data modeling. If you're using MySQL and interested in only changing one table, i.e. adding/re

This article seems to be about really fundamental changes to your data modeling. If you're using MySQL and interested in only changing one table, i.e. adding/removing a column or an index, check out LHM: https://github.com/soundcloud/lhm https://github.com/soundcloud/lhm I've used it in the past a lot. You have to make sure to throttle the migration, or else it'll max out your CPU & memory.

lgierth 10y ago
In addition, in replicated setups, the more important reason for throttling the migration is avoiding slave lag. Disclaimer: I worked on LHM a few years ago, still one of my favorite projects :)
perlgeek 10y ago
Another tool in this category is gh-ost by github: https://github.com/github/gh-ost https://github.com/github/gh-ost Its main advantage seems to be that it doesn't require triggers on the original table (it works by subscribing to the replication log), thus not making write locks worse during the migration. I haven't tried either yet, so if anybody has some experience with them, do please share a comparison!
wolf550e 10y ago
Is it better than pt-online-schema-change from percona-toolkit? That thing creates a new table which is the same shape as the old one, runs an ALTER on the new table, sets up triggers on the old table to update the new table, and then copies the old rows in batches while monitoring replication slave lag. Then it renames the tables and drops the old one. You do need storage for twice the space of the table, at least temporarily.
- mcpherrinm 10y ago
  Square's wrapper around pt-online-schema change is really slick: https://github.com/square/shift https://github.com/square/shift It has a nice webapp to monitor and run schema migrations, including peer review. I use it all the time at Square, and it's pretty great.
sciurus 10y ago
Yeah, in simpler circumstances you can follow the approach of 1) Write code that works with current and new schema 2) Deploy that code 3) Run that version for long enough to know you will not roll it back 3) Run the migration, moving the db from current to new schema 4) Clean up code to only work with new schema 5) Deploy that code Where step 3 uses an appropriate tool for online schema changes for your database. Here a some writeups of that approach I found when trying to explain this to a friend who's a rails developer: * http://blog.honeybadger.io/zero-downtime-migrations-of-large-databases-using-rails-postgres-and-redis/ http://blog.honeybadger.io/zero-downtime-migrations-of-large... * https://pedro.herokuapp.com/past/2011/7/13/rails_migrations_with_no_downtime/ https://pedro.herokuapp.com/past/2011/7/13/rails_migrations_... * https://www.rainforestqa.com/blog/2014-06-27-zero-downtime-database-migrations/ https://www.rainforestqa.com/blog/2014-06-27-zero-downtime-d... * https://blog.engineyard.com/2011/zero-downtime-deploys-with-migrations https://blog.engineyard.com/2011/zero-downtime-deploys-with-...

lgierth 10y ago

In addition, in replicated setups, the more important reason for throttling the migration is avoiding slave lag. Disclaimer: I worked on LHM a few years ago, still one of my favorite projects :)

perlgeek 10y ago

Another tool in this category is gh-ost by github: https://github.com/github/gh-ost https://github.com/github/gh-ost Its main advantage seems to be that it doesn't require triggers on the original table (it works by subscribing to the replication log), thus not making write locks worse during the migration. I haven't tried either yet, so if anybody has some experience with them, do please share a comparison!

wolf550e 10y ago

Is it better than pt-online-schema-change from percona-toolkit? That thing creates a new table which is the same shape as the old one, runs an ALTER on the new table, sets up triggers on the old table to update the new table, and then copies the old rows in batches while monitoring replication slave lag. Then it renames the tables and drops the old one. You do need storage for twice the space of the table, at least temporarily.

mcpherrinm 10y ago
Square's wrapper around pt-online-schema change is really slick: https://github.com/square/shift https://github.com/square/shift It has a nice webapp to monitor and run schema migrations, including peer review. I use it all the time at Square, and it's pretty great.

sciurus 10y ago

Yeah, in simpler circumstances you can follow the approach of 1) Write code that works with current and new schema 2) Deploy that code 3) Run that version for long enough to know you will not roll it back 3) Run the migration, moving the db from current to new schema 4) Clean up code to only work with new schema 5) Deploy that code Where step 3 uses an appropriate tool for online schema changes for your database. Here a some writeups of that approach I found when trying to explain this to a friend who's a rails developer: * http://blog.honeybadger.io/zero-downtime-migrations-of-large-databases-using-rails-postgres-and-redis/ http://blog.honeybadger.io/zero-downtime-migrations-of-large... * https://pedro.herokuapp.com/past/2011/7/13/rails_migrations_with_no_downtime/ https://pedro.herokuapp.com/past/2011/7/13/rails_migrations_... * https://www.rainforestqa.com/blog/2014-06-27-zero-downtime-database-migrations/ https://www.rainforestqa.com/blog/2014-06-27-zero-downtime-d... * https://blog.engineyard.com/2011/zero-downtime-deploys-with-migrations https://blog.engineyard.com/2011/zero-downtime-deploys-with-...