Awaiting feedback SQL-processes running wild

M

multibam

Member
Licensed customer
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
 
Last edited:
I'ld recommend using:
SQL: 
show full processlist;
This will dump the entire SQL query which is more helpful for figuring out what is happening
 
Xon said:
This will dump the entire SQL query which is more helpful for figuring out what is happening
Click to expand...

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 |
 
Automated triage: Awaiting feedback

Thank you for reporting this issue. The stuck query comes from the activity summary email's latest posts section, not the view counter task. The job is retried every 15 minutes when a run doesn't finish, so the queries pile up. We need a little more detail to work out why this query is so slow on your server.

  • Please run EXPLAIN on the full SELECT you posted (the one with the post_date cutoff and LIMIT 25) and share the output, along with your MariaDB version and roughly how many rows xf_post and xf_thread have.
  • Does the problem stop if you temporarily disable the activity summary email option (or disable its 'Latest posts' section)?
  • Have you changed the activity summary settings from the defaults, e.g. the minimum days since last activity, or the 'Latest posts' section's node selection or sort order (reaction score / vote score)?
  • How are jobs run on your site: activity-based (browser) or a server cron calling the CLI job runner? What timeouts apply to PHP requests (max_execution_time, PHP-FPM request_terminate_timeout)?

This is an automated assessment. A member of staff may review and update it.
 
Back
Top Bottom