Awaiting More Details Suggestions for better performance?

[kontrabass]

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.

https://www.talkbass.com/classifieds/

 

[Bob]

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.

https://www.talkbass.com/classifieds/

Didn't we already resolve this a few weeks ago with the solution by Xon?

 

[kontrabass]

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.

 

[Bob]

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.

 

[kontrabass]

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?

 

[Mouth]

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

        $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']);
        };

 

[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).

 

[kontrabass]

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

 

[Bob]

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.

 

[kontrabass]

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.

 

[Bob]

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

 

[Bob]

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.

XF 2.3 - What's new with Enhanced Search, Resource Manager and Media Gallery in XenForo 2.3?

Today is the day we sadly have to report that this is the final 'Have you seen...?' thread for XenForo 2.3. While there are still changes planned, these will almost exclusively be changes to existing features, bug fixes and miscellaneous improvements that aren't significant enough for their own...

xenforo.com

 

[kontrabass]

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)

 

[kontrabass]

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

Bob, after enabling debug mode, the page load times for CAS index is 1.6s on average. Item pages, about .7s. It doesn't seem bad, but when the rest of the site loads with a .05 to .1 second query time, the classifieds do seem quite sluggish. Enabling a couple widgets on the index page does not increase query times.

This is the longest query of the 40-some that are logged, at .64s:

7.
SELECT `xf_xa_cas_ad`.`ad_id`
FROM `xf_xa_cas_ad`
LEFT JOIN `xf_user` AS `xf_user_User_1` ON (`xf_user_User_1`.`user_id` = `xf_xa_cas_ad`.`user_id`)
LEFT JOIN `xf_xa_cas_ad_feature` AS `xf_xa_cas_ad_feature_Featured_2` ON (`xf_xa_cas_ad_feature_Featured_2`.`ad_id` = `xf_xa_cas_ad`.`ad_id`)
LEFT JOIN `xf_xa_cas_ad_sponsor` AS `xf_xa_cas_ad_sponsor_Sponsored_3` ON (`xf_xa_cas_ad_sponsor_Sponsored_3`.`ad_id` = `xf_xa_cas_ad`.`ad_id`)
LEFT JOIN `xf_attachment` AS `xf_attachment_CoverImage_4` ON (`xf_attachment_CoverImage_4`.`content_type` = 'cas_ad' AND `xf_attachment_CoverImage_4`.`content_id` = `xf_xa_cas_ad`.`ad_id` AND `xf_attachment_CoverImage_4`.`attachment_id` = `xf_xa_cas_ad`.`cover_image_id`)
LEFT JOIN `xf_attachment_data` AS `xf_attachment_data_Data_5` ON (`xf_attachment_data_Data_5`.`data_id` = `xf_attachment_CoverImage_4`.`data_id`)
LEFT JOIN `xf_xa_cas_ad_read` AS `xf_xa_cas_ad_read_Read_6` ON (`xf_xa_cas_ad_read_Read_6`.`ad_id` = `xf_xa_cas_ad`.`ad_id` AND `xf_xa_cas_ad_read_Read_6`.`user_id` = '1')
LEFT JOIN `xf_xa_cas_ad_watch` AS `xf_xa_cas_ad_watch_Watch_7` ON (`xf_xa_cas_ad_watch_Watch_7`.`ad_id` = `xf_xa_cas_ad`.`ad_id` AND `xf_xa_cas_ad_watch_Watch_7`.`user_id` = '1')
LEFT JOIN `xf_reaction_content` AS `xf_reaction_content_Reactions_8` ON (`xf_reaction_content_Reactions_8`.`content_type` = 'cas_ad' AND `xf_reaction_content_Reactions_8`.`content_id` = `xf_xa_cas_ad`.`ad_id` AND `xf_reaction_content_Reactions_8`.`reaction_user_id` = '1')
LEFT JOIN `xf_bookmark_item` AS `xf_bookmark_item_Bookmarks_9` ON (`xf_bookmark_item_Bookmarks_9`.`content_type` = 'cas_ad' AND `xf_bookmark_item_Bookmarks_9`.`content_id` = `xf_xa_cas_ad`.`ad_id` AND `xf_bookmark_item_Bookmarks_9`.`user_id` = '1')
LEFT JOIN `xf_xa_cas_category` AS `xf_xa_cas_category_Category_10` ON (`xf_xa_cas_category_Category_10`.`category_id` = `xf_xa_cas_ad`.`category_id`)
LEFT JOIN `xf_xa_cas_category_watch` AS `xf_xa_cas_category_watch_Watch_11` ON (`xf_xa_cas_category_watch_Watch_11`.`category_id` = `xf_xa_cas_category_Category_10`.`category_id` AND `xf_xa_cas_category_watch_Watch_11`.`user_id` = '1')
LEFT JOIN `xf_deletion_log` AS `xf_deletion_log_DeletionLog_12` ON (`xf_deletion_log_DeletionLog_12`.`content_type` = 'cas_ad' AND `xf_deletion_log_DeletionLog_12`.`content_id` = `xf_xa_cas_ad`.`ad_id`)
WHERE (`xf_xa_cas_ad`.`category_id` IN (126, 127, 213, 129, 182, 11, 144, 145, 270, 13, 139, 172, 178, 271, 210)) AND ((`xf_xa_cas_ad`.`ad_state` IN ('visible', 'deleted', 'moderated'))) AND (`xf_xa_cas_ad`.`ad_status` = 'open')
ORDER BY `xf_xa_cas_ad`.`last_update` DESC
Run Time: 0.645228

