Showing posts with label xDB. Show all posts
Showing posts with label xDB. Show all posts

Thursday, September 21, 2023

Sitecore xDB - Troubleshooting xDB Index Rebuilds on Azure

Standard
In my previous post, I shared some important tips to help ensure that if you are faced with an xDB index rebuild, you can get it done successfully and as quickly as possible.

I mentioned a lot of things in the post, but now, I want to mention common reasons where and why things can go wrong, and highlight the most critical items that impact the rebuild speed and stability.


Causes of Need To Rebuild xDB Index

Your xDB relies on your shard database's SQL Server change tracking feature in order to ensure that it stays in sync. This basically determines how long changes are stored in SQL. As mentioned in Sitecore's docs, the Retention Period setting is set to 5 days for each collection shard. 

So, why would 5-day old data not be indexed in time?
  • The Search Indexer is shut down for too long
  • Live indexing is stuck for too long
  • Live indexing falls too far behind

Causes of Indexing Being Stuck or Falling Behind, and Rebuild Failures

High Resource Utilization: Collection Shards 
99% of the time, this is due to high resource utilization on your shard databases. Basically, if you see your shard databases hitting above 80% DTUs, you will run into this problem.

High Resource Utilization: Azure Search or Solr
If you have a lot of data, you need to scale your Azure Search Service or Solr instance.  Sharding is the answer, and I will touch in this further down.

What to check?

If you are on Azure, make sure your xConnect Search Indexer WebJob is running.
Most importantly, check your xConnect Search Indexer logs for SQL timeouts. 

On Azure, the Webjob logs are found in this location: D:\local\Temp\jobs\continuous\IndexWorker\{randomjobname}\App_data\Logs"

Key Ingredients For Rebuild Indexing Speed and Stability

SQL Collection Shards

Database Health 

Maintaining the database indexes and statistics is critically important. As I mentioned in my previous post:  "Optimize, optimize, optimize your shard databases!!!" 

If you are preparing for a rebuild, make sure that you run the AzureSQLMaintenance Stored Procedure on all of your shard databases.

Database Size

The amount of data and the number of collection shards is directly related to resource utilization and rebuild speed and stability. 

Unfortunately, there is no supported way to "reshard" your databases after the fact. We are hoping this will be a feature that is added to a future Sitecore release.

xDB Search Index

Similarly to the collection shards, the amount of data and the number of shards is directly related to resource utilization on both Azure Search and Solr. 

Specifically on Solr, you will see high JVM heap utilization.

If your rebuilds are slowing down or failing, or even if search performance on your xDB index is deteriorating, it's most likely due to the amount of data in your index, the number of shards and distribution amongst nodes that you have set up.  

Search index sharding strategies can be pretty complex, and I might touch on in these in a later post.

Reduce Your Indexer Batch Size

Another item that I mentioned in my previous post. If you drop this down from 1000 to 500 and you are still having trouble, reduce it even further. 

I have dropped the batch size to 250 on large databases to reduce the chance of timeouts (default is 30 seconds) when the indexer is reading contacts and interactions from the collection shards.


Monday, January 3, 2022

Fix Email Campaign Pausing: Sitecore Email Experience Manager 3.x Retry Data Provider

Standard

Background

My company uses Email Experience Manager (EXM) to send several million emails a day, and we have been facing issues where our large campaigns would pause mid-send.

We have a scaled EXM environment with 2 dedicated dispatch servers, and a separate SQL Server, all with appropriate resources so the hardware was not an issue. We also ensured that databases were kept in tiptop condition (proper maintenance plans with stats being updated), and configurations where optimal for our environment.


The causing of the pausing

After digging in, I discovered that the pausing was caused by SQL deadlocks due to the massive amount of records and CRUD activity on the EXM SQL databases.

Sample Exception:

 ERROR Transaction (Process ID 116) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.  
 Exception: System.Data.SqlClient.SqlException  
 Message: Transaction (Process ID 116) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.  
 Source: .Net SqlClient Data Provider  
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)  
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)  
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)  
   at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)  
   at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)  
   at System.Data.SqlClient.SqlDataReader.Read()  
   at System.Data.SqlClient.SqlCommand.CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue)  
   at System.Data.SqlClient.SqlCommand.ExecuteScalar()  
   at Sitecore.Modules.EmailCampaign.Core.Data.SqlDbEcmDataProvider.CountRecipientsInDispatchQueue(Guid messageId, RecipientQueue[] queueStates)  
   at Sitecore.Modules.EmailCampaign.Core.Gateways.DefaultEcmDataGateway.CountRecipientsInDispatchQueue(Guid messageId, RecipientQueue[] queueStates)  
   at Sitecore.Modules.EmailCampaign.Core.Analytics.MessageStatistics.get_Unprocessed()  
   at Sitecore.Modules.EmailCampaign.Core.Analytics.MessageStatistics.get_Processed()  
   at Sitecore.Modules.EmailCampaign.Core.MessageStateInfo.InitializeSendingState()  
   at Sitecore.Modules.EmailCampaign.Core.MessageStateInfo.InitializeMessageStateInfo()  
   at Sitecore.Modules.EmailCampaign.Factory.GetMessageStateInfo(String messageItemId, String contextLanguage)  
   at Sitecore.EmailCampaign.Server.Services.MessageInfoService.Get(String messageId, String contextLanguage)  
   at Sitecore.EmailCampaign.Server.Controllers.MessageInfo.MessageInfoController.MessageInfo(MessageInfoContext data)  
   

How does this new data provider fix the problem?

The new data provider introduces efficient SQL deadlock handling. When a deadlock is detected, it will wait 5 seconds and then retry the transaction. The code will try to execute a deadlocked transaction 3 times.

Configuration

Defaults are set to wait 5 seconds for the retry, and the max retry attempts is 3. The DelaySeconds and RetryCount settings can be modified to suit your needs.

 <configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">  
  <sitecore>  
   <ecmDataProvider defaultProvider="sqlretry">  
    <providers>  
     <clear/>  
     <add name="sqlretry" type="Sitecore.EmailCampaign.RetryDataProvider.RetrySqlDbEcmDataProvider, Sitecore.EmailCampaign.RetryDataProvider" connectionStringName="exm.master">  
      <Logger type="Sitecore.ExM.Framework.Diagnostics.Logger, Sitecore.ExM.Framework" factoryMethod="get_Instance"/>  
      <DelaySeconds>5</DelaySeconds>  
      <RetryCount>3</RetryCount>  
     </add>  
     <add name="sqlbase" type="Sitecore.Modules.EmailCampaign.Core.Data.SqlDbEcmDataProvider, Sitecore.EmailCampaign" connectionStringName="exm.master">  
      <Logger type="Sitecore.ExM.Framework.Diagnostics.Logger, Sitecore.ExM.Framework" factoryMethod="get_Instance"/>  
     </add>  
    </providers>  
   </ecmDataProvider>  
  </sitecore>  
 </configuration>  

