1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Partial Fix forum_list : latest post is not mysql optimized

Discussion in 'Resolved Bug Reports' started by Marcus, Aug 10, 2015.

  1. Marcus

    Marcus Well-Known Member

    Only after I deactivated "showing latest ... posts" on the forum_list my forum could continue to serve webpages. As shown at the debug page this query took between 0.8 - 15 seconds to execute. On a very tiny mysql machine (1 GB RAM) it took around 15 seconds, on a slightly larger mysql machine (2 GB RAM) it took between 0.8 - 4 seconds. [1.5 rc1]
     
  2. imthebest

    imthebest Formerly Super120

    Could you please share the slow/server intensive query? Are you able to reproduce this problem on 1.4.x?
     
  3. Marcus

    Marcus Well-Known Member

    This is the super slow xenforo 1.5 rc1 query to display the latest 5 posts. Actually it fetches ten in the hope that at least five will be visible.
    t1.PNG
    Code:
    SELECT thread.*
    ,
    last_post_user.gender AS last_post_gender,
    last_post_user.avatar_date AS last_post_avatar_date,
    last_post_user.gravatar AS last_post_gravatar,
    IF(last_post_user.username IS NULL, thread.last_post_username, last_post_user.username) AS last_post_username,
    node.title AS node_title, node.node_name,
    forum.*,
    forum.last_post_id AS forum_last_post_id,
    forum.last_post_date AS forum_last_post_date,
    forum.last_post_user_id AS forum_last_post_user_id,
    forum.last_post_username AS forum_last_post_username,
    forum.last_thread_title AS forum_last_thread_title,
    thread.last_post_id,
    thread.last_post_date,
    thread.last_post_user_id,
    thread.last_post_username,
    GREATEST(COALESCE(thread_read.thread_read_date, 0), COALESCE(forum_read.forum_read_date, 0), -xxxxxxxx+xx) AS thread_read_date,
    permission.cache_value AS node_permission_cache
    FROM xf_thread AS thread
    
    LEFT JOIN xf_user AS last_post_user ON
    (last_post_user.user_id = thread.last_post_user_id)
    LEFT JOIN xf_node AS node ON
    (node.node_id = thread.node_id)
    LEFT JOIN xf_forum AS forum ON
    (forum.node_id = thread.node_id)
    LEFT JOIN xf_thread_read AS thread_read ON
    (thread_read.thread_id = thread.thread_id
    AND thread_read.user_id = zzz)
    LEFT JOIN xf_forum_read AS forum_read ON
    (forum_read.node_id = thread.node_id
    AND forum_read.user_id = zzz)
    LEFT JOIN xf_permission_cache_content AS permission
    ON (permission.permission_combination_id = yyy
    AND permission.content_type = 'node'
    AND permission.content_id = thread.node_id)
    WHERE (thread.discussion_type <> 'redirect') AND (thread.discussion_state IN ('visible')) AND (thread.last_post_date > -xxxxxxxx+xx) AND (forum.find_new = 1)
    ORDER BY thread.last_post_date DESC
    LIMIT 10
    Run Time: 1.653349
    
     
    Last edited: Aug 10, 2015
  4. imthebest

    imthebest Formerly Super120

    Are you using something like "last post avatar" add-on on your 1.5 test install?
     
  5. Mike

    Mike XenForo Developer Staff Member

    Can you give the actual query that's running? What's your read marking lifetime set to?

    Your explain output is something that caused problems for us here, but it was down to MySQL's optimizer not using the correct approach which is to use the last_post_date index. It was adjusted to include a condition that would hint to this, though we haven't forced it.
     
  6. Marcus

    Marcus Well-Known Member

    I wrote you a pm with the full query. The read marking lifetime is set to 922337203685480000 which should cover a lifetime.

    The problem occured just today, maybe this query took some days / weeks to slow mysql down.
     
  7. Mike

    Mike XenForo Developer Staff Member

    "That's your problem..."

    Now I might be able to tweak the query, but there isn't really an expectation of keeping the read marking data permanently and there are other places where this creates expensive queries. The default is only kept for 30 days. Setting it to a value like that is very much not expected. I certainly wouldn't go over something like a year.
     
  8. Marcus

    Marcus Well-Known Member

    Thanks for the update, I've set it to 30 days now, I wasn't aware of performance issues
     
  9. Mike

    Mike XenForo Developer Staff Member

    I'm applying a cap of 365 to this option to prevent potential problems from setting it to a huge value. (There has been at least one other problem from this, though it wasn't this query.)

    It should prevent issues with this query but also a few others, so it's not really a direct fix for this issue. I'll tag it as a partial fix to represent that there is a relevant change.
     
    jeffwidman, semprot and Marcus like this.
  10. Marcus

    Marcus Well-Known Member

    With my value Homer could login tracking comments to his latest Iliad. A year is more than enough to keep rarely logged in members to date.
     
    jeffwidman likes this.

Share This Page