The 2026 Drupal Business Survey is open. Your response will help shape the future of the Drupal business ecosystem.We're trying to configure a migration which will not only import but continually update data from a remote database. We're running into issues with things which will be imported as nodes, as there's about 150k of them in the remote database, and it's seeming like Migrate is wanting to fetch all of that data before it'll start importing/updating a single node. I can get it to run reasonably fast if I add a range() method call to the select query object that we pass to new MigrateSourceSQL() so that we limit ourselves to only the first 10k items, but based on the timing of that, it will take about 90 minutes for the whole 150k to run - if that successfully completes at all. (The --limit parameter on the Drush command and its GUI equivalent only seems to work on the downloaded data; it doesn't seem to be adding a range() to the query itself if I'm not mistaken.)
Is there some way that we can have Migrate run our migration in batches of, say, 5k or 10k items, but then intelligently query for the next batch the next time it runs? I guess I could hack this functionality on to my Migrate class myself, using variable_set() or the like to track which batch we're on, but it'd sure save me a lot of time if functionality like that were already in Migrate and I'm just overlooking it somehow.
| Comment | File | Size | Author |
|---|---|---|---|
| #10 | 2296187-10_2.5backport-do-not-test.patch | 2.34 KB | cweagans |
| #9 | 2296187_09-batched_query_support.patch | 2.44 KB | cweagans |
Comments
Comment #1
Garrett Albright commentedComment #2
Garrett Albright commentedComment #3
moshe weitzman commentedIts been a while since I wrote a migration, but perhaps your migration fails to specify a highwater field? See the beer and wine example migrations for examples.
Comment #4
Garrett Albright commentedOh, so that's what highwater fields mean… If I'm able to set that manually to "the highest updated value in the current list of migrated items" instead of "now," then yeah, it looks like I can use that to only do a reasonable number of items per run… I'll play with that and report back.
Comment #5
Garrett Albright commentedOkay, highwater fields are set to the value of the "last" migrated item rather than "now," so I just had to add an orderBy() to the query I pass to MigrateSourceSQL() and that seems to be doing the trick. Thanks, moshe.
Comment #6
cweagansIs there a way to force migrate to download "chunks" of data at a time rather than pulling down the entire dataset into memory?
The highwater mark is helpful for doing periodic imports, but it seems like Migrate should perform well without having to do that. That is, I should be able to just do `drush mi MyMigration`, and no matter how many records the source has for that migration, it should work.
Happy to work on this if it's something that a) migrate doesn't already do somehow and b) it's something that you think migrate should do.
Comment #7
cweagansAlso, full disclosure: I'm working on the same project as Garrett.
Comment #8
cweagansI didn't see any open issues for this, so I'm going to repurpose this issue.
Right now, the mssql source is the only plugin I see that will do a batched select, and it looks like that's mainly because the mssql php extension allows for it in a really easy way. We should do the same in the regular sql source class.
I'm working on this now. Shouldn't be too difficult.
Comment #9
cweagansHow about this?
Comment #10
cweagansHere's a backport to 2.5 for anyone that's still using the latest stable release :)
Comment #11
dave reidI've been testing this out on a large migration and has helped resolve some errors we were getting due to hitting an Acquia Cloud database too hard.
Comment #12
gstout commentedThis is awesome and solved a 500k import problem for me with Pantheon. No reason this shouldn't be rolled into the main branch.
Comment #13
bjalford commentedTested this patch and it works well!
Comment #15
mikeryanCommitted, thanks!