XF 1.4 Deleting users is very slow

[insocial]

We are in the process of cleaning up our user database. Currently have about 700,000 user accounts. We want to delete around 300,000. The problem is that it takes approximately 20-25 seconds to delete each user. Does anyone know of a way to speed this up or how to figure out why it is taking so long?

 

[Mike]

This is probably somewhat expected. There is a lot of content that needs to be disassociated with a user when you delete them. The more content in the DB, the more time it will take.

What's the site?

 

[insocial]

These users do not have any content but I suppose the query has to iterate through all tables that could contain content. I wonder if maybe we could limit that knowing that there isn't data to be removed in these cases. Any other ideas on how to speed this up?

 

[Mike]

Correct, it has work through all the tables. Look at XenForo_Model_User::$userContentChanges for everything it goes through. You may be able to temporarily remove some of these if you're 100% positive they don't have any content in those tables.

 

[insocial]

Correct, it has work through all the tables. Look at XenForo_Model_User::$userContentChanges for everything it goes through. You may be able to temporarily remove some of these if you're 100% positive they don't have any content in those tables.

Thanks for the tip. Since we are removing zero post users, we could comment out xf_post, for example. Since this is the largest table, it would seem that this would speed things up. After doing a lot of experimentation, it doesn't have a big impact on the speed though. In fact, removing all of the tables in XenForo_Model_User::$userContentChanges still doesn't speed up the user deletion very much. What else is happening, besides iterating through those tables, that would take time? I'm looking in XenForo_Deferred_UserAction for clues.

 

[Mike]

2 other things to check mostly:

1. XenForo_DataWriter_User::_postDelete(). There are a bunch of tables that we delete data from here, though most of these should only delete a few records and they should be indexed. It may be more difficult to remove these entries as it's difficult to guarantee there are no records in these, even if the user didn't appear to do anything.
2. Add-ons. Add-ons may add both to the data being deleted and the data being kept but with a blanked out user_id. If any of these don't use indexes, that may make this slower.

It may be worth looking at the MySQL process list while the deletes are running. That'll probably give you a better idea if it's a long query or the combination of all queries that's causing the problem.

 

[insocial]

So, just an update. We isolated the test database and viewed the processes active during the delete. There were calls to tables without an index on the where column. By adding these indexes:

ALTER TABLE `xf_conversation_master` ADD INDEX ( `last_message_user_id` );
ALTER TABLE `xf_conversation_user` ADD INDEX ( `last_message_user_id` );
ALTER TABLE `xf_thread` ADD INDEX ( `last_post_user_id` );
ALTER TABLE `xf_warning` ADD INDEX ( `warning_user_id` );
ALTER TABLE `xf_report` ADD INDEX ( `last_modified_user_id` );

the speed of the delete was increased 20x. This is with leaving all XenForo_Model_User::$userContentChanges calls intact.

With regard to addons, we have the sonnb XenGallery installed. We added indexes to 4 tables there but also had to modify some code that was doing a find/replace on large tables for each user deletion. This increased the speed of user deletion 100x.

Once the batch deletion is complete we will drop the indexes added and undo the code changes.

Just posting this in case it will help someone having a similar problem of needing to delete a large number of users from a large forum. The tables will vary depending on the areas of usage of your forum.

First, isolate the database and run from shell:

mysqladmin -u db_user -p -i 3 processlist --verbose

Then add indexes based on the slow queries. Delete your users and drop the indexes.