Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Rails migrations with no downtime

pedro.herokuapp.com

Post reply on HN
Print view View original

11–20 of 22 posts

Re: Rails migrations with no downtime

#11

Post by mceachen »

morganpyne wrote: post #10

There is another problem unmentioned in this article with his simple table modification example... When you modify a table the DB needs to write the entire table to disk, locking it all the time. Depending on how much data is in the table and how fast your IO is this can be hours and hours of downtime on the live database, for a migration that only took a few seconds locally on your test database. Running migrations…

Absolutely! The author was very thorough with the change process, which is great, but downtime during ALTER should absolutely be broached as well.

A staging system with a db server provisioned similarly to production lets you "smoke test" the migrations and deploy before you take down prod -- but especially with MySQL, that happily blocks all reads during an ALTER, you should really watch out for any table ~> 1M rows (or if they're wide, even fewer than that).

Re: Rails migrations with no downtime

#12

Post by newhouseb »

morganpyne wrote: post #10

There is another problem unmentioned in this article with his simple table modification example... When you modify a table the DB needs to write the entire table to disk, locking it all the time. Depending on how much data is in the table and how fast your IO is this can be hours and hours of downtime on the live database, for a migration that only took a few seconds locally on your test database. Running migrations…

This is true, but note that the article was written in the context of PostgreSQL instead of MySQL. PostgreSQL can do many types of table migrations with no downtime (dropping columns, renaming, adding columns that default to NULL, and creating indexes). Before I made the switch from MySQL to Postgres I did some quick benchmarks which support these claims on a 5 million row test table: https://gist.github.com/1620133.

Re: Rails migrations with no downtime

#13

Post by glenngillen »

mceachen wrote: post #11
morganpyne wrote: post #10

There is another problem unmentioned in this article with his simple table modification example... When you modify a table the DB needs to write the entire table to disk, locking it all the time. Depending on how much data is in the table and how fast your IO is this can be hours and hours of downtime on the live database, for a migration that only took a few seconds locally on your test database. Running migrations…

Absolutely! The author was very thorough with the change process, which is great, but downtime during ALTER should absolutely be broached as well. A staging system with a db server provisioned similarly to production lets you "smoke test" the migrations and deploy before you take down prod -- but especially with MySQL, that happily blocks all reads during an ALTER, you should really watch out for any table ~> 1M rows…

If you're using postgres on Heroku you can get a fork of your production db to test things like these:

http://devcenter.heroku.com/articles/heroku-postgresql#fork_...

Re: Rails migrations with no downtime

#14

Post by c3 »

morganpyne wrote: post #9
c3 wrote: post #3

This is old news, but we successfully use https://github.com/freels/table_migrator in production not on heroku. It creates a copy of the table, performs the schema changes, copies the data over, then renames the tables for almost (i.e. 1-2 seconds) no downtime.

How does this deal with foreign key references? I thought that if you rename a table the FK references from other tables will still point at the renamed table? Discussion here: http://dev.mysql.com/doc/refman/5.0/en/rename-table.html The Percona pt-online-schema-change tool goes to great lengths to avoid this kind of problem.

Basically, it doesn't - because it doesn't need to. Rails/ActiveRecord doesn't use those either. Problem solved :)

Re: Rails migrations with no downtime

#15

Post by morganpyne »

c3 wrote: post #14
morganpyne wrote: post #9

Earlier quoted context omitted.

How does this deal with foreign key references? I thought that if you rename a table the FK references from other tables will still point at the renamed table? Discussion here: http://dev.mysql.com/doc/refman/5.0/en/rename-table.html The Percona pt-online-schema-change tool goes to great lengths to avoid this kind of problem.

Basically, it doesn't - because it doesn't need to. Rails/ActiveRecord doesn't use those either. Problem solved :)

Interesting. As you have probably guessed by now, I'm not a Rails developer and therefore did not know this. I was surprised to read about this after your comment and find that Active Record went the lowest common denominator route with this and therefore gave up any native foreign key integrity support.

Re: Rails migrations with no downtime

#16

Post by morganpyne »

newhouseb wrote: post #12
morganpyne wrote: post #10

There is another problem unmentioned in this article with his simple table modification example... When you modify a table the DB needs to write the entire table to disk, locking it all the time. Depending on how much data is in the table and how fast your IO is this can be hours and hours of downtime on the live database, for a migration that only took a few seconds locally on your test database. Running migrations…

This is true, but note that the article was written in the context of PostgreSQL instead of MySQL. PostgreSQL can do many types of table migrations with no downtime (dropping columns, renaming, adding columns that default to NULL, and creating indexes). Before I made the switch from MySQL to Postgres I did some quick benchmarks which support these claims on a 5 million row test table: https://gist.github.com/1620133…

Guilty as charged for immediately assuming that the problems of the MySQL family apply to other databases. Thank you for pointing out that PostgreSQL does not necessarily have all the same limitations.

Re: Rails migrations with no downtime

#17

Post by JonnieCache »

morganpyne wrote: post #15
c3 wrote: post #14

Earlier quoted context omitted.

Basically, it doesn't - because it doesn't need to. Rails/ActiveRecord doesn't use those either. Problem solved :)

Interesting. As you have probably guessed by now, I'm not a Rails developer and therefore did not know this. I was surprised to read about this after your comment and find that Active Record went the lowest common denominator route with this and therefore gave up any native foreign key integrity support.

There is nothing to stop you using foreign key constraints with rails, the activerecord migration api includes methods to create them for the major db adapters and there are plugins to automate the process to some extent. It's not the Rails Way™ because it sacrifices some database-agnosticity, and therefore almost noone does it. People achieve the same behaviour with application-level validations in the model.

I'd wager that a lot of the big professional rails deployments are doing FK constraints though.

Re: Rails migrations with no downtime

#18

Post by lectrick »

morganpyne wrote: post #15
c3 wrote: post #14

Earlier quoted context omitted.

Basically, it doesn't - because it doesn't need to. Rails/ActiveRecord doesn't use those either. Problem solved :)

Interesting. As you have probably guessed by now, I'm not a Rails developer and therefore did not know this. I was surprised to read about this after your comment and find that Active Record went the lowest common denominator route with this and therefore gave up any native foreign key integrity support.

I have never missed foreign keys on the projects I've worked on that don't use them.

This is because those projects typically sit on databases that are only expected to be accessed via web API and not directly from some other source.

Which is basically the Rails philosophy. Only let the app talk to the DB, and let the only outside interface to the DB be through the ORM that has to do most of the data validations anyway.

Re: Rails migrations with no downtime

#20

Post by lgierth »

At SoundCloud we developed Large Hadron Migrator [1] to work around the problem of locked tables with ALTER and MySQL. It creates a table with the new schema, copies the data from the "old" table to the new one in chunks (utilizing INSERT IGNORE and triggers) and then switches the two tables. The switching is not yet atomic, as there was a bug with RENAME TABLE.

[1] https://github.com/soundcloud/large-hadron-migrator

Edit: URL

Post reply on HN