Select Type	Table	Type	Possible Keys	Key	Key Len	Ref	Rows	Extra
SIMPLE	xf_xa_cas_ad	ref	category_create_date,category_last_update,category_ad_type,category_ad_status,ad_status	ad_status	1	const	214722	Using index condition; Using where; Using filesort
SIMPLE	xf_attachment_CoverImage_4	eq_ref	PRIMARY,content_type_id_date	PRIMARY	4	xenforo3.xf_xa_cas_ad.cover_image_id	1	Using where
SIMPLE	xf_xa_cas_category_Category_10	eq_ref	PRIMARY	PRIMARY	4	xenforo3.xf_xa_cas_ad.category_id	1	Using index

1. XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 96
2. XF\Db\AbstractAdapter->query() in src/XF/Mvc/Entity/Finder.php at line 1474
3. XF\Mvc\Entity\Finder->fetchRaw() in src/XF/Mvc/Entity/Finder.php at line 1489
4. XF\Mvc\Entity\Finder->fetchColumns() in src/addons/AL/FilterFramework/Service/ContextProvider.php at line 750
5. AL\FilterFramework\Service\ContextProvider->executeFacetedSearch() in src/addons/AL/CASFilter/App.php at line 29
6. AL\CASFilter\App::AL\CASFilter\{closure}()
7. call_user_func() in src/addons/AL/CASFilter/XenAddons/CAS/ControllerPlugin/FilterContainerTrait.php at line 202
8. AL\CASFilter\XenAddons\CAS\ControllerPlugin\AdList->applyAdFilters() in src/addons/XenAddons/CAS/ControllerPlugin/AdList.php at line 38
9. XenAddons\CAS\ControllerPlugin\AdList->getAdListData() in src/addons/XenAddons/CAS/Pub/Controller/Ad.php at line 45
10. XenAddons\CAS\Pub\Controller\Ad->actionIndex() in src/addons/AL/CASFilter/XenAddons/CAS/Pub/Controller/Ad.php at line 16
11. AL\CASFilter\XenAddons\CAS\Pub\Controller\Ad->actionIndex() in src/XF/Mvc/Dispatcher.php at line 352
12. XF\Mvc\Dispatcher->dispatchClass() in src/XF/Mvc/Dispatcher.php at line 258
13. XF\Mvc\Dispatcher->dispatchFromMatch() in src/XF/Mvc/Dispatcher.php at line 115
14. XF\Mvc\Dispatcher->dispatchLoop() in src/XF/Mvc/Dispatcher.php at line 57
15. XF\Mvc\Dispatcher->run() in src/XF/App.php at line 2485
16. XF\App->run() in src/XF.php at line 524
17. XF::runApp() in index.php at line 2

And then this one at .3s:

