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

Can anyone identify this query?

Discussion in 'Troubleshooting and Problems' started by RobParker, Aug 31, 2012.

  1. RobParker

    RobParker Well-Known Member

    We're very busy today and it's causing a few server issues. Our host asked us if we could identify what was causing the following:

    Copying to tmp table | SELECT xf_thread.*, xf_user.*, xf_post.message, xf_post.attach_count, xf_node.title AS node_title,

    Any ideas?
    Chris D likes this.
  2. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    XenPorta. RecentNews block.
    Chris D likes this.
  3. RobParker

    RobParker Well-Known Member

    Cheers, our hosts found it too. We've currently got around 3000 users online and that query was killing the server. Disabling the recent news block and it got a lot better.

    Is there anything obviously "wrong" with it? Inefficient, etc?
  4. RobinHood

    RobinHood Well-Known Member

    How did you figure that out Jake? Just wondering what the process was
  5. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    I searched for that query in the XF files using OS X's search feature. Nothing came up, but I noticed it was flagging files outside of the XF directory. Those files were from XenPorta in a different directory on my hard drive.

    Well it is a big query with lots of joins. It might benefit from some extra indexes in your schema.
    RobinHood likes this.
  6. RobParker

    RobParker Well-Known Member

    I'll get the full query from our hosts and post it here.

    Disabling xenporta in total has helped a LOT, much more than I expected.
  7. RobinHood

    RobinHood Well-Known Member

    You can cache all of the blocks on XenPorta in the block settings, might be worth checking to see what these were set to.
  8. RobParker

    RobParker Well-Known Member

    Yep they were all set to cache a few hours.

    It seems that the recentnews block was trying to create a lot of tmp tables which was causing IO issues.

    Today is our busiest day of the year by a long way so we expected high traffic.
  9. Chris D

    Chris D XenForo Developer Staff Member


    I'm glad you've found this dude.

    We have sporadic days where we have 1000+ users online and we were also finding XenPorta was killing our site to the point where we turn it off completely.

    Definitely need to resolve this problem. What does the query actually do? Is it necessary? I guess we should post in the XenPorta support thread first.

    EDIT: The answer to the question "is it necessary" is: yes. Yes if you want it to display any news at all :p

    $news $this->_getDb()->fetchAll("
    SELECT xf_thread.*, xf_user.*, xf_post.message, xf_post.attach_count, xf_node.title AS node_title, 
    IF(xf_user.username IS NULL, xf_thread.username, xf_user.username) AS username,
    IF(EWRporta_promotes.promote_date IS NULL, xf_thread.post_date, EWRporta_promotes.promote_date) AS promote_date,
    EWRporta_promotes.promote_icon, EWRporta_promotes.promote_data
    FROM xf_thread
    LEFT JOIN xf_user ON (xf_user.user_id = xf_thread.user_id)
    INNER JOIN xf_post ON (xf_post.post_id = xf_thread.first_post_id)
    INNER JOIN xf_node ON (xf_node.node_id = xf_thread.node_id)
    LEFT JOIN EWRporta_promotes ON (EWRporta_promotes.thread_id = xf_thread.thread_id)
    WHERE (xf_thread.node_id IN ("
    .$this->_getDb()->quote($options['forum']).") OR EWRporta_promotes.promote_date < ?)
    AND xf_thread.discussion_state = 'visible'
    AND IF(EWRporta_promotes.promote_date IS NULL, xf_thread.post_date, EWRporta_promotes.promote_date) < ?
    $fromOrder promote_date DESC
    LIMIT ?, ?
    , array(XenForo_Application::$timeXenForo_Application::$time$page$options['limit']));
    Jake Bunce likes this.
  10. RobParker

    RobParker Well-Known Member


    It would be amazing if you could resolve this somehow. We've just turned xenporta off completely and page loads with 3000+ users has gone down from 5 seconds to 0.5 seconds.

    If there's anything I can do to help let me know.


  11. Andy.N

    Andy.N Well-Known Member

    This is one reason why i look forward to the new widgetportal which will be released this weekend. Hopefully that addon will have less impact inn the server.
    Itworx4me likes this.
  12. archimedes

    archimedes Member

    The recentnews block frequently kills our server. What can we do?
  13. Brogan

    Brogan XenForo Moderator Staff Member

    Please post in the respective resource discussion thread for support.
  14. RoldanLT

    RoldanLT Well-Known Member

    Extra Portal?
    or Widget page of BD Widget?
  15. Andy.N

    Andy.N Well-Known Member

    None of the above. ;)
    I posted that in 2012 and I'm currently styling Widget Page to replace my Xenporta homepage.

    Still have some styling issues to fix but the functions are there. @xfrocks is doing a great job to develop BD WF into a powerful addon.
    RoldanLT likes this.
  16. RoldanLT

    RoldanLT Well-Known Member

    Yes exactly. :)
    When Widget Page/Portal becomes stable and well documented.
    I will be going to use that also as my Index :)
  17. Andy.N

    Andy.N Well-Known Member

    Replaced Xenporta with Widget Page as my home index today
    Widget page
    Timing: 0.0442 seconds Memory: 6.367 MB DB Queries: 12
    Timing: 0.0659 seconds Memory: 7.574 MB DB Queries: 15
  18. RoldanLT

    RoldanLT Well-Known Member

    That's better improvement.
  19. RoldanLT

    RoldanLT Well-Known Member

    May I know what is that widget your using?
    Latest Thread, what layout?
  20. RoldanLT

    RoldanLT Well-Known Member

    What value did you put here?

Share This Page