I'm currently working on a big port with a lot of content (and growing):

- 1.2m users
- 900k nodes
- 400k files

Running a migration through drush is the only viable thing todo because the UI simple can't handle this. However, with such an amount, the migrate map and message tables fill quickly as well. The first 40k entries go relatively fast, but after that, you're in for a long wait. In case of my files migration, even after 12 hours it wasn't done (I suspect indexes on the source_hash_ids column)

I've been hacking around in MigrateUpgradeDrushRunner a bit and came up with following ideas and code (without hacking core so far) which made importing files run in around 70 minutes.

- if the number of items is bigger then a certain amount, start a new drush process in the background for a single migration plugin using an offset for the query, which is currently handled in migrate_upgrade_query_migrate_alter()
- after each process, move the records from map and message to a temporary table so new background process starts with a clean table (but from the right offset of course)
- there's a threshold for the amount of background processes, and new processes or new migrations won't start before all records from a plugin are processed

Note: code is still a bit rough so far (and will break for derivers like node), I will fix and cleanup (more helper methods and use config instead of constants) in the next couple of weeks and tackling another big problem, which is migration of users. The biggest single problem here is that the password hashing is awfully slow, so my idea is to swap out the user migrate plugin and defer the password hashing to a different plugin which can run after the users have been imported, in a different process. Eventually, this plugin can end up in core as well.

CommentFileSizeAuthor
#2 2708723-2.patch10.52 KBswentel

Comments

swentel created an issue. See original summary.

swentel’s picture

swentel commented
Issue summary: View changes
Status: Active » Needs work
StatusFileSize
new 2708723-2.patch10.52 KB
swentel’s picture

swentel commented
Title: Allow m » Allow to run different background processes
swentel’s picture

swentel commented
Issue summary: View changes
swentel’s picture

swentel commented
Issue summary: View changes
mikeryan’s picture

mikeryan
he/him
Primary language English
Location Pittsfield, MA, USA
commented

Sorry I haven't gotten around to look at this before. I have to say, it looks awfully complex, and potentially brittle (e.g. if it crashes the backup table data never gets merged back, does it?).

I suspect indexes on the source_hash_ids column

For a change of this complexity, I'd want to see more than a suspicion. The root issue in core here is #2688297: File migration slows down and eats more and more memory, eventually stops - what is badly needed there is someone experiencing the file migration slowdown to do some profiling with xhprof.

swentel’s picture

swentel commented

Yeah, I need clean this up more, there's also to many duplicate code here - but it's running fine for now though .. :)

OnkelTem’s picture

OnkelTem commented

We have issues with memory leaks and severe speed degradation. Specifically, there are about 15000 source entities which are migrated into Drupal media entities (w/o files, they are in separate migration) and while at start it yields ~10 records per second, after some time the speed drops to 1 record per 5 seconds.

I couldn't figure out the reason or even find a place in the code where the leak occurs, so I also decided to just split migration into parts.
Initially I began to write a bash script but quickly realized that running it would be pain: it knows nothing about migrations dependencies, about drupal root, environment and other things. So I've switched to coding a drush command instead and then I found this issue. Will check it out and write back.

OnkelTem’s picture

OnkelTem commented

So I've create a drush command for running arbitrary migrations by parallel parts.
Now I'm getting a lots of DEADLOCKS and I don't really understand the reason of such locks.
Using MySQL InnoDB engine.

Example of such a lock:

2016-11-07 12:41:07 7f408152a700InnoDB: transactions deadlock detected, dumping detailed information.
2016-11-07 12:41:07 7f408152a700
*** (1) TRANSACTION:
TRANSACTION 52525198, ACTIVE 0 sec inserting
mysql TABLES IN USE 1, locked 1
LOCK WAIT 5 LOCK struct(s), heap SIZE 1184, 2 ROW LOCK(s), undo log entries 2
MySQL thread id 1012, OS thread handle 0x7f40814e9700, query id 4378 localhost root UPDATE
INSERT INTO users_field_data (uid, langcode, preferred_langcode, preferred_admin_langcode, name, pass, mail, timezone, STATUS, created, changed, access, login, init, default_langcode, im_migration, im_uri) VALUES ('72099', 'en', 'en', NULL, 'some@email.com', '....', 'some@email.com', 'Europe/London', '1', '1250150664', '1472877205', '0', '0', NULL, '1', 'im_user', NULL)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 16221 page no 3 n bits 136 INDEX "PRIMARY" OF TABLE "incisive_default"."users_field_data" trx id 52525198 lock_mode X INSERT intention waiting
Record LOCK, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; ASC supremum;;
 
*** (2) TRANSACTION:
TRANSACTION 52525190, ACTIVE 0 sec inserting
mysql TABLES IN USE 1, locked 1
6 LOCK struct(s), heap SIZE 1184, 3 ROW LOCK(s), undo log entries 2
MySQL thread id 1010, OS thread handle 0x7f408152a700, query id 4362 localhost root UPDATE
INSERT INTO users_field_data (uid, langcode, preferred_langcode, preferred_admin_langcode, name, pass, mail, timezone, STATUS, created, changed, access, login, init, default_langcode, im_migration, im_uri) VALUES ('72098', 'en', 'en', NULL, 'some@email.com', '....', 'some@email.com', 'Europe/London', '1', '1250084691', '1472877204', '0', '0', NULL, '1', 'im_user', NULL)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS SPACE id 16221 page no 3 n bits 136 INDEX "PRIMARY" OF TABLE "incisive_default"."users_field_data" trx id 52525190 lock_mode X
Record LOCK, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; ASC supremum;;
 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 16221 page no 3 n bits 136 INDEX "PRIMARY" OF TABLE "incisive_default"."users_field_data" trx id 52525190 lock_mode X INSERT intention waiting
Record LOCK, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; ASC supremum;;
 
*** WE ROLL BACK TRANSACTION (1)

Currently investigating the reason.

OnkelTem’s picture

OnkelTem commented

Created a sandboxed project Migrate Runner which can run migrations in batches and in parallel:
https://www.drupal.org/sandbox/onkeltem/2827995

It uses simple and sort of canonical strategy of avoiding deadlocks: transactions are restarted.

Welcome to give it a try.

heddn’s picture

heddn
Primary language English
Location Illinois
commented
Status: Needs work » Closed (won't fix)

Let's incubate this in https://www.drupal.org/sandbox/onkeltem/2827995. Once we get some more experience with it, then let's see about merging back here.