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

Fixed Sometimes slow loading (slow global RSS query)

Discussion in 'Resolved Bug Reports' started by twollert, Dec 10, 2011.

  1. twollert

    twollert Active Member

    I switched from vBulletin 4.0.4 to XenForo 1.1.0 a week ago. Since the changeover I have some issues with the server latency and load average (see attachments). Sometimes it takes really long until a clicked page comes up and I also had some double postings from members because of that.

    My provider told me that the reason are some slow querys (which are locking the tables):

    Code:
    2011-12-10 09:41:44 | dbXXXXXXXX[dbXXXXXXXX] @ local3 [127.0.0.3] |
    00:00:13 | SELECT thread.*
                                            ,
                                            user.*, IF(user.username IS
    NULL, thread.username, user.username) AS username,
                                            node.title AS node_title,
                                            forum.*,
                                    permission.cache_value AS
    node_permission_cache
                                    FROM xf_thread AS thread
     
                                            LEFT JOIN xf_user AS user ON
                                                    (user.user_id =
    thread.user_id)
                                            INNER JOIN xf_node AS node ON
                                                    (node.node_id =
    thread.node_id)
                                            INNER JOIN xf_forum AS forum ON
                                                    (forum.node_id =
    thread.node_id)
                                    LEFT JOIN xf_permission_cache_content AS
    permission
                                            ON
    (permission.permission_combination_id = 1
                                                    AND
    permission.content_type = 'node'
                                                    AND
    permission.content_id = thread.node_id)
                                    WHERE (forum.find_new = 1)
                                    ORDER BY thread.last_post_date DESC
                            LIMIT 120
    
    Is there a way to fix that?

    radioforen_latency.png radioforen_loadaverage.png
     
  2. Brogan

    Brogan XenForo Moderator Staff Member

    Any pages in particular?

    Do you have any add-ons installed?
     
  3. twollert

    twollert Active Member

    Not really. It's very hard to reproduce, because it happends only sometimes.

    Maybe one of the cron jobs could be the reason? I'm using the standard cron jobs with the standard settings.

    Only a few smaller ones.
     
  4. Mike

    Mike XenForo Developer Staff Member

    It'd be nice to see a process list in MySQL (SHOW FULL PROCESSLIST; ) when it's slow. I'm trying to make sense of that output and I assume it's saying that the query ran for 13 seconds, but it doesn't show the state of the query. Note that as we use InnoDB (provided your server has it), you shouldn't be getting locking.

    It's possible that MySQL is being stupid and not using the right index for that. If you could use phpMyAdmin, run this query, and show the results, that'd be helpful:

    Code:
    EXPLAIN SELECT thread.*
                                            ,
                                            user.*, IF(user.username IS
    NULL, thread.username, user.username) AS username,
                                            node.title AS node_title,
                                            forum.*,
                                    permission.cache_value AS
    node_permission_cache
                                    FROM xf_thread AS thread
     
                                            LEFT JOIN xf_user AS user ON
                                                    (user.user_id =
    thread.user_id)
                                            INNER JOIN xf_node AS node ON
                                                    (node.node_id =
    thread.node_id)
                                            INNER JOIN xf_forum AS forum ON
                                                    (forum.node_id =
    thread.node_id)
                                    LEFT JOIN xf_permission_cache_content AS
    permission
                                            ON
    (permission.permission_combination_id = 1
                                                    AND
    permission.content_type = 'node'
                                                    AND
    permission.content_id = thread.node_id)
                                    WHERE (forum.find_new = 1)
                                    ORDER BY thread.last_post_date DESC
     
  5. twollert

    twollert Active Member

    Thanks, Mike! I'll have a look at the process list next time when it's slow.

    Here it is:

    mysql.png
     
  6. Mike

    Mike XenForo Developer Staff Member

    Well, indeed that is being stupid, though you don't have that much data so it really shouldn't be that slow anyway. I'll have to do some testing to see if I can reproduce it.
     
  7. twollert

    twollert Active Member

    My server should support InnoDB. The only thing that looks a little bit strange to me is the "MylSAM" in the last row:

    radioforen_innodb.png

    I tried to fetch that two times:

    radioforen_processlist1.png radioforen_processlist2.png

    If you need anything else, please just let me know. Thanks for your help! :)
     
  8. Mike

    Mike XenForo Developer Staff Member

    I've discovered this is from the global RSS feed, so I've improved that query for 1.1.1 now.
     
    Walter, twollert and Darkimmortal like this.
  9. twollert

    twollert Active Member

    Thank you! (y)
     

Share This Page