Source Code and Documentation

Full source code, documentation and package download is available from my GitHub repository:

https://github.com/martinrayenglish/Sitecore-EXM-3.x-Retry-Data-Provider

Monday, August 31, 2020

Sitecore xDB - Extending xConnect To Reduce xDB Index Sizes And Rebuild Times

Standard

Background

In my previous post, I write about how you can extend the xConnect Data Adapter Provider to prevent unwanted data from being written to your shard databases.

But, what if you already have a lot of analytics and contact data in your shards, have an xDB index issue, and are struggling to rebuild your index?  

Or, what if you want to keep the data in your databases just in case you may need it some time in the future but want to lighten the load on your xDB index to improve performance?

In this post, I will show you how to filter data from your xDB index to reduce the index size and growth rate. This will also improve your rebuild times tremendously.

You can also skip ahead and check out the full source code for my example on my GitHub repo: https://github.com/martinrayenglish/Test.SCExtensions


Business Scenario

In a 9.1 project, I was faced with a very slow rebuild times, where it was literally taking weeks to rebuild our xDB index. I was very concerned with the amount of data we were ingesting into the xDB index from the shard databases, specifically the rate at which interactions where being added. I have an older post this talks about this topic, so give it a read if you have not already.

Prior to 9.2, there is no true supported way to purge old interaction data, and so I was faced with the question of how I could reduce the amount of interaction data that our xDB index contained, and preventing our business from being paralyzed for weeks waiting for a rebuild to complete in the future.

Solving The Problem

As sheer quantity of interactions was the problem, I wanted to be able to filter both new and existing interactions from being brought in from the shard databases during a rebuild or after the session end data aggregation process.

Round 1

In my initial exploration, I focused on customizing the index writer code as that seemed to be the sweet spot.  The code below shows an example of where I customized it to remove ALL interactions from making their way to the xDB index (see line 28 where I simply new up a new blank list of InteractionDataRecord objects):

To apply the updated writer, I put the new assembly into
{xconnect}\bin
and
{xconnect}\App_data\jobs\continuous\IndexWorker

 and then updated the XML files to use new writer in {xconnect}\App_data\jobs\continuous\IndexWorker\App_data\Config\Sitecore\SearchIndexer\sc.Xdb.Collection.IndexWriter.Solr.xml
and
{xconnect}\App_data\config\sitecore\SearchIndexer\sc.Xdb.Collection.IndexWriter.Solr.xml

 <IIndexWriter>  
         <Type>Test.SCExtensions.Xdb.Collection.Search.Solr, Test.SCExtensions</Type>  
         <As>Sitecore.Xdb.Collection.Indexing.IIndexWriter, Sitecore.Xdb.Collection</As>  
         <LifeTime>Singleton</LifeTime>  
  </IIndexWriter>  

This worked well for new interactions coming in from the shard databases after session end, but did not work for rebuild operations so I needed to explore further.

Round 2

The problem that I discovered after my initial try, was that the SolrWriter type specified above is not used by the rebuilder because of the dependency injection implementation details. so writing the custom implementation of the rebuilder class was not a valid option.

I started looking into other options to override the related logic.

After investigating further, I discovered that a custom implementation of both the IndexRebuilderFilter and IndexWriterFilter would take care of both new interactions from sessions and existing interactions coming in via a rebuild operation.  

I created 2 new classes to perform these tasks that I used to decorate the related interface. 

Again, in these examples, I am removing ALL interactions from my xDB index. In a real world scenario, it would be best to filter based on analytics data that is valuable to your business so that it will be available via the xDB search API. 

I show you how to do this further down in my post: More Bang - Selective xDB Index Filtering.

Next, I created decorator XML configs to allow xConnect to use my new writer extension code.

Rebuilder writer config location: {xconnect}\App_Data\jobs\continuous\IndexWorker\App_data\config\sitecore\SearchIndexer\sc.Xdb.Collection.Indexing.IndexRebuilderFilterDecorator.xml

 <?xml version="1.0" encoding="utf-8"?>  
 <Settings>  
   <Sitecore>  
     <XConnect>  
       <SearchIndexer>  
         <Services>  
           <IIndexRebuilderFilterDecorator>  
             <Type>Sitecore.XConnect.Configuration.ServiceDecorator`2[[Sitecore.Xdb.Collection.Indexing.IIndexRebuilder, Sitecore.Xdb.Collection], [Test.SCExtensions.Xdb.Collection.Search.Solr.IndexRebuilderFilterDecorator, Test.SCExtensions]], Sitecore.XConnect.Configuration</Type>  
             <LifeTime>Singleton</LifeTime>  
           </IIndexRebuilderFilterDecorator>  
         </Services>  
       </SearchIndexer>  
     </XConnect>  
   </Sitecore>  
 </Settings>  
Writer config location: {xconnect}\App_Data\jobs\continuous\IndexWorker\App_data\config\sitecore\SearchIndexer\sc.Xdb.Collection.Indexing.IndexWriterFilterDecorator.xml
 <?xml version="1.0" encoding="utf-8"?>  
 <Settings>  
   <Sitecore>  
     <XConnect>  
       <SearchIndexer>  
         <Services>  
           <IIndexWriterFilterDecorator>  
             <Type>Sitecore.XConnect.Configuration.ServiceDecorator`2[[Sitecore.Xdb.Collection.Indexing.IIndexWriter, Sitecore.Xdb.Collection], [Test.SCExtensions.Xdb.Collection.Search.Solr.IndexWriterFilterDecorator, Test.SCExtensions]], Sitecore.XConnect.Configuration</Type>  
             <LifeTime>Singleton</LifeTime>  
           </IIndexWriterFilterDecorator>  
         </Services>  
       </SearchIndexer>  
     </XConnect>  
   </Sitecore>  
 </Settings>  

After applying these updates, I was able to successful filter interactions coming from the shard databases to my xDB index during both rebuilds and session ends!

More Bang - Selective xDB Index Filtering

As you don't necessarily want to remove all interactions from the xDB index unless you are in dire straights, I added a new class for filtering interactions based on specific events. In other words, if an interaction has an event in it that we care about, we allow it to go into the index.


By using this example, you can lighten your index, and still include interactions that are important to you for personalization etc. 

This full source code for this example can be found on my GitHub repo: https://github.com/martinrayenglish/Test.SCExtensions

Monday, June 22, 2020

Sitecore xDB - Extending xConnect To Reduce xDB Growth

Standard

Background

As I mentioned in a previous post,  putting a Sitecore environment into Production with xDB enabled means opening up the flood gates for a massive amount of data flowing into the platform as it starts collecting interactions and events for both anonymous and identified contacts.

xDB Storage Smarts

Just because you can store everything in xDB, it doesn't necessary mean that you should store everything. 

xDB is a marketing database. As a rule of thumb, you should only store data in xDB if it's required for:
  • Personalization
  • Targeting
  • Measurement / Analytics

