XenForo 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:

SQL:

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`...

Read more

Continue reading...