In the last week the insert link button in TinyMCE appeared to start taking longer. Around nine seconds now, it seemed longer when I came in this morning but I didn’t time it then.
It may have been taking awhile but we also upgraded to CF2016 from CF9 which may have created newfound interest in in our system’s problems.
I have noticed that the insert link button throws out an ajax call that triggers anywhere from a few hundred to a few thousand db queries. I am curious why this is, but assuming this behavior is necessary is there anything that can be done to speed up this process? I hear from our sysadmin that our db server doesn’t even break a sweat while we are under a DoS attack, so I’m not sure where the holdup could be.
As a maybe relevant aside both logins and site tree navigation seem to trigger large numbers of db queries. We have 1842 pages and 173 contributors and are running CF2016 and FC 7.2.8.
We run CF2016 in production for some of our clients. Please note that although it is unrelated to your issue, there are some known datetime bugs in CF2016. The most recent hotfix fixes some of them, but not all that affect FarCry. You might also want to update to the latest p720 from git which has a couple CF2016 patches as well. These are likely unrelated to your issue in this bug report, but they may help fix other potential issues you could run into later.
When logging in, and when browsing the site tree, the framework will look up the permissions for the webtop and site tree in the DB and cache them against the combination of user roles. This means that after the application has just started up or been restarted, the first time you log in or the first time that you browse the site tree (including the links view via TinyMCE) it will do the necessary queries to calculate permissions, and after that they should be cached.
If your users have many unique combinations of roles then this could be an issue, typically we try to assign as few roles as necessary to users.
If you are seeing a very large amount of queries every time you log in, or every time you use the site tree, then perhaps there may be some issue that needs to be resolved.
Do you know how fast these things were before? Switching to CF2016 shouldn’t make these things take longer.
As Jeff says it’s a good idea to ensure CF2016 is fully patched to avoid any datetime bugs that it had in their earlier releases. We released 7.2.9 last week but there’s only 1 ACF related bug fix since 7.2.8 but AFAIK this would have been present in older versions too;
Hopefully that gives you some background about how and why those things could potentially be “slow”, and if you have something specific we could look at then we can try to get to the bottom of it and make it snappier
The permissions sounds like a good place to start. I inherited this site a little over a year ago and haven’t dug into permissions yet. Unfortunately I’ll be out on leave for a couple of weeks starting Thursday if not sooner. I doubt I’ll have time to get to the bottom of this week but if streamlining permissions doesn’t help the issue I’ll update here, possibly a few weeks from now.
I know I am getting more complaints about speed now while I had none before, though I noticed speed issues myself before. Our old system is still intact in a seperate VM, I can run some tests and compare against it when I am able.
I’ll also mention the updates to the sysadmin. I’m sure he’ll be so excited to hear about more updates :D. Regardless, the heads up is appreciated.
Digging around a bit more I may have found some insight into caching issues with our site. To clarify it is every time I log in, every time I first enter the site tree and I go more than two levels deep in the site tree, and every time that I click the add link button within TinyMCE that I see a delay.
I have found this in the response headers of some requests:
Cache-Control:no-cache, no-store, must-revalidate
I’m assuming locations I see this or similar noting to not cache would be improved if I changed whatever was dictating this part of the header correct? Are there any central locations withing Farcry or possibly certain strings I should search for to track down these options?
Also, since my complaints center around that link button, would there be any harm in removing the ajax call and just letting it function as part of the text editor until I iron out these slowdowns?
This is required for the webtop, because you don’t want to serve up cached / stale content to your contributors while they are managing content. However, this only affects browser / proxy caching though and it’s not the same type of caching I was talking about above.
Permissions for each role are stored against webtop navigation and site tree navigation nodes in the database – instead of looking these up and calculating the resultant permission sets on every request, the framework caches these results once they are calculated for the first time, which is supposed to avoid subsequent queries to the DB. If permissions on the tree are changed, then those cached results are flushed for the affected roles and they will be calculated and cached again on the next hit.
This should usually be relatively “fast”, so anything that is taking multiple or tens of seconds is concerning.
The other concern is that you said it used to be fast on CF9, and now it’s “slow” on CF2016. Unless you have added huge volumes of nodes to your site tree, or unless you’ve changed all your user roles to have multiple / unique roles per user, then it seems like the most likely explanation might be something related to your environment… For example it could be CF2016 related, or DB server related, or both. Can you share your datasource configuration (sanitise it to remove host names, passwords, etc)? Has the DB moved to a new server? Is the DB missing any indexes that it might have previously had?
It would be super helpful if you could compare specific actions in both systems to see how the performance varies, and even better if they were both able to run directly against the same database so that you could rule out the DB server.
I’ve looked into the issue a little further and feel that I’ve narrowed down the problem area a lot. I followed suggestions here starting with timing the old and new servers. Our old production servers “insert hyperlink” button was consistently measuring between 1.5 and two seconds by my stopwatch, while the updated server was measuring eight to nine seconds or even timing out.
After getting my numbers I ensured the data-source settings were identical between the two, which didn’t change things. Then I tried tying the current production database to the old server, which still ran relatively snappy. I then updated FarCry on the old server from 7.0.14 to 7.2.8 (what we’re running now) and now our old production server gives exactly the same behavior regarding this button as the new server. That sounds to me like it is a difference in the Farcry Version.
We haven’t tested yet, but the good news is if performance becomes too much of a problem here then we could roll back to 7.0.14 until we figure this out. In the mean time, is there any clear differences between the two that could cause this kind of behavior? Maybe there’s some difference in caching behavior between the two out of the box?
To touch on other ideas shared here we do have the current version of CF2016, so we’re good there. And talking to the sysadmin it appears we don’t have any custom indexes set up on our databases right now. This will find its way onto our list I bet, but based on my testing isn’t the cause of our issue here. DB is on the same server as well. VM might be on a different box, but I was able to replicate behavior on the old server so I feel that is a moot point.
Thanks a bunch Justin for your pointers, this narrowed down the scope for me faster than I thought it would. I’m going to be out for a couple of weeks, but I’ll be back to look into this further if others don’t pick this up before I’m back.
Ahhh, I assumed you were running the same version of Core in both places – this will give me something specific to look at. The functionality shouldn’t have changed for a long while but I’ll see what I can find
I am back at work and mostly settled back in (unfortunately going on leave doesn’t mean getting out of work :P). Has anything been learned regarding this slow behavior?
This issue is getting in peoples way it seems, so I’ll make it a priority of mine to get this sorted out. If there’s anything you would like me to verify I’ll be happy to do so. There is also other parts of WebTop aside from the insert link button that are slow, but I assume we want to tackle one issue at a time, unless they are all connected.
In case it helps our old stack was:
Windows Server 2008 R2
IIS7
MSSQL (unchanged on seperate server)
Java 1.6.0_17
Adobe ColdFusion 9
Farcry 7.0.14
New stack is:
Windows Server 2012 R2
IIS 8.5
MSSQL (unchanged on seperate server)
Java 1.8.0_72
Adobe ColdFusion 2016
Farcry 7.2.8
I’m not sure where else to look at this point that I haven’t already written on previously, so let me know if you have any other troubleshooting ideas.
I’ve done some investigation of the code related to the Link button in TinyMCE. The most obvious “problem” is that data about the entire tree will be returned each time you click on the link button. For most sites with a few hundred navigation nodes this should be pretty fast, but I’m guessing that perhaps your site might have a lot more.
There are no permission checks in this place so it shouldn’t be related to permissions or the webtops permission caching, because it’s simply showing all navigation nodes in the site which are available for linking.
When the “Link” button is clicked it does an ajax POST to a URL such as;
(Side note: this example has no friendly URLs because I was just running it in Lucee 5.1 Express without friendly URLs enabled)
I’ve compared the most significant files related to this ajax request between the Core versions 7.0.14 and 7.2.9 (latest release);
\core\webtop\facade\ftajax.cfm - trivial differences only
\core\packages\formtools\richtext.cfc:193 - no difference in ajax method “linkoptions” case
\core\packages\formtools\richtext.cfc:459 - no difference in getLinkOptions method
\core\packages\lib\fapi.cfc:1418 - trivial differences in getLink method
\core\packages\farcry\tree.cfc - no differences
From what I can see, there shouldn’t be any significant performance differences between these versions. This means that we’ll need to be some performance profiling within the request to see if we can pinpoint that place that is causing you problems.
I’ve created a test file which I’ll send to you privately. You’ll need to place it in your webroot and run it from both the old app (if you still can) and the new app to see if we can get some useful debugging information out of it.
When I run it from 7.0.14 I get the following timings for the profiling points (in ms):
And when I run it from 7.2.9 I get the following timings for the profiling points (in ms):
Technically, 7.2.9 is faster than 7.0.14 for a site with a small site tree (I’m using the Fandango skeleton site for these tests).
As for a guess as to what’s causing your problems… There may be some underlying issue that could cause fapi.getLink to be slower which would be noticeable on very large site trees, or perhaps serializeJSON is CF2016 has an issue, or it could be something entirely different We’ll see what kind of results you get when you run the tests.
I have ran the test.cfm file as requested. The downside is that CF2016 doesn’t appear to format these CFDump tags all nice, so I’ll label them myself:
FC 7.0.14:
get metadata: 0
get type: 1
get object data: 8
get latest object from dmwizard: 6
get link options object: 1271
serialize json: 14
total: 1310
FC 7.2.8:
get metadata: 0
get type: 1
get object data: 6
get latest object from dmwizard: 7
get link options object: 6734
serialize json: 22
total: 6777
These were both pointing to the same database on the same DB server, so that variable can be ruled out. Hopefully the only differences here are Farcry, Java, and CF. I don’t know the first place to look for the slowdown myself, but it does appear after your numbers that something here really doesn’t want to scale.
There are 2881 rows in dmNavigation, I know we have about 1800 public pages as well.
One possible stopgap I thought of, if it is the creation of the link list drop down slowing this down maybe I could comment that specific part out for now? Short term sacrificing that functionality to make this button more use able again would make a lot of people happy. I did try modifying \core\packages\formtools\richtext.cfc as directed in you PM and it made no meaningful impact on the numbers.
It definitely seems related to fapi.getLink (probably something underlying in the friendly URLs subsystem) which would be called for each of those navigation nodes.
If you want to “remove” that feature temporarily, you can extend the \core\packages\formtools\richtext.cfc into your project (i.e. place a component in \projects\yourproject\packages\formtools\richtext.cfc), and override the getLinkOptions method to return an empty object. Something like this;
<cfcomponent extends="farcry.core.packages.formtools.richtext" name="richtext" displayname="Rich Text Editor" hint="Used to liase with longchar type fields">
<cffunction name="getLinkOptions" access="public" output="false" returntype="struct">
<cfargument name="stObject" typename="struct" required="true" />
<cfargument name="stMetadata" typename="struct" required="true" />
<cfargument name="relatedTypename" typename="string" required="true" />
<cfset var stResult = structnew() />
<cfset stResult["links"] = arraynew(1) />
<cfreturn stResult />
</cffunction>
</cfcomponent>
The other modification I suggested in the PM should have made improvements, so I’ll have to do some more investigation to get to the bottom of it.
I checked into the size of our farFU table and found 10,112 rows, I could definitely see this plus the 2881 rows in dmNavigation being a big contributor here. dmNavigation may take some larger decisions to consolidate content and prune off chunks of the page (i.e. likely not happening), but it appears that removing old friendly URLs was not in our process, just adding new ones and modifying old ones. I assume between the default FU and a custom FU we should need only times the number of objects in our site at most. I’ll need to dig around our own site and determine what the situation is now and what fixing it would look like, sounds like some quantity of elbow grease going into page cleanup.
A possible help then could be advice on what tables we need to trim down. It sounds like this kind of work could stand to speed up everything else that is slow as well. Optimizing the problem away would be nice (and make me much more confident in FarCry’s scaling), but I’m all for reducing clutter assuming involved parties are willing.
In testing I’ve added your richtext.cfc change as suggested and it makes the link button behave as I would expect it to speed wise. We’ll roll this change out overnight. Personally I find the site tree drop down a bit cumbersome on a site the size of ours and don’t enjoy using it, but that’s another discussion. Another question regarding that drop down, my sysadmin noticed that the site tree drop down on the user account admin interface (where the default home node is set) runs significantly faster. I assume at this point that site tree isn’t pulling FriendlyURLs, either way any differences may provide a hint.
Speaking of making changes overnight, I just realized I hadn’t run an updateapp before testing your first suggestion (oops). I’ve done that now, but it still doesn’t seem to make much difference in the results. At least I removed another variable.
I hope this answers more questions, and it sounds like both FarCry and our website can improve here.
That’s right, with a site tree that large I can’t imaging that the navigation links in that dialog would be too usable, unfortunately that’s the only option that TinyMCE provides in that scenario (i.e. on their built-in Link button).
One workaround could be for you to first add the navigation items you want to link to in the “Related Content” array, and then you can use the “FarCry Templates” button to insert a link to those nav nodes. This might work for you, or it might not if you are using that array for some other purposes.
As far as fixing the issue goes, it’s most likely the join on farFU and refobjects. You might want to check that your farFU table has an index on the refobjectid column, if not then you should be able to deploy the default index in the webtop under Admin -> Developer Tools -> COAPI Content Types.
This join is actually something that I thought I’d wound back before (I believe it’s solved for 2 of our clients because we override this functionality in a plugin). I’ll do some more testing to make sure that any fix is globally more performant
While I’m in work a little late I’ll sneak a reply in here.
I may kick off discussions on the “Related Content” functionality. We generally don’t use, but I’ve seen at least one page try to. I also don’t know if the functionality provided by this “Link List” drop down is being used in the first place, more discussion to decide what we want here I bet.
I did find what I believe is an index between farFU and refobjectid. I’m not well versed in SQL indexes but I saw one named “farFU_refobjectid_index” at least. Perhaps, if this is the one, this index could use some other settings to run better?
I could definitely see a join between farFU and refobjectID exploding. As noted we have 10,112 rows in our farFU table, and I just found 42,843 rows in our refobjectID table. I could also see a need to clean this stuff up, I don’t know if these numbers are unreasonable but they sound like big numbers to me. Another note is that according to FusionReactor the AJAX request that this link button sends out results in 5,183 queries being sent as well.
I’ve been testing with a database that has ~1000 records in dmNavigation, and to cut a long story short;
There are scenarios where some things related to friendly URLs are slower in 7.2.x in the first instance compared to 7.0.x, but are faster for every instance after that due to caching
For this scenario, with the richtext formtool getLinkOptions() method, 7.2.x should be much faster than 7.0.x
Here’s the results in 7.0.14 for the ~1000 dmNavigation records;
And here’s the results in 7.2.9 for the same ~1000 dmNavigation records;
As you can see the code executes in about half the time in 7.2.9.
This makes me wonder if the difference is directly related to CF2016. I’m in the process of building a docker image so that I can test it easily.
Is there a way you can compare 7.0.14 on CF2016 vs 7.2.9 on CF2016?
I just realized a mistake I made in a previous post, my numbers were from a CF9 server (our old production server). As requested I’ve ran the test in CF2016 on the server we first experimented with CF2016 on. This is still tied to the same DB server but is looking at a dev database that’s perhaps a few months behind production.
Seeing my previous numbers tied to CF9 I’m doubting CF2016 being the problem vs CF9, but I could see there being a performance penalty with Adobe CF vs. Lucee (which we considered but ran out of time to pursue).
I think it’s safe to assume there is something in our database that isn’t happening in you test environment. I don’t know FarCry deeply enough to know what was relevant to this difference (If I did I’m sure I’d consider enforcing standards that kept everything happy)
I’ve finished configuring the same version of Core (7.2.9) with the same project code, and the same database (it has ~1000 dmNavigation nodes, and getLinkOptions returns 528 items), so that I could test CF2016 specifically vs the numbers which I’m seeing in Lucee 5.1.
I see the CF2016 results vary between ~500ms up to ~1500ms, while Lucee 5.1 is consistently below 400ms for the same code. However, the difference is not as bad as your number being 3-5 seconds for each run, so while CF2016 seems slower, perhaps there is something else which is datasource / database driver related which is hurting performance?
I went ahead and added output for stResult.links[1].menu (I noticed you added that in) to the test.cfm on our production server and got 2596 items. This is nearly five times what your test database has and scaling your numbers up could explain much of the delay I see.
I think it’s clear that we’re running a rather large database at this point. I don’t know where to start but I something needs to happen there. My testing also appears to show that things did slow down from 7.0.14 to 7.2.8, but obviously the response time isn’t entirely FarCry’s fault.
Since I appear to already have Indexes in place I assume there isn’t much I can do to directly speed up the DB. Would it be too much to get a list of the tables that are effecting this? I already know we could trim back friendly URL’s, maybe even dig out old content if there’s no backlash.
Also, not to make more work for you, did you try out if things slowed down from 7.0.14 to 7.2.9 in CF2016 with the larger DB? That could confirm whether my site is tripping on something in the code or in it’s own data.
