kalenjordan/magerun-addons — pull request #7 by jonathanvaughn (2014-04-04 01:05:17+00:00)
Title: Massive customer:anon speedup by leveraging MySQL to do the heavy liftin...

We've been using your wonderful customer:anon function for awhile, but recently we've had some rather large (in the most recent case, over 500,000 rows just on customer_entity) customer DBs to anonymize for development work, and it can take a long time. This most recent case, we decided to find a way to do this faster, because from eyeballing the progress we were guessing at anywhere from 60 to 100 hours to complete just customer_entity. D:

From checking with XDebug, it appears nearly all the time was spent waiting on MySQL. We did some experimenting and found some various different methods, and then tested them, etc. It seems that MySQL spends a lot of time searching for the row and/or compiling the query vs the actual time to change the row, because of how much a difference handing one query per table to the server (and letting it do all the work) makes vs the original implementation (handling each email address one at a time as a separate query).

This speedup change still keeps the emails consistent across tables, while still making them non-recoverable (well, at least for any trivial MD5 attack, since the salt is random and not stored) and (very probably - the nature of hashes being what they are, there's a small chance of collision) unique.

We had already used this technique manually on the database in question and it took just a few minutes, though we've yet to do a full scale test again and I didn't actually time it, so I can't say yet what the full DB speedup is, but we did some testing with only 10,000 rows per table:

* The original implementation took 42+ minutes (most of it spent waiting on the mysql server - actual user + system time was less than 50s).
* This implementation took less than 5 seconds realtime, of which about 1.5s was user+system, the rest was on the mysql server. The speedup for this test was ~700x. :D

I'm currently re-importing the original DB into my test DB with the full ~half a million rows+, but it's going to take awhile so I'll do a full scale test tomorrow. Meanwhile, here's a pull request :D