XF 2.2 Pruned 25k members and now the server is grinding to a halt

dethfire

dethfire

Well-known member
The initial processing ended an hours ago but the site is still being crushed. The jobs table has 20k entries for XF:UserDeleteCleanUp. Is it working through these or do I have a bigger problem?

I am also getting the
"There are scheduled jobs outstanding which have not run. Jobs may not be getting triggered when expected." error
 
Sort by date Sort by votes
Batch updating (users) is extremely slow. Even on a powerful server it can take ages to update.

I had to remove a few thousand posts in a staging environment and that took a few hours in total.
 
Upvote 0 Downvote
Sadly, this is normal. If you look at MySQL you will almost certainly find out it is stuck cleaning up the user reactions table. It seems to be exponential depending on the number of users you delete as the batch cleanup job kicks in sometime after the users have actually been deleted by your batch update.

I would suggest doing in much smaller batches of less than 100 at a time if you don't want to run into problems. Also ensure you wait between batches to allow the cleanup job to run.
 
Upvote 0 Downvote
Pruning 50k users has just resulted in a downtime for us because of the resulting XF:UserDeleteCleanUp jobs and the expensive query in XF\Reaction\AbstractHandler::updateRecentCacheForUserChange in particular. Our xf_post table as roughly 28,000,000 rows and there are roughly 200,000 rows in xf_reaction_content with reaction_user_id = 0. Each resulting query took roughly 2 seconds and since many were done in parallel I guess the jobs were continuously locking (significant parts of) the xf_post table such that web requests started to pile up.

It would be nice if this could be improved. Maybe this could also be improved by not executing multiple jobs in parallel (which might be easier to implement when running jobs via CLI).
 
Upvote 0 Downvote
Steffen said:
Pruning 50k users has just resulted in a downtime for us because of the resulting XF:UserDeleteCleanUp jobs and the expensive query in XF\Reaction\AbstractHandler::updateRecentCacheForUserChange in particular. Our xf_post table as roughly 28,000,000 rows and there are roughly 200,000 rows in xf_reaction_content with reaction_user_id = 0. Each resulting query took roughly 2 seconds
Click to expand...
Yeah, the query is rather bad - it unconditionally updates all rows for the target user id even if there is nothing to update.
Adding a condition should sigificantly speed up that query.

xenforo.com

Inefficient query in XF\ReactionAbstractHandler::updateRecentCacheForUserChange

\XF::db()->query(" UPDATE ( SELECT content_id FROM xf_reaction_content WHERE content_type = ? AND reaction_user_id = ? ) AS temp INNER JOIN {$table} AS reaction_table ON (reaction_table.`$primaryKey` = temp.content_id) SET reaction_table.`{$recentField}` =...
xenforo.com xenforo.com
 
Upvote 0 Downvote
My suggestion is delete their posts & contents (profile posts) etc, before deleting multiple users.

Even using "spam" tool at only 1 user with more than 1000 posts can cause server halt (in my server at least :D)
 
Upvote 0 Downvote
I don't know what if any database changes were made for 2.2.15, but when we upgraded to that the deadlocks and userdeletecleanup issues seemed to disappear for a while. It may just have been a coincidence as we seem to be back to having the problems again now during the regular housekeeping.
 
Upvote 0 Downvote
This is core functionality and it's unacceptable that XF does not respond.

Deleting 250 members gives me error
  • XF\Db\Exception: Job XF:UserDeleteCleanUp: MySQL query error [1205]: Lock wait timeout exceeded; try restarting transaction
  • src/XF/Db/AbstractStatement.php:230
 
Upvote 0 Downvote
Bugger that...😳 👀

If i had that many users and i had to prune a heap of them i'd be looking at the spammers and porn bots that keep registering.

I'd look at @Xon's sign up and abuse addon along with his standard library.

I'd look at the captcha and set up the cloudflare turnstile one.
 
Upvote 0 Downvote
IMHO there is no need for a support ticket nor would I epexct that this could help:
The main issue is that the algorithem to rename & delete users is inefficient, especially if there are many reactions by deleted users.

K

Confirmed Thread 'Service XF:User\ChangeContent runs twice when renaming a user upon deletion'

This is a rather complicated bug, but I hope it's understandable.

Method XF\Service\User\Delete::delete sets entity options to avoid running jobs XF:UserRenameCleanup and XF:UserDeleteCleanup when renaming a user before deletion:
PHP: 
if ($this->renameTo)
{
    $user->reset();
    $user->setOption('admin_edit', true);
    $user->setOption('enqueue_rename_cleanup', false);
    $user->setOption('enqueue_delete_cleanup', false);

    $user->set('username', $this->renameTo);

After deletion,. those jobs are enqueued in method runPostDeleteJobs
PHP: 
if...
  • Like
 
Upvote -1 Downvote
dethfire said:
This is core functionality and it's unacceptable that XF does not respond.
Click to expand...

I shouldn't really have to say this but I will for your benefit and anyone else reading.

This is a community driven support forum.

If you want an official XF response, submit a ticket.

That has been the case for the last 14 years.
 
Upvote 1 Downvote
You must log in or register to reply here.
Back
Top Bottom