I need to mass delete about 600k users from a drupal install, I have the UIDS, and built a drush script to do it, however the problem is that this is a live site, so it seems that user_delete_multiple acquires a lock for the whole duration ( even split into chunks of 100 it takes too long). so iterating over the array one by one (thus releasing the lock in between and allowing normal operation), and deleting at a rate of 1.5 users per second. 600k --> going to take about a week.

Plus I cannot parallelize this because of the lock on the table (tried it , it makes it slower). what other options to I have? ( I looked into deleting from database directly but there are lots of modules that implemente user_delete and entity_delete so I might forget something...

I'm looking maybe at using the same script but removing the lock on the table... do now know how to to this, but could be an option.

[EDIT] just edited the script a but, chunking in size 5 arrays, and using user_delete_multiple. this allows me to save 4 calls to module_invoke_all which did gain me a bit of speed . I am currently deleting at 5 users in 4, 4.5 s). this also allows me to maintain the lock for less time and also allow site to operate. waitig to see if site is still usable with this fix..

  • 2
    Is maintenance mode a possibility?
    – mpdonadio
    May 15 '14 at 16:37
  • yes if I do it during the off hours ( AKA night)
    – cromestant
    May 15 '14 at 16:39
  • Is it any faster during maintenance mode?
    – mpdonadio
    May 15 '14 at 16:55
  • 1
    It seems that doing the deletes in chunks during the off hours is your best option. Trying to cheat around locks on a live site sounds very dangerous. It shouldn't matter if it takes a week or more to get rid of these users if there is a period of the day with less activity, when the performance hit will not affect many users. May 15 '14 at 17:00
  • @MPD I've not tried it. Will have to do so tonight.
    – cromestant
    May 15 '14 at 17:01

I would do a few things.

Slow deletions could be an indication of a poorly tuned MySQL instance (or other database server). I would run MySQLTuner (or whatever is the equivalent in your case) on your server and make sure you have a decent configuration.

Second, I would do this in maintenance mode. Your slowness may the result of the additional traffic on the site, and difficulty acquiring the locks.

If I were in your shoes, I would write a shell script that does something like

drush @thesite variable-set -y --always-set maintenance_mode 1
drush @thesite -u 1 scr /path/to/a/drush/script.php
drush @thesite variable-delete -y --exact maintenance_mode

and then place this in a nightly crontab. Inside /path/to/a/drush/script.php, you would write your code to delete a batch of users (say 100 or so).

This will then delete your users in small batches while in maintenance mode. This will minimize the downtime on your site.

Monitor progress and remove the crontab entry when you are done.

  • This is a nice suggestion. MYSQL is an Amazon RDS, so finely tuned I guess. I t is a very high traffic site, so yes difficulty acquiring lock is the definite culprit. Still looking at another option to see if viable, if not I guess during the night the deletions will be quicker
    – cromestant
    May 15 '14 at 20:22
  • marking this as the answer as it is safe and correct way to do this. however I did not see improvement with the maintenance mode, and myself am opting for a more "dangerous" approach
    – cromestant
    May 16 '14 at 21:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.