Can anyone identify this query?

[RobParker]

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?

 

[Jake Bunce]

XenPorta. RecentNews block.

 

[RobParker]

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?

 

[RobinHood]

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

 

[Jake Bunce]

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.

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

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

 

[RobParker]

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.

 

[RobinHood]

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

 

[RobParker]

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.

 

[Chris D]

RobParker,

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

$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)
$fromWhere
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) < ?
ORDER BY $fromOrder promote_date DESC
LIMIT ?, ?
", array(XenForo_Application::$time, XenForo_Application::$time, $page, $options['limit']));

 

[RobParker]

Chris,

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.

Cheers

Rob

 

[Andy.N]

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.

 

[Member 10517]

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

 

[Paul B]

Please post in the respective resource discussion thread for support.

 

[rdn]

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.

Extra Portal?
or Widget page of BD Widget?

 

[Andy.N]

Extra Portal?
or Widget page of BD Widget?

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.

 

[rdn]

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.

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

 

[Andy.N]

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

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

 

[rdn]

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

That's better improvement.

 

[rdn]

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

 

[rdn]

What value did you put here?