Break Up Big Migrations with WillPaginate
December 9th, 2008
Have a big migration that would take too long to complete? It would be too bad to inadvertently bring down your whole server by loading a big dataset into memory.
A very simple solution is to use will_paginate. I was just combing through the plugin code, and found out that will_paginate has a nice method called paginated_each, which “Iterates through all records by loading one page at a time. This is useful for migrations or any other use case where you don’t want to load all the records in memory at once.”
So instead of writing for article in Article.find(all), you’re better off using Article.paginated_each do |article|. Your migrations will finish faster and with less memory usage.
3 Responses to “Break Up Big Migrations with WillPaginate”
Sorry, comments are closed for this article.
December 14th, 2008 at 09:34 AM
Be careful with that, large offsets can take an incredibly long time to load. We once had an operation over a few million rows that ran in 100 record windows and once the offset got into the millions it started to take 20 minutes per query. After a week we had to cancel the thing and start over with this method: http://weblog.jamisbuck.org/2007/4/6/faking-cursors-in-activerecord
December 15th, 2008 at 12:54 PM
Ryan,
Great tip!
One other thing I did recently to migrate millions of rows at a time was to setup a transaction within the paginated_each block. This groups all of the SQL work for each page into a single transaction. Otherwise, you incur a transactional overhead for every SQL insert/update/delete you perform, for each record within each page you are processing.
Doing this will commonly cut down work from hours to minutes, esp with MySql where transactions are very expensive when using the innodb engine.
December 17th, 2008 at 12:59 PM
Holy cow – Thanks for that tip Tobias