1. SELECT `xf_xa_cas_ad`.*, `xf_user_User_1`.*, `xf_xa_cas_ad_feature_Featured_2`.*, `xf_xa_cas_ad_sponsor_Sponsored_3`.*, `xf_attachment_CoverImage_4`.*, `xf_attachment_data_Data_5`.*, `xf_xa_cas_ad_read_Read_6`.*, `xf_xa_cas_ad_watch_Watch_7`.*, `xf_reaction_content_Reactions_8`.*, `xf_bookmark_item_Bookmarks_9`.*, `xf_xa_cas_category_Category_10`.*, `xf_xa_cas_category_watch_Watch_11`.*, `xf_deletion_log_DeletionLog_12`.*
   FROM `xf_xa_cas_ad`
   LEFT JOIN `xf_user` AS `xf_user_User_1` ON (`xf_user_User_1`.`user_id` = `xf_xa_cas_ad`.`user_id`)
   LEFT JOIN `xf_xa_cas_ad_feature` AS `xf_xa_cas_ad_feature_Featured_2` ON (`xf_xa_cas_ad_feature_Featured_2`.`ad_id` = `xf_xa_cas_ad`.`ad_id`)
   LEFT JOIN `xf_xa_cas_ad_sponsor` AS `xf_xa_cas_ad_sponsor_Sponsored_3` ON (`xf_xa_cas_ad_sponsor_Sponsored_3`.`ad_id` = `xf_xa_cas_ad`.`ad_id`)
   LEFT JOIN `xf_attachment` AS `xf_attachment_CoverImage_4` ON (`xf_attachment_CoverImage_4`.`content_type` = 'cas_ad' AND `xf_attachment_CoverImage_4`.`content_id` = `xf_xa_cas_ad`.`ad_id` AND `xf_attachment_CoverImage_4`.`attachment_id` = `xf_xa_cas_ad`.`cover_image_id`)
   LEFT JOIN `xf_attachment_data` AS `xf_attachment_data_Data_5` ON (`xf_attachment_data_Data_5`.`data_id` = `xf_attachment_CoverImage_4`.`data_id`)
   LEFT JOIN `xf_xa_cas_ad_read` AS `xf_xa_cas_ad_read_Read_6` ON (`xf_xa_cas_ad_read_Read_6`.`ad_id` = `xf_xa_cas_ad`.`ad_id` AND `xf_xa_cas_ad_read_Read_6`.`user_id` = '1')
   LEFT JOIN `xf_xa_cas_ad_watch` AS `xf_xa_cas_ad_watch_Watch_7` ON (`xf_xa_cas_ad_watch_Watch_7`.`ad_id` = `xf_xa_cas_ad`.`ad_id` AND `xf_xa_cas_ad_watch_Watch_7`.`user_id` = '1')
   LEFT JOIN `xf_reaction_content` AS `xf_reaction_content_Reactions_8` ON (`xf_reaction_content_Reactions_8`.`content_type` = 'cas_ad' AND `xf_reaction_content_Reactions_8`.`content_id` = `xf_xa_cas_ad`.`ad_id` AND `xf_reaction_content_Reactions_8`.`reaction_user_id` = '1')
   LEFT JOIN `xf_bookmark_item` AS `xf_bookmark_item_Bookmarks_9` ON (`xf_bookmark_item_Bookmarks_9`.`content_type` = 'cas_ad' AND `xf_bookmark_item_Bookmarks_9`.`content_id` = `xf_xa_cas_ad`.`ad_id` AND `xf_bookmark_item_Bookmarks_9`.`user_id` = '1')
   LEFT JOIN `xf_xa_cas_category` AS `xf_xa_cas_category_Category_10` ON (`xf_xa_cas_category_Category_10`.`category_id` = `xf_xa_cas_ad`.`category_id`)
   LEFT JOIN `xf_xa_cas_category_watch` AS `xf_xa_cas_category_watch_Watch_11` ON (`xf_xa_cas_category_watch_Watch_11`.`category_id` = `xf_xa_cas_category_Category_10`.`category_id` AND `xf_xa_cas_category_watch_Watch_11`.`user_id` = '1')
   LEFT JOIN `xf_deletion_log` AS `xf_deletion_log_DeletionLog_12` ON (`xf_deletion_log_DeletionLog_12`.`content_type` = 'cas_ad' AND `xf_deletion_log_DeletionLog_12`.`content_id` = `xf_xa_cas_ad`.`ad_id`)
   WHERE (`xf_xa_cas_ad`.`category_id` IN (126, 127, 213, 129, 182, 11, 144, 145, 270, 13, 139, 172, 178, 271, 210)) AND ((`xf_xa_cas_ad`.`ad_state` IN ('visible', 'deleted', 'moderated'))) AND (`xf_xa_cas_ad`.`ad_status` = 'open')
   ORDER BY `xf_xa_cas_ad`.`last_update` DESC
   
   LIMIT 15
   Run Time: 0.330600
   

   Select Type	Table	Type	Possible Keys	Key	Key Len	Ref	Rows	Extra
   SIMPLE	xf_xa_cas_ad	ref	category_create_date,category_last_update,category_ad_type,category_ad_status,ad_status	ad_status	1	const	214722	Using index condition; Using where; Using filesort
   SIMPLE	xf_user_User_1	eq_ref	PRIMARY	PRIMARY	4	xenforo3.xf_xa_cas_ad.user_id	1
   SIMPLE	xf_xa_cas_ad_feature_Featured_2	eq_ref	PRIMARY	PRIMARY	4	xenforo3.xf_xa_cas_ad.ad_id	1
   SIMPLE	xf_xa_cas_ad_sponsor_Sponsored_3	eq_ref	PRIMARY	PRIMARY	4	xenforo3.xf_xa_cas_ad.ad_id	1
   SIMPLE	xf_attachment_CoverImage_4	eq_ref	PRIMARY,content_type_id_date	PRIMARY	4	xenforo3.xf_xa_cas_ad.cover_image_id	1	Using where
   SIMPLE	xf_attachment_data_Data_5	eq_ref	PRIMARY	PRIMARY	4	xenforo3.xf_attachment_CoverImage_4.data_id	1	Using where
   SIMPLE	xf_xa_cas_ad_read_Read_6	eq_ref	user_id_ad_id,ad_id	user_id_ad_id	8	const,xenforo3.xf_xa_cas_ad.ad_id	1
   SIMPLE	xf_xa_cas_ad_watch_Watch_7	ref	PRIMARY,ad_id_email_subscribe	ad_id_email_subscribe	4	xenforo3.xf_xa_cas_ad.ad_id	1	Using where; Using index
   SIMPLE	xf_reaction_content_Reactions_8	ref	content_type_id_user_id,content_type_id_reaction_date,reaction_user_id	content_type_id_reaction_date	31	const,xenforo3.xf_xa_cas_ad.ad_id	1	Using where
   SIMPLE	xf_bookmark_item_Bookmarks_9	range	user_id_content_type_content_id,content_type_content_id	user_id_content_type_content_id	31		1	Using where
   SIMPLE	xf_xa_cas_category_Category_10	ALL	PRIMARY				16	Using where
   SIMPLE	xf_xa_cas_category_watch_Watch_11	range	PRIMARY,node_id_notify_on	PRIMARY	4		1	Using where
   SIMPLE	xf_deletion_log_DeletionLog_12	eq_ref	PRIMARY	PRIMARY	31	const,xenforo3.xf_xa_cas_ad.ad_id	1	Using where

   1. XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 96
   2. XF\Db\AbstractAdapter->query() in src/XF/Mvc/Entity/Finder.php at line 1428
   3. XF\Mvc\Entity\Finder->fetch() in src/addons/XenAddons/CAS/ControllerPlugin/AdList.php at line 166
   4. XenAddons\CAS\ControllerPlugin\AdList->getAdListData() in src/addons/XenAddons/CAS/Pub/Controller/Ad.php at line 45
   5. XenAddons\CAS\Pub\Controller\Ad->actionIndex() in src/addons/AL/CASFilter/XenAddons/CAS/Pub/Controller/Ad.php at line 16
   6. AL\CASFilter\XenAddons\CAS\Pub\Controller\Ad->actionIndex() in src/XF/Mvc/Dispatcher.php at line 352
   7. XF\Mvc\Dispatcher->dispatchClass() in src/XF/Mvc/Dispatcher.php at line 258
   8. XF\Mvc\Dispatcher->dispatchFromMatch() in src/XF/Mvc/Dispatcher.php at line 115
   9. XF\Mvc\Dispatcher->dispatchLoop() in src/XF/Mvc/Dispatcher.php at line 57
   10. XF\Mvc\Dispatcher->run() in src/XF/App.php at line 2485
   11. XF\App->run() in src/XF.php at line 524
   12. XF::runApp() in index.php at line 20

 