It is important to plan for the data requirements early, and again, only store the data that you really need to!

Remove xDB Analytics Data

In version 9.2 and later, xConnect allows you to delete contacts and all associated data in xDB if you choose to. So, you have the ability to remove the data that isn't useful to you anymore. https://doc.sitecore.com/developers/93/sitecore-experience-platform/en/deleting-contacts-and-interactions-from-the-xdb.html

But, you need to do this by writing code, and so you would need to work with your Sitecore architect and developers to carefully plan this out for your implementation. And again, this is only an option for the newer versions of the platform.

Extend xConnect To Filter xDB Analytics Data

One good option that I have implemented is to prevent analytics data that is not valuable to your business from getting into xDB in the first place. xConnect allows you to extend the XConnectDataAdapterProvider, and this allows you to hook into the sweet spot where the session data is being written to the shard databases for storage.



For my implementation, I extended the data adapter provider and added configuration to only goals and other events that I explicitly specify, to be recorded in my collection database.

I hope that this helps with your implementation!

Sunday, June 14, 2020

Sitecore xDB - How To Fix Right To Be Forgotten Breaking EXM Campaigns

Standard

Background

Our email manager faced an Email Experience Manager (EXM) issue where when starting or resuming an email campaign, it will start initializing and then it will go directly to a paused state.

Errors

The logs of our Content Management Server revealed the following errors: 

Cause

After digging in, I discovered that this was caused by contacts in our lists that where missing an Alias identifier. This is a dependency for EXM in the way it pulls in the necessary contact data during dispatch. 

What had happened was that our email manager had used the Anonymize feature in the Experience Profile dashboard that executes the right to be forgotten xConnect API under the covers. This process removes the Alias identifier from the contact record in our key xDB shard database tables. 



Checking the xDB Database

If you have SQL experience, you will be pleasantly surprised to find that the xDB databases are not that complicated to work with it all.

Executing the following SQL query helped me find the contacts that where missing contact identifiers:


If you compare what a normal contact looks like vs an anonymized contact, you can see that the records have been wiped from the key ContactIdentifiers and ContactIdentifiersIndex tables.

This is the SQL statement that I used:


Normal Contact


Anonymized Contact



Another important point to note is that when a contact has been anonymized, a new ConsentInformation facet as added to the contact.

 


The Fix

I simply wrote a SQL statement that would insert new Alias identifier records in the ContactIdentifiers and ContactIdentifiersIndex tables for the contacts in my lists that had been anonymized.


Running this SQL statement fixed the missing dependency that EXM required for its email dispatch, and our email campaigns starting sending again.



Sunday, June 7, 2020

Sitecore xDB - Solr xDB Index Troubleshooting Postman Collection

Standard

Background

Over time, I have saved a series of Solr queries that I have found to be extremely useful in understanding, troubleshooting and maintaining xDB Solr indexes.  Some are especially useful when performing an xDB index rebuild.

Getting Started

After downloading the Sitecore xDB.postman_collection.json file, you can install it in your Postman application by clicking the "Import" button located in the top-left corner of the app and selecting the file downloaded on your computer.

After doing this, you will see the Sitecore xDB collection appear in your Postman app:



The core / index name, and Solr url are variables, so the next thing you want to do is set the values for your configuration.

Click on the Sitecore xDB collection, and then the more actions option (3 dots) will appear. Click on Edit, and then navigate to Variables.  




Set the solr_url variable to your Solr instance url, and the xdb_index to match the index you want to work with.  

There are two xConnect Solr cores: the live core (usually xdb) and the rebuild core, which has a _rebuild suffix (like xdb_rebuild). So, you can set this to either value, depending on the core you want to work with.


That's it! You should be ready to test.

Working with the collection

The collection has the following queries available to help with your troubleshooting or maintenance.

xDB Contacts Count

This will return the number of contacts in your index (shown in the numberFound value that is returned). 

When you start an index rebuild, depending on the core you have configured, you will see this number initially be 0, and then gradually start to increase as contacts get added to the core from your shard databases. 

Obviously, this is also useful to see how many total contacts you have in your index over time.



xDB Interactions Count

This returns the number of interactions in your index (shown in numberFound value that is returned). 

xDB Docs Count

This returns the total number of documents in your xDB core. This is a combination of contacts and interactions.

xDB Sync Token

This returns the sync token which is a key component of the Sitecore xConnect / xDB optimistic concurrency model. See more information about it here: https://sitecore.stackexchange.com/questions/12453/xconnect-indexworker-error-tokens-are-incompatible-they-have-different-set-of#answer-12454

xDB Rebuild Status

As described in one of my previous posts, it will return the status of your index, which is especially useful if you are performing an index rebuild.

Default = 0
RebuildRequested = 1
Starting = 2
RebuildingExistingData = 3
RebuildingIncomingChanges = 4
Finishing = 5
Finished = 6

xDB Schema Modifications

When setting up xDB cores for the first time, you are required to make specific schema modifications to both your xDB and xDB rebuild cores using the Solr Schema API. See the Sitecore docs site for more information: https://doc.sitecore.com/developers/90/platform-administration-and-architecture/en/walkthrough--using-solrcloud-for-xconnect-search.html

This post request contains the JSON schema modifications that need to be applied to each core.  The JSON that is used in the Body of this request can be found in the schema.json file located in the \App_Data\solrcommands folder of your xConnect instance.





xDB Delete Interactions

Do not use this in your Production environment! Like the name implies, it will remove interactions from your index. This is useful for local dev environments, if you need to clean things up in your index. This can also we used as an example for working in with your Solr cores via the API.

Monday, May 18, 2020

Sitecore xDB - Troubleshooting Contacts Moving Slowly Through Marketing Automation Plans

Standard

Background

We ran into an issue on our Sitecore 9.1 Azure PaaS deployment, where contacts were moving extremely slowly through the elements of several of our Marketing Automation plans. The contacts were being enrolled correctly, but when they got to a certain step, the could be stuck for several days.

Our instance includes several high traffic sites, but our plans were not complicated at all.  For example, one was set up like this:

  • Visitor updates their profile, and after clicking submit, a goal is triggered that enrolls the contact into the plan.
  • There is a 1 minute delay.
  • An email is sent.

In this post, I will describe the approach I took to getting to the bottom of this problem.

Getting More Information From Marketing Automation Logs

This first step in any troubleshooting process is to get as much detail from your logs as possible so that you can better understand what could be going wrong. 

One of the key components of Sitecore Marketing Automation, is the task that is responsible for the processing of contacts in the Automation Plans. In Azure, this is the Marketing Automation Operations App Service (ma-ops) WebJob, or the Sitecore Marketing Automation Engine service on a Windows Server. What you need to do is set the logging level to “Information” in the sc.Serilog.xml file. 

The full path of the file location is the following (Azure):
wwwroot/App_Data/jobs/continuous/AutomationEngine/App_Data/Config/sitecore/CoreServices/sc.Serilog.xml

