Cannot Move or Delete Posts on Large XenForo Forum, Need Advice

[sunsky7]

We have run into an issue where we can't move or delete posts or threads on a large XenForo install and I would like to get some advice.

We are running XenForo v2.2.9 with 50+ million posts and 2+ million registered users.

When we try to move or delete posts using the inline moderation (even with only one post selected), we get a MySQL timeout with the following information:

XF\Db\Exception: MySQL query error [1205]: Lock wait timeout exceeded; try restarting transaction src/XF/Db/AbstractStatement.php:230

In the XenForo admin Server Error logs, we can see several normal user functions (users just browsing the site) are triggering similar errors, although those seem to be far less frequent. Here is another example of a user getting a timeout.

Stack trace

            UPDATE xf_user
            SET reaction_score = reaction_score + ?
            WHERE user_id = ?
        
------------
XF\Db\Exception: MySQL query error [1205]: Lock wait timeout exceeded; try restarting transaction src/XF/Db/AbstractStatement.php:230 

#0 src/XF/Db/Mysqli/Statement.php(198): XF\Db\AbstractStatement->getException('MySQL query err...', 1205, 'HY000')
#1 src/XF/Db/Mysqli/Statement.php(79): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1205, 'HY000')
#2 src/XF/Db/AbstractAdapter.php(96): XF\Db\Mysqli\Statement->execute()
#3 src/XF/Entity/ReactionContent.php(194): XF\Db\AbstractAdapter->query('
            UPDATE xf_u...', Array)
#4 src/XF/Entity/ReactionContent.php(117): XF\Entity\ReactionContent->adjustUserReactionScore(5521640, 1)
#5 src/XF/Mvc/Entity/Entity.php(1270): XF\Entity\ReactionContent->_postSave()
#6 src/XF/Repository/Reaction.php(191): XF\Mvc\Entity\Entity->save()
#7 src/XF/Repository/Reaction.php(144): XF\Repository\Reaction->insertReaction(3, 'post', 95477221, Object(XF\Entity\User), true, false)
#8 src/XF/ControllerPlugin/Reaction.php(67): XF\Repository\Reaction->reactToContent(3, 'post', 95477221, Object(XF\Entity\User), true)
#9 src/XF/ControllerPlugin/Reaction.php(21): XF\ControllerPlugin\Reaction->actionToggleReaction(Object(XFES\XF\Entity\Post), 'posts/reactions', 'posts')
#10 src/XF/ControllerPlugin/Reaction.php(14): XF\ControllerPlugin\Reaction->actionReact(Object(XFES\XF\Entity\Post), 'posts', 'posts/react', 'posts/reactions')
#11 src/XF/Pub/Controller/Post.php(381): XF\ControllerPlugin\Reaction->actionReactSimple(Object(XFES\XF\Entity\Post), 'posts')
#12 src/XF/Mvc/Dispatcher.php(352): XF\Pub\Controller\Post->actionReact(Object(XF\Mvc\ParameterBag))
#13 src/XF/Mvc/Dispatcher.php(259): XF\Mvc\Dispatcher->dispatchClass('XF:Post', 'React', Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\Post), NULL)
#14 src/XF/Mvc/Dispatcher.php(115): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\Post), NULL)
#15 src/XF/Mvc/Dispatcher.php(57): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#16 src/XF/App.php(2352): XF\Mvc\Dispatcher->run()
#17 src/XF.php(524): XF\App->run()
#18 index.php(20): XF::runApp('XF\\Pub\\App')
#19 {main}

All of these errors show "AbstractStatement.php:230".

The forum runs pretty quickly other than the issue with not being able to delete or move posts or threads.

Does anyone have experience with this issue or suggestions for resolving it? We've tried increasing resources (memory and CPU) but it doesn't seem to help. There must be some XenForo specific optimizations that we're missing to resolve this?

Thank you for your help.

 

[fly]

Have you tried running MySQLTuner?

 

[KSA]

2+ million registered users

2 million! wow that's quite a community. I would love to take a peek if you don't mind sharing the link.

 

[sunsky7]

Have you tried running MySQLTuner?

Thank you for the suggestion. We have a very experienced server administrator managing our servers, and they have tried various MySQL optimizations without any luck. Since this issue is specific to XenForo, and the forum runs fast when we aren't trying to move or remove posts, we are hoping to find someone with XenForo experience who has a suggestion about how this might be resolved.

 

[KSA]

we are hoping to find someone with XenForo experience who has a suggestion about how this might be resolved.

You can submit a support ticket via your xf account for xf team to look into this issue for you.

 

[KSA]

You can submit a support ticket via your xf account for xf team to look into this issue for you.

Customer area login

xenforo.com

 

[sunsky7]

You can submit a support ticket via your xf account for xf team to look into this issue for you.

Thank you, I will do that now.

If anyone else with a large XenForo sees this thread, I'm still interested in replies here as well.

 

[briansol]

line 230 of abstract statement is just a wrapper to perform the execution so it's not the actual error, as you probably are aware.


you seem to be having an issue with the reactions update as part of the move.

I assume with that many users, the reactions table is huge. Perhaps run an optimize there? Not very helpful, sorry.

 

[fly]

Thank you for the suggestion. We have a very experienced server administrator managing our servers, and they have tried various MySQL optimizations without any luck. Since this issue is specific to XenForo, and the forum runs fast when we aren't trying to move or remove posts, we are hoping to find someone with XenForo experience who has a suggestion about how this might be resolved.

Hopefully the XF team can get you some answers. If they do, please update us. Otherwise, SQL tuning can be a very fine art. No disrespect to your admin, but I'd say your next step would be to find an actual artist.

 

[KSA]

Thank you, I will do that now.

If anyone else with a large XenForo sees this thread, I'm still interested in replies here as well.

I also have a very large XF community but haven't come across this issue before. It looks like it is related to the XF reaction is somehow preventing other transactions to run. I would generally recommend that you run this query

SHOW PROCESSLIST;

This will tell you what mysql transaction is standing in the middle causing this issue.
Once you find the culprit, kill it by running

Kill (transaction ID here);

Then see if that fixes the issue.

 

[Xon]

This is a design flaw with XenForo, the inline mod tools do not interact well with large number of users, threads or posts.

The actual problem is it latches on the xf_forum tables which cause the xf_post and xf_user tables to hold locks for way too long.

This is a related bug report:

Merging Post performance issues

When merging posts the updateSourceData function ends up doing a lot of work inside a transaction which likely is not required. This is notable on a very busy forum when merging posts in the same thread, as this can cause deadlocks and cause replying to faill, for the entire node the thread...

xenforo.com

(I didn't check deleting, as this is something my site rarely does for various reasons).