XF 2.1 Batch update users results in MySQL overload

[duderuud]

I want to remove a LOT of (spam)users with zero posts. Looks like the process is a query so large that the database crawls to a halt.

The xf_jobs table has a lot of data and empty that table speeds things up again. But that is a dirty workaround because it cleans up afaik.
Is there a way to run that job with low priority? I don;t care it takes hours and hours, as long as the DB keeps responsive...

 

[Chromaniac]

If you are subscribed to @AndyB addon package, he has a pretty cool addon for this which deletes x number of users on a configured schedule.

Or maybe you can limit the number of users being deleted in one go by filtering by registered date. This is what Andy's addon does. Basically delete accounts older than 365 days with zero posts.

 

[Paul B]

Just refine the criteria for the batch update to reduce the amount of matches.

The registered between dates should work best for that.

 

[duderuud]

Already tried that. Even with deleting 115 users the database struggles...

 

[Paul B]

That sounds like a poorly configured server.

I just did a batch delete of 1,000 users on localhost (on a Windows machine which is over 10 years old) and it was done in 2-3 seconds.

 

[duderuud]

Could be.

Or maybe the difference is that my DB is over 30GB in size with over 30 million posts, 250,000 users and about 700 active users online...

 

[duderuud]

Did some searching. Seems that it could be an index problem...

Fixed - Add an index to `xf_reaction_content`.`reaction_user_id`

I had a similar issue as described here: https://xenforo.com/community/threads/mass-errors-when-batch-deleting.172453/ Running this query with a random unused user id took bout 40 seconds. This query runs with ever user deletion: UPDATE IGNORE `xf_reaction_content` SET `reaction_user_id` = 0...

xenforo.com

 

[duderuud]

Update 2: Upgraded my beta forum tot 2.2 b3.

I can confirm the the comment of @Mike in the topic above. I successfully deleted 24k users without any issues.

PS. The beta server has half the amount of cpu's and memory compared to the production DB server.