For more information, check the following article: https://kb.sitecore.net/articles/017744

After getting this in place, I was able to rule out the possibility of exceptions being thrown that could have been causing the problem.

Finding the clogged Automation Pool

After digging in, I discovered that the number of contacts in the UI corresponded to the data in the Marketing Automation (ma-db) database’s AutomationPool table.  

These are the other things I discovered:
  • Running a “row count” query against the AutomationPool table determined that there were 100s of millions of records.
  • Checking Azure resource analytics showed large database utilization spikes (100% DTU) that corresponded to high CPU on my ma-ops service (averaging above 70%).
  • Running a profile on the maengine.exe job revealed that getting and processing the data from the SQL server seemed to be the bottleneck.

Fixing the clogged Automation Pool

Database Health

One key assumption here was that my ma-db was healthy, and that its indexes were not seriously fragmented.

In previous posts, I have spoken about how important it is to make sure that regular maintenance is performed on your databases. Same thing applies here. Make sure that you run the AzureSQLMaintenance Stored Procedure on your ma-db regularly.

Increase Pricing Tier If Possible

The analysis determined that both my ma-ops app service and ma-db database were struggling to keep up with the processing load, and so I increased the pricing tier on both to give them more horsepower.

Increase Low Priority Worker Batch Size

The AutomationPool table showed me that almost all of the items had a priority of 80, and would be processed by the LowPriorityWorker. I opted to increase the batch size of this worker, so that it would process more items in each batch.  My worker was initially set to 200, and so I tippled it to 600.

The config file location can be found here: App_Data/jobs/continuous/AutomationEngine/App_Data/Config/sitecore/MarketingAutomation/sc.MarketingAutomation.Workers.xml

<LowPriorityWorkerOptions>
<!-- How long the worker sleeps when there is no work available -->
<Schedule>00:00:20</Schedule>
<!-- The minimum priority of work item to process. -->
<MinimumPriority>0</MinimumPriority>
<!-- The timeout period to use for work items. -->
<WorkItemTimeout>00:00:45</WorkItemTimeout>
<!-- The period after which the work item timeout is set again. -->
<WorkItemTimeoutSchedule>00:00:30</WorkItemTimeoutSchedule>
<!-- The batch size multiplier to use when checking out work items from the pool. -->
<BatchHead>4</BatchHead>
<!-- The batch size to use when checking items out from the pool. -->
<BatchSize>150</BatchSize>
</LowPriorityWorkerOptions>

Clog Removed

After making these adjustments, I kept a close eye on the number of rows in the AutomationPool table.

I am happy to report that they were decreasing at a pleasing rate, and that the data started to appear in the reports in the UI.


Monday, December 9, 2019

Huge xDB and xConnect Improvements in Sitecore 9.3

Standard
With the release of Sitecore 9.3, there is a lot of hype in the community around the new bells and whistles that the updated version of the platform offers.

What I am most excited about are the huge performance and stability improvements in xConnect and xDB, and in this post, I will highlight those key areas.



Search Indexer Rebuild Enhancements

Performance

Sitecore made some very important improvements to the stability of rebuilds in systems under stress scenarios, allowing for faster rebuilds:

  • Implemented batching and parallelization during the rebuild synchronization stage
  • Reduced the default SplitRecordsThreshold from 25,000 to 1,000
  • Reduced the ParallelizationDegree of the rebuild to half
  • The sync stage of rebuild can now finish if there is an indexing cycle where less than 25k of changes are detected.


New Commands

  • Check status of rebuild: -rebuildmonitor / -rm
    • Before you could just check logs for the completion entires
  • Delete obsolete data from index: -cleanuprebuildindex / -cri


Rebuild Resume

If you have a lot of interaction data, rebuilds can take a very long time!

If you have a hiccup during the process, there is finally an option to resume without having to start from scratch! A major feature to say the least.


Sharding Deployment Tool Enhancements

With the 9.3 Database Deployment Tool, we finally have the ability to add and remove shards to the xDB collection store. Adding is so important, as I mentioned in my previous post.

To summarize, additional collection shards means additional SQL compute capacity to serve incoming queries in the distributed configuration, and thus faster query response times and index builds. Additional shards will increase total cluster storage capacity, speed up processing, and offer higher availability at a much lower cost than vertical scaling.

You will be required to run an upgrade tool that will modify your pre 9.3 collection databases to allow for the ability to make these adds and removes to your shards.

Web Tracker Stability

The following improvements have been made to the Web Tracker's stability and performance:

  • Session expiration batching
  • Optimization of the Web Tracker and xConnect communication
  • Optimization of the Web Tracker and Reference Data Service communication


Reference Data Layer Redesign 

There have been a lot of issues in previous versions of 9.x that required patches and SQL database scripts to resolve. See my post on Critical Patches for 9.1.

In 9.3, this Reference Data layer has been completely redesigned from the ground up, with focus the main focus being on performance.

New TVP Provider

Sitecore uses table-valued parameters (TVP) to send aggregated information to SQL, because this is a very fast way to communicate with SQL server. Basically, you can use table-valued parameters to send multiple rows of data to a function or stored procedure without the overhead of creating a temporary table or using tons of parameters.

Sitecore has added a new TVP provider in 9.3, that provides major performance improvements.

Ability to Turn On and Off Performance Counters in Azure

If you are deploying to PaaS, performance counters are now disabled by default just like CMS counters.

With 9.3, you have the ability to switch performance counters off and on for xConnect, xDB and Cortex services.


Tuesday, October 15, 2019

Sitecore xDB Resharding Tool - Unlock xDB Storage and Performance Limitations by Increasing Collection Shards Without Data Loss

Standard

Background

There is currently no way for you to increase the number of xDB collection database shards for an existing deployment, without starting from scratch and losing all your data.

The release of my colleague Vitaly Taleyko's Sitecore xDB Resharding Tool solves this problem for all of us, as it provides us with a migration path from an old shard collection to a new one.

https://github.com/pblrok/Sitecore.XDB.ReshardingTool


The inability to increase shards after deployment is a major problem for enterprise customers using the platform, who may not be aware of how quickly the collection databases will grow over time.

If you are a Sitecore veteran, you have experienced this rapid collection growth in MongoDB. As soon as the platform is "turned on", it starts collecting interactions and events for both anonymous and identified contacts.

Putting a Sitecore environment into Production, means opening up the flood gates for a massive amount of data that you don't have much control over.

xDB Search Index

On the xDB index side of the house, Sitecore has filtered the interactions and contact data in the xDB index to identified contacts only (by default). This is good for simple customers who aren't doing much with the platform.

However, if you have millions of contacts, you will have the same index problems as you may have faced when dealing with anonymous contact data in previous versions mentioned in this blog post from a while ago. There is a solve for this, that I may touch on in a later post.

The 2 Shard SQL Problem

