• REGISTRATION REQUIREMENTS:

    Your username here MUST MATCH your XenForo username (connected to your XF license).

    Once you have registered here, then you need to start a conversation at xenforo.com w/Bob and provide the following:
    1. Your XenForo License Validation Token
    2. The Domain Name associated with the License
    NOTE: Your account will be validated once ALL requirements are verified/met. Thank you for your patience.

Awaiting More Details Suggestions for better performance?

kontrabass

Member
AMS Premium
CAS Premium
RMS Premium
SC Premium
Hi Bob!

We're days into our Xenforo 2 upgrade, along with it we've imported our entire classifieds forums into CAS.

Page loads are snappy on all other pages in the site, but everything in CAS is slow. Like 6-10 seconds loading a page.

I've got people looking into the server, mysql queries etc., but just wanted to see if you knew of any tips or tweaks to help.

 
Hi Bob!

We're days into our Xenforo 2 upgrade, along with it we've imported our entire classifieds forums into CAS.

Page loads are snappy on all other pages in the site, but everything in CAS is slow. Like 6-10 seconds loading a page.

I've got people looking into the server, mysql queries etc., but just wanted to see if you knew of any tips or tweaks to help.

Didn't we already resolve this a few weeks ago with the solution by Xon?
 
Didn't we already resolve this a few weeks ago with the solution by Xon?
That solution is in place, and it drastically helped (index wouldn't load for over 10 seconds previously). We're looking into the logs to see what causing general sluggishness now.
 
That solution is in place, and it drastically helped (index wouldn't load for over 10 seconds previously). We're looking into the logs to see what causing general sluggishness now.
Might want to double check the indexes on the xf_xa_cas_ad and xf_xa_cas_category tables.

Things like setting the default sort order to a sort type that doesn't have an index would slow things down if there is a significant amount of rows of data in a table that is performing a full table scan when there is no appropriate index in place.

Also, having too many widgets is going to add to the page load.

Also, displaying Ads (like Google Adsense) is going to significantly cause slow page loads, something that a lot of people overlook.

So when testing, its best to disable all the "extras" like Widgets, Google Ads (ads from any network), sidebar blocks, feature block etc.. That way its only the Index of CAS Ads (or if viewing an Ad, only the Ad itself and not a ton of blocks and widgets AND ADS.
 
Might want to double check the indexes on the xf_xa_cas_ad and xf_xa_cas_category tables.

Things like setting the default sort order to a sort type that doesn't have an index would slow things down if there is a significant amount of rows of data in a table that is performing a full table scan when there is no appropriate index in place.

Also, having too many widgets is going to add to the page load.

Also, displaying Ads (like Google Adsense) is going to significantly cause slow page loads, something that a lot of people overlook.

So when testing, its best to disable all the "extras" like Widgets, Google Ads (ads from any network), sidebar blocks, feature block etc.. That way its only the Index of CAS Ads (or if viewing an Ad, only the Ad itself and not a ton of blocks and widgets AND ADS.
Bob, my server admin is asking, is there a list of what indexes should be on those tables?
 
Bob, my server admin is asking, is there a list of what indexes should be on those tables?
The install file (Setup.php) has the indexes that are created ...

PHP:
        $tables['xf_xa_cas_ad'] = function(Create $table)
        {
            [...]
            $table->addKey(['category_id', 'create_date'], 'category_create_date');
            $table->addKey(['category_id', 'last_update'], 'category_last_update');
            $table->addKey(['category_id', 'ad_type'], 'category_ad_type');
            $table->addKey(['category_id', 'ad_status'], 'category_ad_status');
            $table->addKey(['category_id', 'is_sponsored', 'last_update']);
            $table->addKey(['user_id', 'last_update']);
            $table->addKey(['is_sponsored', 'last_update']);
            $table->addKey('create_date');
            $table->addKey('last_update');
            $table->addKey('ad_type');
            $table->addKey('ad_status');
            $table->addKey('is_sponsored');
            $table->addKey('discussion_thread_id');
            $table->addKey('prefix_id');
        };


        $tables['xf_xa_cas_category'] = function(Create $table)
        {
            [...]
            $table->addKey(['parent_category_id', 'lft']);
            $table->addKey(['lft', 'rgt']);
        };
 
Last edited:
  • Like
Reactions: Bob
The install file (Setup.php) has the indexes that are created ...
This is true for XF 2.2 and older versions.

Starting with XF 2.3.0 stable versions of my addons, table structure/data is located in src/addons/XenAddons/[AddonId]/Install/Data/MySQL.php (both XFRM and XFMG maed this change, which follows Core XF, so I decided to do the same).
 
Confirmed all indexes seem to be there. Server admin thinks maybe a MariaDB upgrade will help (we're on 10.3).

These queries are always in the processlist, with run times 4-5 secs or more:

| SELECT `xf_xa_cas_comment`.*, `xf_xa_cas_ad_Ad_1`.*, `xf_user_User_2`.*, `xf_user_profile_Profile_3`.*, `xf_session_activity_Activity_4`.*, `xf_reaction_content_Reactions_5`.*
FROM `xf_xa_cas_comment`
LEFT JOIN `xf_xa_cas_ad` AS `xf_xa_cas_ad_Ad_1` ON (`xf_xa_cas_ad_Ad_1`.`ad_id` = `xf_xa_cas_comment`.`ad_id`)
LEFT JOIN `xf_user` AS `xf_user_User_2` ON (`xf_user_User_2`.`user_id` = `xf_xa_cas_comment`.`user_id`)
LEFT JOIN `xf_user_profile` AS `xf_user_profile_Profile_3` ON (`xf_user_profile_Profile_3`.`user_id` = `xf_user_User_2`.`user_id`)
LEFT JOIN `xf_session_activity` AS `xf_session_activity_Activity_4` ON (`xf_session_activity_Activity_4`.`user_id` = `xf_user_User_2`.`user_id` AND `xf_session_activity_Activity_4`.`unique_key` = CONCAT(`xf_user_User_2`.`user_id`, ''))
LEFT JOIN `xf_reaction_content` AS `xf_reaction_content_Reactions_5` ON (`xf_reaction_content_Reactions_5`.`content_type` = 'cas_comment' AND `xf_reaction_content_Reactions_5`.`content_id` = `xf_xa_cas_comment`.`comment_id` AND `xf_reaction_content_Reactions_5`.`reaction_user_id` = '39455')
WHERE (`xf_xa_cas_comment`.`ad_id` = 1655635) AND ((`xf_xa_cas_comment`.`comment_state` = 'moderated' AND `xf_xa_cas_comment`.`user_id` = 39455) OR (`xf_xa_cas_comment`.`comment_state` IN ('visible')))
ORDER BY `xf_xa_cas_comment`.`comment_date` ASC, `xf_xa_cas_comment`.`comment_id` ASC
 
Confirmed all indexes seem to be there. Server admin thinks maybe a MariaDB upgrade will help (we're on 10.3).

These queries are always in the processlist, with run times 4-5 secs or more:

| SELECT `xf_xa_cas_comment`.*, `xf_xa_cas_ad_Ad_1`.*, `xf_user_User_2`.*, `xf_user_profile_Profile_3`.*, `xf_session_activity_Activity_4`.*, `xf_reaction_content_Reactions_5`.*
FROM `xf_xa_cas_comment`
LEFT JOIN `xf_xa_cas_ad` AS `xf_xa_cas_ad_Ad_1` ON (`xf_xa_cas_ad_Ad_1`.`ad_id` = `xf_xa_cas_comment`.`ad_id`)
LEFT JOIN `xf_user` AS `xf_user_User_2` ON (`xf_user_User_2`.`user_id` = `xf_xa_cas_comment`.`user_id`)
LEFT JOIN `xf_user_profile` AS `xf_user_profile_Profile_3` ON (`xf_user_profile_Profile_3`.`user_id` = `xf_user_User_2`.`user_id`)
LEFT JOIN `xf_session_activity` AS `xf_session_activity_Activity_4` ON (`xf_session_activity_Activity_4`.`user_id` = `xf_user_User_2`.`user_id` AND `xf_session_activity_Activity_4`.`unique_key` = CONCAT(`xf_user_User_2`.`user_id`, ''))
LEFT JOIN `xf_reaction_content` AS `xf_reaction_content_Reactions_5` ON (`xf_reaction_content_Reactions_5`.`content_type` = 'cas_comment' AND `xf_reaction_content_Reactions_5`.`content_id` = `xf_xa_cas_comment`.`comment_id` AND `xf_reaction_content_Reactions_5`.`reaction_user_id` = '39455')
WHERE (`xf_xa_cas_comment`.`ad_id` = 1655635) AND ((`xf_xa_cas_comment`.`comment_state` = 'moderated' AND `xf_xa_cas_comment`.`user_id` = 39455) OR (`xf_xa_cas_comment`.`comment_state` IN ('visible')))
ORDER BY `xf_xa_cas_comment`.`comment_date` ASC, `xf_xa_cas_comment`.`comment_id` ASC
How many CAS Comment Widgets do you have created and where are those being displayed? Did you temp disable those on the page that you were testing? eg, if CAS Index is slow, disable the Recent Comments Widget to see if that might be the issue.

When troubleshooting page load issues, the first thing I do is disable all Widgets (those can add a lot of load time) and any optional "blocks" of content (so that its just the main index page without all the additional load. Then go back and start enabling them again (testing between each one) to see if there is a specific widget or block that is adding additional load.
 
I have 0 widgets enabled or created. The classified index pages seem to be ok, but it'll take sometimes 12 seconds to load an individual ad.

NVM didn't realize latest comments widget was there. On it.
 
Each Tab of a Classified Ad has its own action in the controller that fetches data for that specific tab.

Is it the Overview Tab that you are talking about? If so, have you disable all of the optional blocks for the Overview page so that its only the Ad and not all of the optional extra's?

Can you activate debug mode and then view the debug page for the page that you are viewing. That will display all of the queries involved and the execution time for each query. Anything over 1 is considered a "slow" query (altho, some of that could be database performance and not necessarily a fetching issue).

I always find it best to disable as many things as possible so that you are testing with barebones (and default enables a lot of optional blocks, so unless you already disabled them, there are a lot of them pre-enabled).
 
btw, CAS will be taking advantage of Deferred Joins (new to XF 2.3) for sub content like Items, Comments, Offers, Questions, Transactions, Updates etc.

Deferred joins​

Enter "deferred joins"! This is actually a new feature in the core of XenForo, albeit currently only used within Media Gallery. For the developers amongst you, all it really means is you will call $finder->fetchDeferred() rather than $finder->fetch(), and for the non-developers amongst you, what it means is "slow query go fast" 😉

Behind the scenes what actually happens is rather than executing a single query, it executes two! Which may seem counter-intuitive but bear with me. The first query simply aims to fetch the applicable IDs for the conditions and page you are on which is fast because it uses the primary key, whereas the second query fetches all of the data applicable with all of the joins but matching those specific IDs fetched from the first query.

To cut a very long story short, if you take the page 45,252 example from above, this same query reduced from over 30 seconds to only 2 seconds. While 2 seconds might still seem a little on the high side, this is a 93% decrease from before, and it's only as high as it is because of the high page number. The upshot is that the sheer majority of those 45,000+ pages will now be remarkably faster to load than before. This will not only massively decrease frustration of your users, but it will significantly increase the likelihood of these pages being crawled by search engines and dramatically decrease the load on your database.

 
Each Tab of a Classified Ad has its own action in the controller that fetches data for that specific tab.

Is it the Overview Tab that you are talking about? If so, have you disable all of the optional blocks for the Overview page so that its only the Ad and not all of the optional extra's?

Can you activate debug mode and then view the debug page for the page that you are viewing. That will display all of the queries involved and the execution time for each query. Anything over 1 is considered a "slow" query (altho, some of that could be database performance and not necessarily a fetching issue).

I always find it best to disable as many things as possible so that you are testing with barebones (and default enables a lot of optional blocks, so unless you already disabled them, there are a lot of them pre-enabled).

Hi Bob,

We just upgraded to MariaDB 10.11

It seems to have sped up ad load times considerably but it's still slower than optimal. I will look at all the queries and see what I can find. Trying to enable debug mode for just my IP but I think cloudflare is messing with it. I have turned off all widgets, "more from this seller", and "more in this category" blocks.

My sysadmin just sent this:

Also the classified query that is slow seems to processing all the comments on ads given there like 2 million to process , Here is a sample query,

| 17080 | xf2014 | 127.0.0.1:34932 | xenforo3 | Execute | 7 | Sending data | SELECT `xf_xa_cas_comment`.*, `xf_xa_cas_ad_Ad_1`.*, `xf_user_User_2`.*, `xf_user_profile_Profile_3`.*, `xf_session_activity_Activity_4`.*
FROM `xf_xa_cas_comment`
LEFT JOIN `xf_xa_cas_ad` AS `xf_xa_cas_ad_Ad_1` ON (`xf_xa_cas_ad_Ad_1`.`ad_id` = `xf_xa_cas_comment`.`ad_id`)
LEFT JOIN `xf_user` AS `xf_user_User_2` ON (`xf_user_User_2`.`user_id` = `xf_xa_cas_comment`.`user_id`)
LEFT JOIN `xf_user_profile` AS `xf_user_profile_Profile_3` ON (`xf_user_profile_Profile_3`.`user_id` = `xf_user_User_2`.`user_id`)
LEFT JOIN `xf_session_activity` AS `xf_session_activity_Activity_4` ON (`xf_session_activity_Activity_4`.`user_id` = `xf_user_User_2`.`user_id` AND `xf_session_activity_Activity_4`.`unique_key` = CONCAT(`xf_user_User_2`.`user_id`, ''))
WHERE (`xf_xa_cas_comment`.`ad_id` = 1610437) AND ((`xf_xa_cas_comment`.`comment_state` IN ('visible')))
ORDER BY `xf_xa_cas_comment`.`comment_date` ASC, `xf_xa_cas_comment`.`comment_id` ASC

LIMIT 20 | 0.000 |


and here is the explain plan,

MariaDB [xenforo3]> explain extended SELECT `xf_xa_cas_comment`.*, `xf_xa_cas_ad_Ad_1`.*, `xf_user_User_2`.*, `xf_user_profile_Profile_3`.*, `xf_session_activity_Activity_4`.* FROM `xf_xa_cas_comment` LEFT JOIN `xf_xa_cas_ad` AS `xf_xa_cas_ad_Ad_1` ON (`xf_xa_cas_ad_Ad_1`.`ad_id` = `xf_xa_cas_comment`.`ad_id`) LEFT JOIN `xf_user` AS `xf_user_User_2` ON (`xf_user_User_2`.`user_id` = `xf_xa_cas_comment`.`user_id`) LEFT JOIN `xf_user_profile` AS `xf_user_profile_Profile_3` ON (`xf_user_profile_Profile_3`.`user_id` = `xf_user_User_2`.`user_id`) LEFT JOIN `xf_session_activity` AS `xf_session_activity_Activity_4` ON (`xf_session_activity_Activity_4`.`user_id` = `xf_user_User_2`.`user_id` AND `xf_session_activity_Activity_4`.`unique_key` = CONCAT(`xf_user_User_2`.`user_id`, '')) WHERE (`xf_xa_cas_comment`.`ad_id` = 868721) AND ((`xf_xa_cas_comment`.`comment_state` IN ('visible'))) ORDER BY `xf_xa_cas_comment`.`comment_date` ASC, `xf_xa_cas_comment`.`comment_id` ASC;
+------+-------------+--------------------------------+--------+---------------+---------+---------+--------------------------------------+---------+----------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+--------------------------------+--------+---------------+---------+---------+--------------------------------------+---------+----------+-----------------------------+
| 1 | SIMPLE | xf_xa_cas_comment | ALL | NULL | NULL | NULL | NULL | 2669449 | 100.00 | Using where; Using filesort |
| 1 | SIMPLE | xf_xa_cas_ad_Ad_1 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | |
| 1 | SIMPLE | xf_user_User_2 | eq_ref | PRIMARY | PRIMARY | 4 | xenforo3.xf_xa_cas_comment.user_id | 1 | 100.00 | |
| 1 | SIMPLE | xf_user_profile_Profile_3 | eq_ref | PRIMARY | PRIMARY | 4 | xenforo3.xf_user_User_2.user_id | 1 | 100.00 | Using where |
| 1 | SIMPLE | xf_session_activity_Activity_4 | eq_ref | PRIMARY | PRIMARY | 22 | xenforo3.xf_user_User_2.user_id,func | 1 | 100.00 | Using where |
+------+-------------+--------------------------------+--------+---------------+---------+---------+--------------------------------------+---------+----------+-----------------------------+
5 rows in set, 1 warning (0.001 sec)
 
Back
Top