13

Our production environment has a pretty much standard analytics configuration. MongoDb gets filled up as expected and collected data is up to date.

But the Analytics dashboard in Sitecore is not up to date. It shows data with a 10 day delay, so no interactions from 10 days ago till now but correct data before that. Also in the last 6 hours the visits bar of the last processed day grew around 25000 visits.

So it seems that the processing service is kinda working correctly but is fully occupied and can't process any faster. Is that possible? Are there any configs we can tweak or is only more hardware doing the trick? Or maybe even outsourcing the reporting service to a dedicated server?

  • I have an answer that I will post, but I will be able to add more to it if you provide a little more information about your infrastructure by answering the following questions: Is your instance scaled or standalone? What roles (if any) do you have and how many? Are you using xDB Cloud or xDB Cloud 2.0? What version of Sitecore are you on? – Zachary Kniebel Mar 1 '17 at 16:13
  • Thanks @ZacharyKniebel. It's Sitecore 8.1 Update 2. We're not using xDB Cloud. It's 1 CM + 6 CD Server. Not sure about roles. – z00mable Mar 1 '17 at 17:08
  • CM and CD are your roles ;) I'm writing my post up now – Zachary Kniebel Mar 1 '17 at 17:17
19

To answer your first question, yes, it is absolutely possible that the server fulfilling the Processing role is occupied leading to processing taking a back-seat. Having said that, 10 days is quite a long time so we first have to rule out that something else isn't at play. I would also hazard to say that it is highly unlikely that a single aggregation is taking a whole 10 days to run, so I think we can backlog that possibility for the moment.

Infrastructure and Recent Changes

