(imported comment written by MattBoyd)
Wow! That’s the exact opposite of support!
Our case remains unresolved. I can’t even get a follow-up from devs. In case it helps someone else struggling with this in their 8.2 environment, I was able to somewhat alleviate our issues. In particular, one TEM query that retrievies CUSTOM_SITE_WRITER records seems to be the most significant bottleneck in our deployment. To give you an idea of what kind of impact this has, I used SQL Management Studio to measure the amount of data returned from this
query:
select CSW.Sitename,
U.MastheadUsername,
CSW.UserID,
CSW.IsOwner,
CSW.AuthorizingMasterOperatorID,
CSW.AuthorizingOwnerID,
CSW.ServerID,
CSW.Certificate,
CSW.SupportingCerts,
CSW.SignedData
from CUSTOM_SITE_WRITERS CSW
INNER JOIN USERINFO U
on ( CSW.UserID = U.UserID and U.IsDeleted = 0 )
where CSW.IsDeleted = 0
Each time I ran the query, the result set was around 120 MB in size, which is quite large (in my opinion) for a result set that runs frequently. The query runs 20-30 times per minute on average (as I mentioned previously) depending on the number of operators connected! And, to give you a better baseline, the size of the result set from this query prior to migrating servers was only 24 MB!
Then, I updated the site writer permissions on roughly 20 console operators (I wrote a query to determine which console operators had the largest sum of SiteData in CUSTOM_SITE_WRITERS and performed this operation on them) and measured the result set again. The result set is now 62 MB.
Here’s what I did in the console that reduced the size of SignedData field records associated with particular console operators:
-
Go to Operators in the All Content pane
-
Select the operator, then go to the Sites pane
-
Sort by Explicit permissions, then select all entries in the list with explicit 'Writer Permissions"
-
Click “Reader” to change the permissions then click “Writer” again (without saving) to change them back. Click Save Changes
When I did this, the size of the SignedData field in each associated record from the CUSTOM_SITE_WRITERS table would shrink from ~100 KB to ~12 KB. I also noticed that the certificate and SupportingCerts fields were set to an empty string (in most cases, they contained data prior to performing this operation). However, it appears that the size of your signing key is a factor in the final size of the SignedData field, so your results may vary. The lack of control over this is very frustrating!
The console is somewhat more responsive and retrieving action results is faster, though there’s definitely room for improvement. Also, this doesn’t address the issue with our Actionsite quadrupling in size, though I have a feeling that they’re related.
Here’s the query I wrote to check the size of SignedData in the CUSTOM_SITE_WRITERS table - please draw your own conclusions, doin so at your own risk. In our environment, the total size for some console operators was 4000+ KB before updating the site writer permissions. Prior to migrating, the the size for most console operators was under 100 KB. Obviously, there’s other factors involved, especially the number of sites you have and the number of sites the console operator has explicit writer permissions for.
select U.MastheadUsername,
CSW.UserID,
U.Username,
(SUM(DATALENGTH(CSW.SignedData))/(1024)) as
Total Size of SignedData fields in KB
from CUSTOM_SITE_WRITERS CSW
INNER JOIN USERINFO U
on ( CSW.UserID = U.UserID and U.IsDeleted = 0 )
where CSW.IsDeleted = 0
GROUP BY U.Username, U.MastheadUsername, CSW.UserID
ORDER by
Total Size of SignedData fields in KB
desc
Good luck and please keep sharing anything you find.