CODEWITHSUNDEEP
rubyruby-on-rails-3database-migrationspree
akaDanPaul
akaDanPaul

Reputation: 283

Rails, how to migrate large amount of data?

I have a Rails 3 app running an older version of Spree (an open source shopping cart). I am in the process of updating it to the latest version. This requires me to run numerous migrations on the database to be compatible with the latest version. However the apps current database is roughly around 300mb and to run the migrations on my local machine (mac os x 10.7, 4gb ram, 2.4GHz Core 2 Duo) takes over three days to complete.

I was able to decrease this time to only 16 hours using an Amazon EC2 instance (High-I/O On-Demand Instances, Quadruple Extra Large). But 16 hours is still too long as I will have to take down the site to perform this update.

Does anyone have any other suggestions to lower this time? Or any tips to increase the performance of the migrations?

FYI: using Ruby 1.9.2, and Ubuntu on the Amazon instance.

Upvotes: 19

Views: 4369

Answers (5)

encc
encc

Reputation: 1633

Unfortunaltly the downrated solution is the only one. What is really slow in rails are the activerecord models. The are not suited for tasks like this.

If you want a fast migration you will have to do it in sql.

There is an other approach. But you will always have to rewrite most of the migrations...

Upvotes: 0

pufferfish
pufferfish

Reputation: 291

Take a look at this gem. https://github.com/zdennis/activerecord-import/

data = []
data << Order.new(:order_info => 'test order')
Order.import data

Upvotes: 0

sockmonk
sockmonk

Reputation: 4255

  • Dropping indices beforehand and adding them again afterwards is a good idea.

  • Also replacing .where(...).each with .find_each and perhaps adding transactions could help, as already mentioned.

  • Replace .save! with .save(:validate => false), because during the migrations you are not getting random inputs from users, you should be making known-good updates, and validations account for much of the execution time. Or using .update_attribute would also skip validations where you're only updating one field.

  • Where possible, use fewer AR objects in a loop. Instantiating and later garbage collecting them takes CPU time and uses more memory.

Upvotes: 3

Jim Riordan
Jim Riordan

Reputation: 1428

This answer is more about approach than a specific technical solution. If your main criteria is minimum downtime (and data-integrity of course) then the best strategy for this is to not use rails!

Instead you can do all the heavy work up-front and leave just the critical "real time" data migration (i'm using "migration" in the non-rails sense here) as a step during the switchover.

So you have your current app with its db schema and the production data. You also (presumably) have a development version of the app based on the upgraded spree gems with the new db schema but no data. All you have to do is figure out a way of transforming the data between the two. This can be done in a number of ways, for example using pure SQL and temporary tables where necessary or using SQL and ruby to generate insert statements. These steps can be split up so that data that is fairly "static" (reference tables, products, etc) can be loaded into the db ahead of time and the data that changes more frequently (users, sesssions, orders, etc) can be done during the migration step.

You should be able to script this export-transform-import procedure so that it is repeatable and have tests/checks after it's complete to ensure data integrity. If you can arrange access to the new production database during the switchover then it should be easy to run the script against it. If you're restricted to a release process (eg webistrano) then you might have to shoe-horn it into a rails migration but you can run raw SQL using execute.

Upvotes: 0

Bj&#246;rn Nilsson
Bj&#246;rn Nilsson

Reputation: 3773

Maybe you have already considered this:

  1. Tell the db not to bother making sure everything is on disk (no WAL, no fsync, etc), you now have an in memory db which should make a very big difference. (Since you have taken the db offline you can just restore from a backup in the unlikely event of power loss or similar). Turn fsync/WAL on when you are done.

  2. It is likely that you can do some of the migrations before you take the db offline. Test this in staging env of course. That big user migration might very well be possible to do live. Make sure that you don't do it in a transaction, you might need to modify them a bit.

I'm not familiar with your exact situation but I'm sure there are even more things you can do unless this isn't enough.

Upvotes: 0

Related Questions