XF 2.2 Very slow query causing server issues.

[jeffatrackaid]

I have a forum with 1.6M threads, 1M members, and 15M messages. Running on an AWS cloud instance with MariaDB 10.5.

The site was migrated from VBulletin.

Does someone know what site feature/page triggers this query:

SELECT COUNT(*)
                                FROM `xf_thread` FORCE INDEX (`last_post_date`)

                                WHERE (`xf_thread`.`discussion_state` = 'visible') AND (`xf_thread`.
` <> 60) AND (`xf_thread`.`node_id` <> 66) AND (`xf_thread`.`node_id` <> 68) AND (`xf_thread`.`node_
ead`.`node_id` <> 108) AND (`xf_thread`.`node_id` <> 120) AND (`xf_thread`.`node_id` <> 121) AND (`x
160) AND (`xf_thread`.`node_id` <> 168) AND (`xf_thread`.`node_id` <> 175) AND (`xf_thread`.`node_id
ad`.`node_id` <> 203) AND (`xf_thread`.`node_id` <> 204) AND (`xf_thread`.`node_id` <> 209) AND (`xf
28) AND (`xf_thread`.`node_id` <> 230) AND (`xf_thread`.`node_id` <> 240) ......
 AND (`xf_thread`.`node_id` IN (2, 37, 38, 39, 40, 41,
8, 80, 82, 83, 90, 95, 96, 97, 98, 99, 100, 101, 102, 111, 115, 116, 119, 152, 154, 155, 156, 157, 1
 174, 177, 178, 180, 181, 182, 183, 185, 188, 189, 190, 192, 196, 197, 198, 199, 200, 205, 207, 208,
2, 233, 234, 235, 236, 239, 241, 242, 243, 244, 245, 246, 249, 255, 256, 257, 258, 259, 260, 261, 26
283, 285, 286, 288, 289, 290, 297, 299, 302, 314, 315, 316, 318, 320, 324, 329, ........

This query can be very long and take more than 4-5s to execute.

If several of these hit at once, the system often starts to slow down and eventually crash.

The query is slow even on higher powered instances with faster CPU and RAM.

I've tried various settings in MariaDB, including optimizer switches, different versions, etc with little improvement.

I am curious if this is triggered by bots or real users. The issues happens at random times and usually requires a server reboot if it is not stopped quickly.

Thanks

 

[Xon]

You can use my free add-on to try to find where this is being generated;

Resource icon X

Slow query logger

Feb 7, 2018

Generate a stack trace on slow queries

• Xon
• dev tools sql
• Add-ons [2.x]

I do not recommend leaving it enabled