Out of the box, if you install the Sitecore platform using default scripts and SIF or if Sitecore Managed Cloud has set up your environment in Azure, you will have 2 SQL shard collection databases.

The value proposition of Sitecore xDB is the ability to store any events tied to an interaction from any channel in xDB. They have provided a robust set of APIs to allow this.

The problem is that storing hundreds of gigabytes or even terabytes of data requires a very carefully planned strategy, or else, you will fail. It is just a matter of when.

I always have the following scene from Evan Almighty in my head when I talk about this problem:




The bottom line - if you have an enterprise deployment, and are using xDB, the out of the box 2 shard collection database configuration is not enough!

New Deployments

If you are new to the Sitecore platform, you can fix this by using the Shard Map Manager Tool to increase the number of shards. This great post by Kelly Rusk explains how: http://thebitsthatbyte.com/what-is-and-how-to-use-the-sitecore-9-shard-map-manager-tool

Existing / Live Deployments

Bad news if you have an existing deployment.

You will hit bottlenecks as you store more contact, interaction and event data. With limited CPU, storage capacity and memory, database performance will start to suffer and query performance and routine maintenance will slow down.

When it comes to adding resources to support database operations, vertical scaling (aka scaling up which is very easy to do on Azure) has its own set of limits and eventually reaches a point of diminishing returns.

The Negative Ripple Effect on xDB

I have seen cases where due to the massive amount of data stored in the xDB collection shards over time, xConnect Search will fail to keep the xDB index in sync, and xConnect search will stop working.

After this happens, the only option is to rebuild your xDB index, but because of the poor xDB collection database performance, it will take days, if not weeks if you are lucky.

Or, it will simply keep failing.

How Increasing Shards Helps

Adding additional collection shards to xDB means additional SQL compute capacity to serve incoming queries in the distributed configuration, and thus faster query response times and index builds.

Additional shards will increase total cluster storage capacity, speed up processing, and offer higher availability at a much lower cost than vertical scaling.

How the xDB Resharding Tool Helps

As I started working with Vitaly to architect this tool, our first idea was to use the Data Exchange Framework that is used to power the xDB Migration Tool. We had used a customized version of this tool when we migrated from our Sitecore 8.2 deployment to our current 9.1.

We decided to pivot, because we wanted a lightweight tool that could run on any Windows-based machine, and would run directly against SQL and as a result, be much more efficient!

The Beauty of the Tool Part 1: Migrating Your Data

This tool allows you to reshard your Sitecore xDB Collection Databases without any data loss.

What does this mean exactly?!

This tool allows you to migrate your current SQL xDB analytics collection database shards to a new set of xDB analytics collection database shards without losing any of your data.

So, for example if you have 2 shard databases, and want to move up to 4 shard databases, this tool will allow you to migrate over.

For this example, you would set up 4 new shards using SIF (as shown in Vitaly's GitHub readme doc), or use the Shard Map Manager Tool, and then point the tool at your old shards and the new shards and voila! Your data will get migrated over!

The Beauty of the Tool Part 2: Resume Mode

Another fantastic feature that Vitaly added to this tool is "resume mode". If there is a glitch in the migration process, or you need to stop it manually for some reason and resume later, it will remember where it left off and pick the migration right back up!

Battle-Tested and Ready For Download!

This tool has been tested, and I can say that it works, and works well!

You can check the full source code out on Vitaly's GitHub: https://github.com/pblrok/Sitecore.XDB.ReshardingTool

You can download your copy today using this link: https://github.com/pblrok/Sitecore.XDB.ReshardingTool/raw/master/ToolReleases/win-x64.zip


Saturday, August 31, 2019

Sitecore xDB - Optimizing Your xDB Index Rebuild For Speed

Standard
Having performed Sitecore xDB index rebuilds many times with large data sets, I wanted to share some key tips to ensure a successful and speedy rebuild.

My experience has been on Azure PaaS, with both Azure Search and SolrCloud, but these techniques can be applied to on-premise and IaaS as well.

Change The Log Level

Before starting a large rebuild job, it's important to enable the proper logging in case you need to investigate any issues that may arise. By default, the log level is set to Warning. Change it to Information.

Navigate to the: App_Data\jobs\continuous\IndexWorker\App_data\config\sitecore\CoreServices\sc.Serilog.xml file, and change the MinimumLevel to Information: 
   <MinimumLevel>
      <Default>Information</Default>
    </MinimumLevel>

Optimize Your Indexer Batch Size

Don't be over eager with your indexer's batch size setting. This setting determines how many contacts or interactions are loaded per parallel stream during an index rebuild. This setting is found in the following location:
App_Data\jobs\continuous\IndexWorker\App_data\config\sitecore\SearchIndexer\sc.Xdb.Collection.IndexerSettings.xml file:
<BatchSize>1000</BatchSize>

I have had success reducing this size to 500, as it helps execution go faster and prevents you from hitting timeouts during your rebuild when your shard databases are under heavy load.

Reduce Your SplitRecordsThreshold

A good tip from a member of our Sitecore community. By default, this value is set to 25000. Tweaking and reducing this value can also make your rebuilds run faster, and improve your live indexing.

Like the Indexer Batch size, this setting is found in the following location:
App_Data\jobs\continuous\IndexWorker\App_data\config\sitecore\SearchIndexer\sc.Xdb.Collection.IndexerSettings.xml
<SplitRecordsThreshold>25000</SplitRecordsThreshold>

I have had success reducing this size to a little less than half the original value, 12000.

Reduce Your Index Writer's ParallelizationDegree

To decrease load in your Search Service, another good suggestion is to decrease the PrallelizationDegree Setting which is 4 by default.

You will see this in many of the other configs in your xConnect App Services. This setting determines how many parallel streams of data can be processed at the same time.

This setting is found in this location for Solr:
\App_Data\jobs\continuous\IndexWorker\App_data\config\sitecore\SearchIndexer\ sc.Xdb.Collection.IndexWriter.SOLR.xml
 <ParallelizationDegree>4</ParallelizationDegree>​

This setting is found in this location for Azure Search:
\App_Data\jobs\continuous\IndexWorker\App_data\config\sitecore\SearchIndexer\ sc.Xdb.Collection.IndexWriter.AzureSearch.xml
 <ParallelizationDegree>4</ParallelizationDegree>​

I have seen large improvements reducing this value from 4 to 1.

Optimize Your Databases

Optimize, optimize, optimize your shard databases!!!

If you are not already using the AzureSQLMaintenance Stored Procedure on your Sitecore databases, do it today!

This is critically important for not only your shard databases but also your other Sitecore databases like Core, Master and Web.  Marketing Automation and Reference databases also get hammered pretty hard, so make sure that this gets applied and run regularly on these.

Note that this maintenance is 100% necessary. As Grant Killian says: "The expectation that Azure SQL is a 'fully managed solution' is somewhat misleading, as rebuilding query stats and defragmentation are the user’s responsibility."

Sitecore recommends an approach like this: https://techcommunity.microsoft.com/t5/Azure-Database-Support-Blog/Automating-Azure-SQL-DB-index-and-statistics-maintenance-using/ba-p/368974

Schedule an Azure Automation “Runbook” to attend to this after hours for all Sitecore databases.

Run the Rebuild

After all this things have been completed, run the xDB rebuild as Sitecore's docs mentions.  In Kudu, go to site\wwwroot\App_data\jobs\continuous\IndexWorker and execute this command:

.\XConnectSearchIndexer.exe -rr

After this, you will see the magic start with docs in your inactive / rebuild index first be reset to 0, and then counts start gradually increasing.

Unfortunately, there is no way to see how far along you are. There is however a query that you can run against your Azure Search or Solr indexes to check the status.

Azure Search Query
$filter=id eq 'xdb-rebuild-status'&$select=id,rebuildstate

Solr Query
id:"xdb-rebuild-status"

Returned Index Rebuild Status:
Default = 0
RebuildRequested = 1
Starting = 2
RebuildingExistingData = 3
RebuildingIncomingChanges = 4
Finishing = 5
Finished = 6

Rebuild Success

When you run the status query, and you see a "6" (sometimes you will see "5" returned, and that's ok too),  you have a successfully completed the rebuild, and new data will start flowing into your xDB index.

Friday, August 9, 2019

Demystifying Pools and Threads to Optimize and Troubleshoot Your Sitecore Application

Standard

Background

If you are .NET application developer that works on Sitecore or not, it is important to have an understanding of how the Microsoft .NET Common Language Runtime (CLR) Thread Pool works will help you determine how to configure your application for optimal performance and help you troubleshoot issues that may present themselves in high traffic production environments.

This topic has been of great interest to me, and it's understanding has helped me troubleshoot and solve many difficult problems within the Sitecore realm.

I am hoping that this post helps other fellow Sitecore developers who may not be as familiar with the inner workings of the .NET CLR and Thread Pool, to have a starting pointing to understand where potential threading issues may occur if the application you support shows symptoms similar to what I intend to discuss.



Thread Pool and Threads 

To put it simply, a thread pool is a group of warmed up threads that are ready to be assigned work to process. 

The CLR Thread Pool contains 2 types of threads that have different roles.

1) Worker Threads 

Worker threads are threads that process HTTP requests that come into your web server - basically they handle and process your application's logic. 

2) Input/Output (I/O) Completion Port or IOCP Threads 

