SQL-processes running wild

[multibam]

Affected version

2.3.6

Ok, so the xenforo user is starting up a new process every 15 minutes without closing them, soon eating up all the server CPU. It looks like this:

MariaDB [(none)]> show processlist;
+---------+-----------+-----------+-----------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+---------+-----------+-----------+-----------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+----------+
| 2920748 | forum_xen | localhost | forum_xen | Execute | 15934 | Sending data | SELECT xf_post., xf_thread_Thread_1., xf_forum_Forum_2., xf_node_Node_3., `xf_user_User_ | 0.000 |
| 2945918 | forum_xen | localhost | forum_xen | Execute | 15030 | Sending data | SELECT xf_post., xf_thread_Thread_1., xf_forum_Forum_2., xf_node_Node_3., `xf_user_User_ | 0.000 |
| 2973439 | forum_xen | localhost | forum_xen | Execute | 14128 | Sending data | SELECT xf_post., xf_thread_Thread_1., xf_forum_Forum_2., xf_node_Node_3., `xf_user_User_ | 0.000 |
| 2999817 | forum_xen | localhost | forum_xen | Execute | 13229 | Sending data | SELECT xf_post., xf_thread_Thread_1., xf_forum_Forum_2., xf_node_Node_3., `xf_user_User_ | 0.000 |
| 3026923 | forum_xen | localhost | forum_xen | Execute | 12328 | Sending data | SELECT xf_post., xf_thread_Thread_1., xf_forum_Forum_2., xf_node_Node_3., `xf_user_User_ | 0.000 |
| 3053687 | forum_xen | localhost | forum_xen | Execute | 11427 | Sending data | SELECT xf_post., xf_thread_Thread_1., xf_forum_Forum_2., xf_node_Node_3., `xf_user_User_ | 0.000 |
| 3081369 | forum_xen | localhost | forum_xen | Execute | 10527 | Sending data | SELECT xf_post., xf_thread_Thread_1., xf_forum_Forum_2., xf_node_Node_3., `xf_user_User_ | 0.000 |
| 3109846 | forum_xen | localhost | forum_xen | Execute | 9625 | Sending data | SELECT xf_post., xf_thread_Thread_1., xf_forum_Forum_2., xf_node_Node_3., `xf_user_User_ | 0.000 |
| 3136554 | forum_xen | localhost | forum_xen | Execute | 8725 | Sending data | SELECT xf_post., xf_thread_Thread_1., xf_forum_Forum_2., xf_node_Node_3., `xf_user_User_ | 0.000 |
| 3163576 | forum_xen | localhost | forum_xen | Execute | 7825 | Sending data | SELECT xf_post., xf_thread_Thread_1., xf_forum_Forum_2., xf_node_Node_3., `xf_user_User_ | 0.000 |
| 3189889 | forum_xen | localhost | forum_xen | Execute | 6924 | Sending data | SELECT xf_post., xf_thread_Thread_1., xf_forum_Forum_2., xf_node_Node_3., `xf_user_User_ | 0.000 |
| 3215911 | forum_xen | localhost | forum_xen | Execute | 6021 | Sending data | SELECT xf_post., xf_thread_Thread_1., xf_forum_Forum_2., xf_node_Node_3., `xf_user_User_ | 0.000 |
| 3242564 | forum_xen | localhost | forum_xen | Execute | 5120 | Sending data | SELECT xf_post., xf_thread_Thread_1., xf_forum_Forum_2., xf_node_Node_3., `xf_user_User_ | 0.000 |
| 3268641 | forum_xen | localhost | forum_xen | Execute | 4219 | Sending data | SELECT xf_post., xf_thread_Thread_1., xf_forum_Forum_2., xf_node_Node_3., `xf_user_User_ | 0.000 |
| 3296648 | forum_xen | localhost | forum_xen | Execute | 3319 | Sending data | SELECT xf_post., xf_thread_Thread_1., xf_forum_Forum_2., xf_node_Node_3., `xf_user_User_ | 0.000 |
| 3323530 | forum_xen | localhost | forum_xen | Execute | 2417 | Sending data | SELECT xf_post., xf_thread_Thread_1., xf_forum_Forum_2., xf_node_Node_3., `xf_user_User_ | 0.000 |
| 3348913 | forum_xen | localhost | forum_xen | Execute | 1516 | Sending data | SELECT xf_post., xf_thread_Thread_1., xf_forum_Forum_2., xf_node_Node_3., `xf_user_User_ | 0.000 |
| 3378855 | forum_xen | localhost | forum_xen | Execute | 616 | Sending data | SELECT xf_post., xf_thread_Thread_1., xf_forum_Forum_2., xf_node_Node_3., `xf_user_User_ | 0.000 |

