nrep
Well-known member
I'm testing out upgrading a forum with several million threads, which contains many forum categories with hundreds of thousands of posts.
I did a test XF import and it works really well (and fast) on smaller forum sections, however if I start browsing a deep forum section page (i.e. page 10,000 of a forum), then things crawl to a halt. The further back I go, the worse it gets. I can use up to page 2,000 without much of a delay, but if I start to access page 10,000+ I get timeouts (over 30 seconds).
Here's an example of one of the slow queries I get from debug mode:
This forum runs really well on vBulletin, so I'm very puzzled as to why it crawls on XF? I would consider MySQL to be well optimised - other sites on the server fly, as does the VB version of the site.
I did a test XF import and it works really well (and fast) on smaller forum sections, however if I start browsing a deep forum section page (i.e. page 10,000 of a forum), then things crawl to a halt. The further back I go, the worse it gets. I can use up to page 2,000 without much of a delay, but if I start to access page 10,000+ I get timeouts (over 30 seconds).
Here's an example of one of the slow queries I get from debug mode:
Code:
SELECT thread.*
,
user.*, IF(user.username IS NULL, thread.username, user.username) AS username,
deletion_log.delete_date, deletion_log.delete_reason,
deletion_log.delete_user_id, deletion_log.delete_username,
IF(thread_read.thread_read_date > 1420400047, thread_read.thread_read_date, 1420400047) AS thread_read_date,
IF(thread_watch.user_id IS NULL, 0,
IF(thread_watch.email_subscribe, 'watch_email', 'watch_no_email')) AS thread_is_watched,
thread_user_post.post_count AS user_post_count
FROM xf_thread AS thread
LEFT JOIN xf_user AS user ON
(user.user_id = thread.user_id)
LEFT JOIN xf_deletion_log AS deletion_log ON
(deletion_log.content_type = 'thread' AND deletion_log.content_id = thread.thread_id)
LEFT JOIN xf_thread_read AS thread_read ON
(thread_read.thread_id = thread.thread_id
AND thread_read.user_id = 1)
LEFT JOIN xf_thread_watch AS thread_watch
ON (thread_watch.thread_id = thread.thread_id
AND thread_watch.user_id = 1)
LEFT JOIN xf_thread_user_post AS thread_user_post
ON (thread_user_post.thread_id = thread.thread_id
AND thread_user_post.user_id = 1)
WHERE (thread.node_id = 152) AND (thread.sticky = 0) AND (thread.discussion_state IN ('visible','deleted','moderated'))
ORDER BY thread.last_post_date DESC
LIMIT 20 OFFSET 162380
Run Time: 12.230421
Select Type Table Type Possible Keys Key Key Len Ref Rows Extra
SIMPLE thread ref node_id_last_post_date,node_id_sticky_state_last_post node_id_last_post_date 4 const 552480 Using where
SIMPLE user eq_ref PRIMARY PRIMARY 4 testsite.thread.user_id 1
SIMPLE deletion_log eq_ref PRIMARY PRIMARY 31 const,testsite.thread.thread_id 1 Using where
SIMPLE thread_read eq_ref user_id_thread_id,thread_id user_id_thread_id 8 const,testsite.thread.thread_id 1
SIMPLE thread_watch eq_ref PRIMARY,thread_id_email_subscribe PRIMARY 8 const,testsite.thread.thread_id 1
SIMPLE thread_user_post eq_ref PRIMARY,user_id PRIMARY 8 testsite.thread.thread_id,const 1This forum runs really well on vBulletin, so I'm very puzzled as to why it crawls on XF? I would consider MySQL to be well optimised - other sites on the server fly, as does the VB version of the site.
. That market place is DAMN fast!