These threads handle communication from your application's code to a network type resource, like a database or web service.

There is really no technical difference between worker threads and IOCP threads. The CLR Thread Pool keeps separate pools of each simply to avoid a situation where high demand on worker threads exhausts all the threads available to dispatch native I/O callbacks, potentially leading to a deadlock. However, this can still occur under certain circumstances.

Out of the Box / Default Thread Pool Thread Counts 

Minimums 

By default, the number of Worker and IOCP threads that your Thread Pool will have ready for work is determined by the number of processors your server has.

Min Formula: Processor Count =  Thread Pool Worker Threads = Thread Pool IOCP Threads

Example: If you have a server with 8 CPUs, you will start with only 8 worker and 8 IOCP threads.

Maximums 

By default, the maximum number of Worker and IOCP threads is 20 per processor.

Max Formula: Processor Count * 20 =  Max Thread Pool Worker Threads = Max Thread Pool IOCP Threads

Example:  If you have a server with 8 CPUs, the default max worker and IOCP threads will be 20 x 8 = 160.

Safety Switch 

The Thread Pool WILL NOT inject new threads when the CPU usage is above 80%. This is a safely mechanism to prevent overloading the CPU.

The Thread Pool In Action

As requests come into your web server, the Thread Pool will inject new worker or I/O completion threads when all the other threads are busy until it reaches the "Minimum" number for each type of thread.

After this "Minimum" has been reached, the Thread Pool will throttle the rate at which it injects new threads and will only add or remove 1 thread per 500ms / 2 threads per second, or as a thread has completed work and becomes free, whatever comes first.

Through its "hill climbing technique algorithm", it is self-tuning and will stop adding threads and remove them if they are not actually helping improve throughput. The thread injection will continue while there is still work to be done until the "Maximum" number for each thread type has been reached.

As the number of requests is reduced, the threads in the Thread Pool start timing out waiting for new work (if an existing thread stays idle for 15 seconds), and will eventually retire themselves until the pool shrinks back to the minimum.

"Bursty" Web Traffic, Thread Starvation and 503 Service Unavailable

Let's say you have your Sitecore site running on an untuned, single Content Delivery server that has 8 processors with the default Thread Pool thread settings. For the sake of the simple example, let's assume we have an under-powered web service (perhaps used for looking up customer information from a backend CRM system) that under heavy load takes 5 seconds to provide a response to a request. Our developers have not implemented asynchronous programming in this example, and use the HttpWebRequest class.

We start out with 8 warmed up and ready worker and IOCP threads in our Thread Pool.

Now, lets say we have burst of 100 visitors accessing different pages (pages that consume the web service) on our site at the same time. The Thread Pool will quickly assign the 8 threads to handle the first 8 requests that will be busy for the next 5 seconds, while the other 92 sit in a queue. As you can see, it will take many 500ms intervals to catch up with the workload. IIS will wait some time for the threads to get free, so that the requests in queue can be processed. If any thread gets free in the waiting time, then it will be used to process the request. Otherwise IIS will return a 503 Service Unavailable error message. Both the slow web service and the untuned Thread Pool will result in some unhappy visitors seeing the 503 error message.

Looking at this a bit closer, a call to a web service uses one worker thread to execute the code that sends the request and one IOCP thread to receive the callback from the web service. In our case, the Thread Pool is completely saturated with work, and so the callback can never get executed because the items that were queued in the thread pool were blocked.

This problem is called Thread Pool Starvation - we have a "hungry" queue waiting to be served threads from the pool to perform some work, but none are available.

This example is a good reason for using asynchronous programming. With async programming, threads aren’t blocked while requests are being handled, so the threads would be freed up almost immediately.

Optimizing Thread Settings 

The ability to tune / manage thread settings has been available in the .NET framework for ages - since v1.1 actually.

Arguably, the most important settings are the minWorkerThreads and minIOThreads where you can specific the minimum number of threads that are available to your application's Thread Pool out of the gate (overriding the default formula's based on processor count as described above).

Threads that are controlled by these settings can be created at a much faster rate (because they are spawned from the Thread Pool), than worker threads that are created from the CLR's default "thread-tuning" capabilities - 1 thread per 500ms / 2 threads per second when all available threads in the pool are busy.

These and other important thread settings can be set in either your server's machine configuration file (in the \WINDOWS\Microsoft.Net\Framework\vXXXX\CONFIG directory) or with the Thread Pool API.

Beware: Out-of-Process Session State and Redis Client  

Out-of-Process Session State