[kontrabass]

Bob,

After we upgraded to XF 2.3.4 and CAS 2.3.6 (about 2 weeks ago) our database locks up. We had to forcefully kill FPM and some of these queries to recover and now they seem to have stopped running and things are working now...

Here is a clip of some of the processlist:

| 38550918 | xf2014               | 127.0.0.1:34958 | xenforo3 | Execute | 177  | 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` = 1037420) 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    |
| 38550919 | xf2014               | 127.0.0.1:34964 | xenforo3 | Execute | 176  | 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` = 1148204) 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    |
| 38550920 | xf2014               | 127.0.0.1:34966 | xenforo3 | Execute | 177  | 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` = 1094702) 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    |
| 38550927 | xf2014               | 127.0.0.1:34984 | xenforo3 | Execute | 176  | 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` = 953463) 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    |
| 38550928 | xf2014               | 127.0.0.1:34986 | xenforo3 | Execute | 176  | 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` = 1179813) 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    |
| 38550931 | xf2014               | 127.0.0.1:34992 | xenforo3 | Execute | 176  | 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` = 1055402) 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    |
| 38550932 | xf2014               | 127.0.0.1:34998 | xenforo3 | Execute | 177  | 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` = 972034) 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    |
| 38550933 | xf2014               | 127.0.0.1:35000 | xenforo3 | Execute | 176  | 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` = 1022405) 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    |
| 38550936 | xf2014               | 127.0.0.1:35006 | xenforo3 | Execute | 176  | 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` = 1083259) 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    |
| 38550937 | xf2014               | 127.0.0.1:35008 | xenforo3 | Execute | 176  | 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` = 1338914) 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    |
| 38550938 | xf2014               | 127.0.0.1:35010 | xenforo3 | Execute | 175  | 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` = 1121401) 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    |
| 38550940 | xf2014               | 127.0.0.1:35016 | xenforo3 | Execute | 176  | 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` = 1077388) 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    |
| 38550939 | xf2014               | 127.0.0.1:35012 | xenforo3 | Execute | 175  | 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` = 1323278) 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    |
| 38550941 | xf2014               | 127.0.0.1:35018 | xenforo3 | Execute | 176  | 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` = 1322699) 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    |
| 38550943 | xf2014               | 127.0.0.1:35022 | xenforo3 | Execute | 175  | 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` = 1052200) 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    |
| 38550944 | xf2014               | 127.0.0.1:35024 | xenforo3 | Execute | 175  | 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` = 961114) 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    |
| 38550946 | xf2014               | 127.0.0.1:35030 | xenforo3 | Execute | 176  | 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` = 1037733) 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    |
| 38550947 | xf2014               | 127.0.0.1:35034 | xenforo3 | Execute | 176  | 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` = 1036416) 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    |
| 38550948 | xf2014               | 127.0.0.1:35038 | xenforo3 | Execute | 175  | 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` = 1414990) 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

 

[Bob]

Bob,

After we upgraded to XF 2.3.4 and CAS 2.3.6 (about 2 weeks ago) our database locks up. We had to forcefully kill FPM and some of these queries to recover and now they seem to have stopped running and things are working now...

Here is a clip of some of the processlist:

| 38550918 | xf2014               | 127.0.0.1:34958 | xenforo3 | Execute | 177  | 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` = 1037420) 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    |
| 38550919 | xf2014               | 127.0.0.1:34964 | xenforo3 | Execute | 176  | 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` = 1148204) 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    |
| 38550920 | xf2014               | 127.0.0.1:34966 | xenforo3 | Execute | 177  | 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` = 1094702) 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    |
| 38550927 | xf2014               | 127.0.0.1:34984 | xenforo3 | Execute | 176  | 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` = 953463) 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    |
| 38550928 | xf2014               | 127.0.0.1:34986 | xenforo3 | Execute | 176  | 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` = 1179813) 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    |
| 38550931 | xf2014               | 127.0.0.1:34992 | xenforo3 | Execute | 176  | 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` = 1055402) 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    |
| 38550932 | xf2014               | 127.0.0.1:34998 | xenforo3 | Execute | 177  | 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` = 972034) 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    |
| 38550933 | xf2014               | 127.0.0.1:35000 | xenforo3 | Execute | 176  | 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` = 1022405) 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    |
| 38550936 | xf2014               | 127.0.0.1:35006 | xenforo3 | Execute | 176  | 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` = 1083259) 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    |
| 38550937 | xf2014               | 127.0.0.1:35008 | xenforo3 | Execute | 176  | 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` = 1338914) 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    |
| 38550938 | xf2014               | 127.0.0.1:35010 | xenforo3 | Execute | 175  | 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` = 1121401) 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    |
| 38550940 | xf2014               | 127.0.0.1:35016 | xenforo3 | Execute | 176  | 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` = 1077388) 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    |
| 38550939 | xf2014               | 127.0.0.1:35012 | xenforo3 | Execute | 175  | 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` = 1323278) 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    |
| 38550941 | xf2014               | 127.0.0.1:35018 | xenforo3 | Execute | 176  | 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` = 1322699) 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    |
| 38550943 | xf2014               | 127.0.0.1:35022 | xenforo3 | Execute | 175  | 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` = 1052200) 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    |
| 38550944 | xf2014               | 127.0.0.1:35024 | xenforo3 | Execute | 175  | 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` = 961114) 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    |
| 38550946 | xf2014               | 127.0.0.1:35030 | xenforo3 | Execute | 176  | 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` = 1037733) 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    |
| 38550947 | xf2014               | 127.0.0.1:35034 | xenforo3 | Execute | 176  | 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` = 1036416) 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    |
| 38550948 | xf2014               | 127.0.0.1:35038 | xenforo3 | Execute | 175  | 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` = 1414990) 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