Before doing any major troubleshooting, be sure to cover the low-effort basic questions that could help identify/explain/resolve your issue:

  1. Have there been any recent changes to infrastructure? Adding additional CDs, a dedicated Reporting server (N/A to you, based on your comments), a dedicated Processing server (also N/A to you for the same reason), changing or replacing your CM (atypical cause, but possible if scalability settings aren't identical) could all result in a desynchronization of the Collections (MongoDB) and Reporting (SQL) databases
  2. Have you performed one or more of the actions listed below in the "Reasons for Rebuilding the Reporting Database" section that are cause to rebuild the reporting database? If any of the reasons do apply to you, it is possible that the delay is due to the Collections (MongoDB) and Reporting (SQL) databases being out of sync.
  3. Have you added any new processors for Tracking, Reporting or Processing or made any recent changes to the processing agents? Such changes could have bugs that may be resulting in the delayed aggregation.
  4. Has the performance of your CM been consistently slow, to the point where the CPU and/or RAM are maxing out on a regular basis? This could result in a general slow-down of your CM, which could cause numerous side-effects, including but not limited to a significantly slowdown in aggregation and scheduled agent execution.
  5. Have you seen any errors in your logs related to processing, aggregation, reporting, tracking, the scheduler, agents (general; non-specific unless it's the aggregation agent), or your MongoDB connection? Such errors could indicate a breakdown that could propagate and affect aggregation or prevent aggregation from running in its entirety.
  6. Have you reviewed your Collections (MongoDB) database to ensure that your instances are historically and currently getting the correct amount of throughput, aren't maxing out on resources (CPU/RAM), and aren't maxing out on connections? Such issues could result in issues retrieving data to be aggregated, thus causing the delay but explaining why the data does eventually appear.

Reasons for Rebuilding the Reporting Database

Per Sitecore's documentation, you should rebuild the reporting database if any of the following apply to you. If any of these do apply to you, it is possible that the delay is due to the Collections (MongoDB) and Reporting (SQL) databases being out of sync:

You have used the conversion tool to populate the reporting database with analytics data from an earlier version of Sitecore.

You have made changes to the collection database – a rebuild is necessary to reflect the changes in reports that use older data. For example, if you have changed the channel associated with one of your referring sites.

You have reclassified reporting data – In Experience Analytics and other Sitecore reporting applications it is possible to reclassify data that has already been processed by the aggregation layer. For example, if you have reclassified a search key word or channel, aggregated report data is not automatically updated. This can cause the reporting database to become out of sync with the collection database.

Your databases are out of sync – if the reporting database has been lost or has become out of sync with the collection database, for example, due to a disaster or if contacts have been merged. The accuracy of the reporting database can decrease over time as more contacts are merged and more interactions are imported. How often you need to rebuild the reporting database depends on your system architecture and the amount of merges and imports you need to process.

Understanding Analytics Processing

Before we dive into lower-level troubleshooting (e.g. config audits, hardware changes, new instance setup, etc.) let's make sure that we have all of our ducks in a row as far as understanding how processing works and what it does, at a high level (adapted from Sitecore's docs):

  1. Visitor interactions are saved to the collections database (MongoDB) on Session_End
  2. "Collected" interaction data is added to the processing pool in order to "queue" it up for aggregation (processing)
  3. A processing agent running on Sitecore's scheduler runs, checks the processing pool for new interactions, then passes the "queued" interactions to the aggregator for processing
  4. The aggregator runs the aggregation pipeline over the interactions, which converts (groups, condenses, etc.) the data into a form suitable for the SQL Reporting Database
  5. The converted data is then merged into the existing reporting data stored in the SQL Reporting Database

Troubleshooting "Scheduled" Processing

From the symptoms that you described in the OP, it sounds like processing works when it actually runs, but the problem is that it isn't being told to run often enough. The key piece in the processing flow (as described above) to consider then is how processing is actually kicked off. Recall that Sitecore relies on a scheduled agent to kick off processing, which can result in processing not running, if the agent's scheduled interval is too large.

The Problem with Agents

Remember that all agents run on Sitecore's scheduler and that the greatest pitfall of Sitecore's schedule is that it's interval-based and not date-time-based. In other words, when you configure an agent, you have to set an interval at which the agent should run (e.g. "run every 30 minutes"), as there is no support for setting a specific date-time when you want the agent to run (e.g. "run at 3:05 PM every Tuesday").

The fact that Sitecore's agents cannot be set to run at a specific time presents a durability risk for any feature/code/logic/etc. that is dependent on an agent to run. To help understand this risk, consider the following situation:

One has an agent that should run once per every 24 hour period (i.e. 24-hour interval). Assuming that the application was started at 7:00 AM, one would expect that the agent will run shortly after 7:00 AM (or a little later, depending) the next day. However, at 2:00 AM the application pool recycled (could be a crash, could have been scheduled, ... doesn't matter). Assuming that one is aware that the app pool recycle occurred, one must now expect that the agent in question will run shortly after 2:00 AM (or a little later, depending) the following day.

The situation above describes the start of how an infinite (or near-infinite) "delay" of an agent's execution can occur as a result of Sitecore's scheduler not supporting the scheduling of agents to run at a specific date-time. Were one to be able to schedule an agent for a specific date time, the agent in question would have run at 7:00 PM both the first and second day. However, with the interval scheduling, the agent did not run the first day, will not run the second day, and may not run the third day either, if there is another app pool recycle before the interval has elapsed.

Troubleshooting the Agent

First, have a look at your Sitecore and IIS logs and try to find if/when the app pool recycled over the previous two weeks. The app pool recycling could indicate a reason why the agent might not run, but it doesn't eliminate the agent not running as a possibility if a recycle never occurred.

Next, open up your configs and have a look at your processing agents. Make sure that they are configured and that they are set to run at a relatively short interval (I believe the OOTB setting is 15 seconds). If it is not set to the OOTB setting or a relatively low setting then do the following:

  1. Set the interval to a lower setting and test. Note that setting the interval too high can result in the agent never running, while setting it too low can cause collisions between agent executions (just unnecessary overhead, as Sitecore shouldn't run the agent more than once at a time) or too many requests (not super likely with batch aggregation, but it could happen) to the collections DB (MongoDB) or the reporting DB (SQL).
  2. Check the MaxThreads setting and ensure that your processing agents are neither using too many or too few threads (this shouldn't be your issue, since it looks like the problem is that aggregation isn't being started often enough, not that it is running too slowly)
  3. Replace your agent's type with a custom inheriting type and have the custom type log a message whenever it runs. This way, you will know exactly when the agent runs without affecting the actual functionality.
  4. Remove any custom processors from the processing/aggregation pipelines and retest each of the above

If your agent is running (you see a message in the logs) but you still don't see any data from the last 10 days (after waiting about 30 minutes or so) then move onto the "Other Troubleshooting Steps" section.

If your agent is not running, then you now know what you need to debug, and you can either post another question here or in Sitecore Community Slack for additional support, as needed.

Other Troubleshooting Steps

If your agent is running and you still don't see any data from the last 10 days, then the next thing you should do is troubleshoot the actual processing of the data.

  1. Audit your CM's Sitecore configuration. Pull down a fresh copy of the Config Enable/Disable Excel spreadsheet from Sitecore's docs and ensure that your CM (based on the infrastructure you mentioned: 1 CM, 6 CD, no dedicated processing or reporting) has all of the necessary configs enabled for CM + Processing. This is because your CM is fulfilling the CM and Processing roles. To that end, you may want to also want to verify that the necessary Reporting configs are enabled, as your CM also fulfills the Reporting role. While it is possible that a bug with your reporting configuration could be causing this issue, it is less likely, unless recent changes have been made to your infrastructure or reporting
  2. Failing any new discoveries from step 1, proceed with troubleshooting the remainder of the processing flow, as described in "Troubleshooting the Processing Pipeline" section, below.
  3. Backup both your collections (MongoDB) and reporting (SQL) databases and then execute history processing (aka rebuild the reporting database). This should help bring your Collections DB and Reporting DB back into sync, if something caused them to lose sync.
  4. Reach out to Sitecore Support if none of the above worked

Troubleshooting the Processing Pipeline

  1. Remove/disable any custom aggregation processors that you may have
  2. Add a custom processor to the top of your aggregation pipeline, and see if it gets called. As before you can do this by logging a message - this time also indicating whether or not the pipeline is aborted.
  3. If the processor you added is called and the pipeline is not aborted, then move the process to the end of the pipeline.

If your processor still gets called and the pipeline isn't aborted then your aggregator may either have a problem with getting the data from collections (MongoDB) or something else could be at play. At this point, I would reach out to Sitecore support for further assistance.

Vertical/Horizontal Scaling and Processing Performance Tuning

If after troubleshooting the issue, you conclude that you need to improve the performance of your processing engine, then you can do so by tuning the Batch Aggregator, tuning the aggregation agent, vertically scaling your environment or any subset of the three.

Tuning the Batch Aggregator

When tuning the Batch Aggregator, you have three options:

  • Tune the Batch Aggregation Agent (docs)
  • Tune the Aggregator (docs)
  • Tune both

The docs do a pretty good job of describing how to tune each. Note that tuning the Batch Aggregator is all about controlling how much you are going to aggregate in a given thread, and how much data you plan to actually save to the Reporting Database (SQL) at a given time.

Tuning the Aggregation Agent

You can customize the performance of your aggregation agents by configuring the number of agents (threads) and the maximum pool size of your agents, following Sitecore's documentation which is pretty descriptive.

Vertical Scaling

The next thing you can try is vertically scaling your environment. Vertical Scaling is when you add more hardware resources (CPU/RAM) to your individual Sitecore instances in order to improve their performance. Vertical Scaling does incur additional hosting costs for the client, but these costs are typically fairly limited as compared to Horizontal Scaling (see then next section).

In your case, if you were to vertically scale your environment to help resolve the issue, then you may consider increasing the CPU/RAM on your CM server, as well as beefing up your MongoDB (more CPU/RAM, load-balancing, etc.) and your SQL instance, as needed.

Horizontal Scaling

Lastly, you can always add more horizontal scaling to your environment. When working with a production site, unless the client has an extra Sitecore license that they aren't using, this is typically my last resort, as it brings a recurring licensing cost to the client.

Horizontal Scaling is when you add additional Sitecore instances to perform dedicated jobs, in order to increase overall environmental performance or performance. When you split a Sitecore environment into a Content Management (CM) instance and a Content Delivery (CD) instance, you are vertically scaling the environment.

In your case, you would be looking to set up either a dedicated Processing server. In doing so, you would disable Processing on your CM thus improving the performance of your CM instance. You would then add an additional instance to your environment that serves the Processing role and only the processing role, thus significantly improving the performance of processing on that instance and consequently the processing performance of the whole environment.

  • Dear Zachary, I am also facing the same issue, my reporting database is delayed more than 13 days, and still, data is processing into the reporting database, around 50 records are updating in every hour - seems very slow? any idea? – Ashish Bansal Jan 3 at 12:28
4

Our team has faced the same issue with analytics failing to populate data and the processing pool DB stacking up records into the 10's of thousands. We adjusted the app_config/sitecore/marketing.xdb/Sitecore.Analytics.Processing.Aggregation.Services.config values for the processing application (we are using xp in a scaled env).

We changed the maximum batch size from 8 to 256 I believe there are 3 places in the config to do this

 <param desc="maximumBatchSize" type="Sitecore.Analytics.Core.ConfigurationHelper, Sitecore.Analytics.Core" factoryMethod="ToShort" arg0="256" />  <param desc="maximumBatchSize">256</param>

and

 <param desc="maximumBatchSize" type="Sitecore.Analytics.Core.ConfigurationHelper, Sitecore.Analytics.Core" factoryMethod="ToShort" arg0="256" />

We also updated the maxAgents values for <param desc="agentPath">aggregation/aggregator</param> and <param desc="agentPath">aggregation/contactProcessing</param> from 1 to 8.

Be cautious if you are using the CM as the processing role for your stack as this will add excess load to the server and cause CPU spikes depending on the number of cores you have for you application processor.

Over a course of about a week we have seen the number decrease from over 100k to about 20k also accounting for the addition of 2500 new records daily.

Hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.