If you are using Out-of-Process Session State in your Sitecore environment, you need to tune your Thread Pool!

Each of your Sitecore Content Delivery instances are individually configured to query expired sessions from your session store. This mechanism will add a ton of additional request overhead to your CD instances, and if your Thread Pools aren't tuned to handle this, you will find yourself in a Thread Starvation situation.

For more background on how and why this happens, please check out Ivan Sharamok's great post: http://blog.sharamok.com/2018-04-07/prepare-cd-for-experience-data-collection

Redis Client

If you are running your Sitecore environments on Microsoft Azure, you will be using Redis for session management. Sitecore makes use of the StackExchange.Redis client within the platform. Even though the client is built for high performance, it get's finicky if your Thread Pool threads are all busy, the "minimum" has been reached and thread injection slows down. You will start seeing Redis service request timeouts.

It is important for you to go through a Thread Pool tuning exercise to ensure that you don't run into Thread Starvation issues.

The nice thing is that the client prints Thread Pool statistics to your logs with details about worker and IOCP threads, to help you with your tuning exercise.

For more details, follow this Microsoft Redis FAQ link: https://docs.microsoft.com/en-us/azure/azure-cache-for-redis/cache-faq#important-details-about-threadpool-growth

Self-adjusting Thread Settings 

Lucky for us on Sitecore 9 and above, there is a pipeline processor that allows the application to adjust thread limits dynamically based on real-time thread availability (using the Thread Pool API).

By default, every 500 milliseconds, the processor will keep adding 50 to the minWorkerThreads setting via the Thread Pool API until it determines that the minimum number of threads is adequate based on available threads.

In my next post, I intend to explore this processor in detail and provide information on it's self-tuning abilities.

Thursday, September 20, 2018

Sitecore GeoIP - A Developer's Guide To What Has Changed In Sitecore 9

Standard
In my previous post, I took a dive into the 8.x version of the Sitecore GeoIP service from a developer's point of view. Sitecore 9 introduced great improvements to xDB, GeoIP being one of those features.

In this post, I intend to help developers understand what has changed in Sitecore 9 GeoIP.  Like my previous post, the purpose is to arm developers with the necessary details to understand what is happening under the hood, so that they can successfully troubleshoot a problem if one arises.


Reference Data

One of the first things that I discovered when diving into version 9 is the use of a series of "ReferenceDataClientDictionaries" that are exposed to us as "KnownDataDictionaries".

As inferred by the name, these are known collections of things that are used to store common data, one being IP Geolocation data. The data is ultimately stored in a SQL database, so that it can be referenced throughout the Experience Platform.

There is a new pipeline in Sitecore 9 that initializes these dictionaries, as shown here:

Config: 

<initializeKnownDataDictionaries patch:source="Sitecore.Analytics.Tracking.config">
<processor type="Sitecore.Analytics.DataAccess.Pipelines.InitializeKnownDataDictionaries.InitializeKnownDataDictionariesProcessor, Sitecore.Analytics.DataAccess"/>
<processor type="Sitecore.Analytics.XConnect.DataAccess.Pipelines.InitializeKnownDataDictionaries.InitializeDeviceDataDictionaryProcessor, Sitecore.Analytics.XConnect" patch:source="Sitecore.Analytics.Tracking.Database.config"/>
</initializeKnownDataDictionaries>

Processor Code:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
namespace Sitecore.Analytics.DataAccess.Pipelines.InitializeKnownDataDictionaries
{
  public class InitializeKnownDataDictionariesProcessor : InitializeKnownDataDictionariesProcessorBase
  {
    public override void Process(InitializeKnownDataDictionariesArgs args)
    {
      Condition.Requires<InitializeKnownDataDictionariesArgs>(args, nameof (args)).IsNotNull<InitializeKnownDataDictionariesArgs>();
      GetDictionaryDataPipelineArgs args1 = new GetDictionaryDataPipelineArgs();
      GetDictionaryDataPipeline.Run(args1);
      Condition.Ensures<DictionaryBase>(args1.Result).IsNotNull<DictionaryBase>("Check configuration, 'getDictionaryDataStorage' pipeline  must set args.Result property with instance of DictionaryBase type.");
      args.LocationsDictionary = new LocationsDictionary(args1.Result);
      args.ReferringSitesDictionary = new ReferringSitesDictionary(args1.Result);
      args.GeoIpDataDictionary = new GeoIpDataDictionary(args1.Result);
      args.UserAgentsDictionary = new UserAgentsDictionary(args1.Result);
      args.DeviceDictionary = new DeviceDictionary(args1.Result);
    }
  }
}

If you look at line 13 above, the GeoIpDataDictionary object being created is inherited from Sitecore's new ReferenceDataDictionary.

This is the glue between GeoIP and the new Reference Data "shared storage" mechanism.

Here is what the code looks like:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
namespace Sitecore.Analytics.DataAccess.Dictionaries
{
  public class GeoIpDataDictionary : ReferenceDataDictionary<Guid, GeoIpData>
  {
    public GeoIpDataDictionary(DictionaryBase dictionary, int cacheSize)
      : base(dictionary, "GeoIpDataDictionaryCache", XdbSettings.GeoIps.CacheSize * cacheSize)
    {
      this.ReadCounter = AnalyticsDataAccessCount.DataDictionariesGeoIpsReads;
      this.WriteCounter = AnalyticsDataAccessCount.DataDictionariesGeoIpsWrites;
      this.CacheHitCounter = AnalyticsDataAccessCount.DataDictionariesGeoIpsCacheHits;
      this.DataStoreReadCounter = AnalyticsDataAccessCount.DataDictionariesGeoIpsDataStoreReads;
      this.DataStoreReadTimeCounter = AnalyticsDataAccessCount.DataDictionariesGeoIpsDataStoreReadTime;
      this.DataStoreWriteTimeCounter = AnalyticsDataAccessCount.DataDictionariesGeoIpsDataStoreWriteTime;
    }

    public GeoIpDataDictionary(DictionaryBase dictionary)
      : this(dictionary, XdbSettings.GeoIps.CacheSize)
    {
    }

    public override TimeSpan CacheExpirationTimeout
    {
      get
      {
        return TimeSpan.FromSeconds(600.0);
      }
    }

    public override Guid GetKey(GeoIpData value)
    {
      return value.Id;
    }

    public string GetKey(Guid id)
    {
      return id.ToString();
    }
  }
}


Notice on line 25 that this object is cached for 10 minutes. More on this below.

Reference Data Storage and the GeoIP Lookup Flow

You may be wondering how this Reference Data feature changes what you know about the GeoIP flow from previous versions of the platform.

Let's review the steps:

  • Sitecore runs the CreateVisits pipeline. Within this pipeline, there is a processor called UpdateGeoIpData that fires a method called GeoIpManager.GetGeoIpData within Sitecore.Analytics.Tracking.CurrentVisitContext that initiates the GeoIP lookup for the visitor's interaction.

  • Sitecore performs a GeoIP data lookup in the GeoIP memory cache.
    • NOTE: Cache expiration is set to 10 seconds => TimeSpan.FromSeconds(10.0)

