• 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?

RobParker

Well-known member
#1
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?
 

RobParker

Well-known member
#3
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?
 

Jake Bunce

XenForo moderator
Staff member
#5
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

Well-known member
#8
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

XenForo developer
Staff member
#9
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 :p

PHP:
$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

Well-known member
#10
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

Well-known member
#11
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.
 

RoldanLT

Well-known member
#16
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 :)