Fixed Sometimes slow loading (slow global RSS query)

twollert

Active member
I switched from vBulletin 4.0.4 to XenForo 1.1.0 a week ago. Since the changeover I have some issues with the server latency and load average (see attachments). Sometimes it takes really long until a clicked page comes up and I also had some double postings from members because of that.

My provider told me that the reason are some slow querys (which are locking the tables):

Code:
2011-12-10 09:41:44 | dbXXXXXXXX[dbXXXXXXXX] @ local3 [127.0.0.3] |
00:00:13 | SELECT thread.*
                                        ,
                                        user.*, IF(user.username IS
NULL, thread.username, user.username) AS username,
                                        node.title AS node_title,
                                        forum.*,
                                permission.cache_value AS
node_permission_cache
                                FROM xf_thread AS thread
 
                                        LEFT JOIN xf_user AS user ON
                                                (user.user_id =
thread.user_id)
                                        INNER JOIN xf_node AS node ON
                                                (node.node_id =
thread.node_id)
                                        INNER JOIN xf_forum AS forum ON
                                                (forum.node_id =
thread.node_id)
                                LEFT JOIN xf_permission_cache_content AS
permission
                                        ON
(permission.permission_combination_id = 1
                                                AND
permission.content_type = 'node'
                                                AND
permission.content_id = thread.node_id)
                                WHERE (forum.find_new = 1)
                                ORDER BY thread.last_post_date DESC
                        LIMIT 120

Is there a way to fix that?

radioforen_latency.webpradioforen_loadaverage.webp
 
It'd be nice to see a process list in MySQL (SHOW FULL PROCESSLIST; ) when it's slow. I'm trying to make sense of that output and I assume it's saying that the query ran for 13 seconds, but it doesn't show the state of the query. Note that as we use InnoDB (provided your server has it), you shouldn't be getting locking.

It's possible that MySQL is being stupid and not using the right index for that. If you could use phpMyAdmin, run this query, and show the results, that'd be helpful:

Code:
EXPLAIN SELECT thread.*
                                        ,
                                        user.*, IF(user.username IS
NULL, thread.username, user.username) AS username,
                                        node.title AS node_title,
                                        forum.*,
                                permission.cache_value AS
node_permission_cache
                                FROM xf_thread AS thread
 
                                        LEFT JOIN xf_user AS user ON
                                                (user.user_id =
thread.user_id)
                                        INNER JOIN xf_node AS node ON
                                                (node.node_id =
thread.node_id)
                                        INNER JOIN xf_forum AS forum ON
                                                (forum.node_id =
thread.node_id)
                                LEFT JOIN xf_permission_cache_content AS
permission
                                        ON
(permission.permission_combination_id = 1
                                                AND
permission.content_type = 'node'
                                                AND
permission.content_id = thread.node_id)
                                WHERE (forum.find_new = 1)
                                ORDER BY thread.last_post_date DESC
 
Well, indeed that is being stupid, though you don't have that much data so it really shouldn't be that slow anyway. I'll have to do some testing to see if I can reproduce it.
 
Note that as we use InnoDB (provided your server has it), you shouldn't be getting locking.

My server should support InnoDB. The only thing that looks a little bit strange to me is the "MylSAM" in the last row:

radioforen_innodb.webp

It'd be nice to see a process list in MySQL (SHOW FULL PROCESSLIST; ) when it's slow.

I tried to fetch that two times:

radioforen_processlist1.webp radioforen_processlist2.webp

If you need anything else, please just let me know. Thanks for your help! :)
 
Top Bottom