...and it's just going on until I kill the processes or the server crashes. It's a big busy board with with XenForo Enhanced Search 2.3.5, but I guess it has something to do with Update view counters (that is somehow bugging us in other ways too).

Does anyone have any idea how to fix this?

Thanks,

multibam

 

[ekool]

There is an add-on to move the View Counters to Redis... you could try and see if that fixes it?

X

Redis View Counters

Aug 9, 2017

Use redis for view counters rather than MySQL

• Xon
• performance redis threads
• Add-ons [2.x]

 

[Xon]

I'ld recommend using:

show full processlist;

This will dump the entire SQL query which is more helpful for figuring out what is happening

 

[MentaL]

Releem is a good tool to log stuff and optimize long term too.

 

[multibam]

This will dump the entire SQL query which is more helpful for figuring out what is happening

Thank you, but this is just making me even more confused

| 302769 | forum_xen | localhost | forum_xen | Execute | 29278 | Sending data | SELECT xf_post., xf_thread_Thread_1., xf_forum_Forum_2., xf_node_Node_3., xf_user_User_4., xf_permission_combination_PermissionCombination_5.
FROM xf_post
LEFT JOIN xf_thread AS xf_thread_Thread_1 ON (xf_thread_Thread_1.thread_id = xf_post.thread_id)
LEFT JOIN xf_forum AS xf_forum_Forum_2 ON (xf_forum_Forum_2.node_id = xf_thread_Thread_1.node_id)
LEFT JOIN xf_node AS xf_node_Node_3 ON (xf_node_Node_3.node_id = xf_forum_Forum_2.node_id)
LEFT JOIN xf_user AS xf_user_User_4 ON (xf_user_User_4.user_id = xf_post.user_id)
LEFT JOIN xf_permission_combination AS xf_permission_combination_PermissionCombination_5 ON (xf_permission_combination_PermissionCombination_5.permission_combination_id = xf_user_User_4.permission_combination_id)
WHERE (xf_post.message_state = 'visible') AND (xf_thread_Thread_1.discussion_state = 'visible') AND (xf_forum_Forum_2.find_new = 1) AND (xf_post.post_date > 1738862304)
ORDER BY xf_post.post_date DESC
LIMIT 25 | 0.000 |
| 370592 | forum_xen | localhost | forum_xen | Execute | 27214 | Sending data | SELECT xf_post., xf_thread_Thread_1., xf_forum_Forum_2., xf_node_Node_3., xf_user_User_4., xf_permission_combination_PermissionCombination_5.
FROM xf_post
LEFT JOIN xf_thread AS xf_thread_Thread_1 ON (xf_thread_Thread_1.thread_id = xf_post.thread_id)
LEFT JOIN xf_forum AS xf_forum_Forum_2 ON (xf_forum_Forum_2.node_id = xf_thread_Thread_1.node_id)
LEFT JOIN xf_node AS xf_node_Node_3 ON (xf_node_Node_3.node_id = xf_forum_Forum_2.node_id)
LEFT JOIN xf_user AS xf_user_User_4 ON (xf_user_User_4.user_id = xf_post.user_id)
LEFT JOIN xf_permission_combination AS xf_permission_combination_PermissionCombination_5 ON (xf_permission_combination_PermissionCombination_5.permission_combination_id = xf_user_User_4.permission_combination_id)
WHERE (xf_post.message_state = 'visible') AND (xf_thread_Thread_1.discussion_state = 'visible') AND (xf_forum_Forum_2.find_new = 1) AND (xf_post.post_date > 1738864369)
ORDER BY xf_post.post_date DESC
LIMIT 25 | 0.000 |