XF 2.1 Slow loading on large threads - how to improve?

[Fullmental]

Hi, I'm staff at a forum where we are experiencing a performance issue. When trying to load threads with 1500+ replies, we are experiencing significant delays in the MySQL query. This happens when you click on the thread title. Other threads with far fewer posts are nearly instant

The problem goes away almost completely if you click on a page number of that thread, instead of the thread itself. That loads in maybe 1-2 seconds, with an average 500ms return time for the php call.

I'd like to understand what the difference is on the MySQL engine, and how we can optimize the loading of these large threads. We already have Xon's addon for Optimized List Queries. We've observed these behaviors are pretty much unchanged whether this add-on is enabled or disabled (Direct page queries are a bit slower, but not bad. Thread URLs are just as slow either way).

This seems to point to some core Xenforo code that is not behaving optimally.

Here's an example of the mysql log for one of these slow queries.

# Query_time: 136.476312  Lock_time: 0.000034  Rows_sent: 20  Rows_examined: 7170565
# Rows_affected: 0  Bytes_sent: 389
SET timestamp=1576964217;
SELECT
                post_main.post_id,
                (
                  SELECT post_id
                  FROM xf_post AS post_tmp
                  WHERE post_tmp.user_id = post_main.user_id
                    AND post_tmp.position >= 0
                    AND post_tmp.position < post_main.position
                    AND post_tmp.thread_id = 15480
                    AND post_tmp.message_state IN ('visible', 'deleted', 'moderated')
                  ORDER BY post_tmp.post_id ASC
                  LIMIT 1
                ) AS previous_post_id
            FROM
            (
                SELECT DISTINCT user_id, post_id, position
                FROM xf_post AS post
                WHERE post.thread_id = 15480
                  AND post.message_state IN ('visible', 'deleted', 'moderated')
                  AND post.position >= 0
                  AND post.position < 20
            ) AS post_main;

Our forum is rather large, with 783k posts and nearly 20k threads. We also need to import roughly another million posts and 15k threads from our old forum software, Tapatalk, at some point, but we are trying to ensure everything is well optimized before we continue with that migration. I expect this issue will only get worse as we import more posts. Tapatalk did not exhibit these query delays, but we don't have any insight into their backend configuration to try and further optimize on our own.

 

[Jeremy P]

Is it possible an add-on is at play here? That query doesn't look like anything I'm familiar with in the core, or at the very least not one which normally occurs when viewing a thread

 

[Fullmental]

Hmm, yes it looks like you are right. Our add-on for limiting signatures to once per page seems to be the culprit - it appears to be running its query on the entire thread, instead of just the 20 posts displayed on the page. I will make a post in the developer's support thread instead. Thank you.