Partial fix forum_list : latest post is not mysql optimized

[Marcus]

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]

 

[imthebest]

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

 

[Marcus]

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.

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

 

[imthebest]

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

 

[Mike]

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.

 

[Marcus]

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.

 

[Mike]

"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.

 

[Marcus]

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

 

[Mike]

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.

 

[Marcus]

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.