All of those pertain to CAS Comments, which are based on XFMG Comments for standardization purposes. Could be an issue with the XFMG Comments System or something that changes in XFMG Comments that XF didn't mention and I need to do some updates.

Keep me updated on this if it happens again!

 

[kontrabass]

All of those pertain to CAS Comments, which are based on XFMG Comments for standardization purposes. Could be an issue with the XFMG Comments System or something that changes in XFMG Comments that XF didn't mention and I need to do some updates.

Keep me updated on this if it happens again!

Ahh. Ok will do. Also, post upgrade, all of our ITEM pages load slowly. Which is different than pre-upgrade when the CAS index would take longer. Now the index loads instantly, but there is always a several second delay on item pages (https://www.talkbass.com/classifieds/ashdown-studio-speaker-stool.1665256/ for example).

 

[Bob]

Ahh. Ok will do. Also, post upgrade, all of our ITEM pages load slowly. Which is different than pre-upgrade when the CAS index would take longer. Now the index loads instantly, but there is always a several second delay on item pages (https://www.talkbass.com/classifieds/ashdown-studio-speaker-stool.1665256/ for example).

Just FYI... that is not a CAS Item page, that is a CAS Ad page. CAS Item pages are for multi-item Classified Ads and have a different URL and structure.

That Ad has 2 comments on it. Did you test ads that don't have any comments to see if the same thing happens? Could very well be comment related.

I don't have this problem myself, so troubleshooting is nearly impossible. The only thing that I can recommend is making sure that the recommendation made by Xon (which you applied and it instantly fixed the slow load times on your server) is STILL applied. I've had several people that upgraded their servers lately and never re-applied Xon's fix (and once they did, all was well again).

 

[kontrabass]

Thanks Bob. Confirming that Xon's fix is still applied. Meanwhile, I have determined that ads with comments and without take the same (slow) time to load. I'm looking back over this thread and re-educating myself

 

[Bob]

Thanks Bob. Confirming that Xon's fix is still applied. Meanwhile, I have determined that ads with comments and without take the same (slow) time to load. I'm looking back over this thread and re-educating myself

The only other thing that comes to mind is maybe some missing index's on various CAS tables. Start a Ticket with me so that we can troubleshoot INDEXs on CAS Tables on your server.