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:
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.
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.
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:
Code:
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.
Code:
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.