Sitecore.Analytics.Lookups.GeoIpCache:

1
2
3
4
5
6
7
8
    public void Add(GeoIpHandle handle)
    {
      Assert.ArgumentNotNull((object) handle, nameof (handle));
      if (this.cache.Count >= this.maxCount)
        this.Scavenge();
      this.cache.Add(handle.Id, (object) handle, TimeSpan.FromSeconds(10.0));
      AnalyticsTrackingCount.GeoIPCacheSize.Value = (long) this.cache.Count;
    }

  • If the GeoIP data IS in the GeoIP memory cache, then it will attach it to the visitor's interaction.

  • If the GeoIP data IS NOT in the GeoIP memory cache, it performs a lookup in the Reference Data's GeoIpDataDictionary (KnownDictionaries) memory cache.
    • NOTE: Cache expiration is set to 10 minutes => TimeSpan.FromSeconds(600.0). See above for the 10 minute CacheExpirationTimout property on the Sitecore.Analytics.DataAccess.Dictionaries.GeoIpDataDictionary class.

  • If the GeoIP data IS in the Reference Data's GeoIpDataDictionary memory cache, it attaches it to the visitor's interaction and adds it to the GeoIP memory cache.

  • If the GeoIP data IS NOT in the Reference Data's GeoIpDataDictionary memory cache, it performs a lookup in the SQL ReferenceData database and if found, stores the result in the Reference Data's GeoIpDataDictionary cache and GeoIP memory cache, and then attaches it to the visitor's interaction.

  • If the GeoIP data IS NOT in the SQL ReferenceData database, it performs a lookup using the Sitecore Geolocation service and stores the result in the SQL ReferenceData database, the Reference Data's GeoIpDataDictionary cache and GeoIP memory cache, and then attaches it to the visitor's interaction.

Reference Data Storage in SQL

By using SQL Server Management Studio, and opening up the ReferenceData database's DefinitionTypes table, you can see the different types of reference data that is being stored. The GeoIp data type name as you can see below, is called "Tracking Dictionary - GeoIpData".


By looking at the Definitions table, you can see that the data is stored as a Binary data type:


The following SQL Query will return the top 100 GeoIP reference data results:

1
2
3
4
SELECT TOP 100 [xdb_refdata].[DefinitionTypes].Name, [xdb_refdata].[Definitions].Data, [xdb_refdata].[Definitions].IsActive, [xdb_refdata].[Definitions].LastModified, [xdb_refdata].[Definitions].Version
FROM [xdb_refdata].[Definitions]
INNER JOIN [xdb_refdata].[DefinitionTypes] ON [xdb_refdata].[DefinitionTypes].ID = [xdb_refdata].[Definitions].TypeID
WHERE [xdb_refdata].[DefinitionTypes].Name = 'Tracking Dictionary - GeoIpData'



Changes to the GeoIpManager class

Finally, I wanted to provide a glimpse of the changes in the GeoIpManager class that I referenced in my previous post.

By comparing the 8.x version of the GeoIpManager code to 9, you can see the usage of the KnownDataDictionaries.GeoIPs dictionary instead of the Tracker.Dictionaries.GeoIpData (ContactLocation class) from 8.x:



Final Words

I hope that this information helps developers understand more about Reference Data and the updated GeoIP Lookup Flow in Sitecore 9.

As always, feel free to comment or reach me on Slack or Twitter if you have any questions.


Thursday, August 16, 2018

Sitecore GeoIP - What Is Happening Under The Hood In 8.x?

Standard

Background

Most posts explain how Sitecore's GeoIP service works from a high-level point of view.

In this post, I intend to take the explanation a few steps deeper, so that developers can understand all the pieces that make this process work. The goal is to arm developers with the necessary details to successfully troubleshoot a problem if one arises.


Visitor Interaction - Start of visitor's session

  • Visitor visits Sitecore website, and this is regarded as a new interaction. Sitecore's definition of an interaction is ".. any point at which a contact interfaces with a brand, either online or offline". In our case, this is a new visitor session on the website.

  • Sitecore runs the CreateVisits pipeline. Within this pipeline, there is a processor called UpdateGeoIpData that fires a method called GeoIpManager.GetGeoIpData within Sitecore.Analytics.Tracking.CurrentVisitContext that initiates the GeoIP lookup for the visitor's interaction.

  • Within the GeoIP lookup logic, Sitecore will use the visitor's IP address to generate a unique identifier (GUID) based on the visitor's IP address. Eg. 192.168.1.100 => fd747022-dd48-b1ca-1312-eb4ba55030b2. 

NOTE: Sitecore performs all GeoIP lookups using this unique identifier. You can see this id by looking inside your MongoDB's GeoIPs collection. The field is named _id and this is the unique naming convention that MongoDB uses across all of its content. See my previous post for a snapshot.

  • Sitecore performs a GeoIP data lookup in memory cache.

  • If the GeoIP data IS in memory cache, then it will attach it to the visitor's interaction.

  • If the GeoIP data IS NOT in memory cache, it performs a GeoIP lookup in the MongoDB Analytics database's GeoIps collection.

  • If the GeoIP data IS in the MongoDB Analytics database's GeoIps collection, it attaches it to the visitor's interaction and stores the result in memory cache.

  • If the GeoIP data IS NOT in the GeoIps collection, it performs a lookup using the Sitecore Geolocation service and stores the result in memory cache and attaches it to the visitor's interaction.

NOTE: After a successful lookup, the GeoIP data is stored in the Tracker.Current.Interaction.GeoData (ContactLocation class)

GeoIP Data Cache

  • When the GeoIP data is obtained, it is added to a dictionary object that is part of the Sitecore Tracker so that it can be referenced via the Tracker.Current.Interaction.GeoData (shown above).

  • The odd thing that I noticed was that the cache expiration was set to 10 seconds (by default)
          Code reference:
          Sitecore.Analytics.Data.Dictionaries.TrackingDictionary
          private readonly TimeSpan defaultCacheExpirationTimeout = TimeSpan.FromSeconds(10.0);

GeoIP Data - End of visitor's session

  • At the end of the visitor's interaction / session, Sitecore will run the CommitSession pipeline.

  • Like the CreateVisits pipeline, there is a processor called UpdateGeoIpData that fires a method called GeoIpManager.GetGeoIpData (with the exact same code as in the CreateVisits pipeline). This initiates the GeoIP lookup flow once again (Cache / MongoDB / GeoIP Service).

  • Seems like the intention here is to confirm the visitor's GeoData before storing the data in MongoDB that will ultimately make it's way to the reporting database.

More To Come

Next, I intend to dig into Sitecore's GeoIP code for the 9.x series, and talk about the differences identified in that implementation.