MongoSessionStateProvider might slow down your application

Challenge:

Since couple of weeks, Have noticed some performance issues with couple of projects, I was involved. Basically their page load was taking 20 seconds to load and when you look at other things like:

1. Sitecore log
2. PublishQueue
3. History
4. EventQueue
5. and so on

Everything seems normal. Then we figured out that we are using MongoSessionStateProvider for these clients and after doing some steps we have been able to load pages in normal load time! So, what we did to tackle the situation? What we learnt during this challenge? Curious to know? We are also curious to share. Here you go:

Solution:

First of all, If Session, SharedSession etc. Confuses you or scares you. Then don’t worry. You are not the only one in this Sitecore world. There are a lot of folks like you. And the best thing is someone already wrote simplified version of it for you – Thanks to Martina! Please read these awesome articles:

Let’s Talk About Session State

What’s in a session: What exactly happens during a session, and how does the xDB know who you are?

https://doc.sitecore.net/sitecore_experience_platform/81/setting_up__maintaining/xdb/session_state/session_state

Enough Basics. Let’s get back to main point. Live site pages were loading in 20 seconds and as it was slow LB was saying you guys [CD servers] are not healthy. I am not giving you any requests to serve. After doing basic check we thought we should take memory dump and analyze it. Dump revealed that Sitecore is waiting for Mongo to respond. So, we though to look in to Mongo DB and after clearing session collection from session and sharedSession Mongo DB. Things came back to normal.

You can do it using RoboMongo you can do it using UI.

But If you don’t have RoboMongo connection. Then you can do it using Mongo Shell Commands : https://docs.mongodb.com/manual/reference/mongo-shell/

• show dbs
• use <db>
• show collections
• db.sessions.drop()

Do it for both session and sharedSession database. That’s it!

Then thought to get views on this issue with Sitecore support ticket and during discussion found one BUG and some learnings which says it is good to use SQL Session State Provider instead of Mongo

Support discussion:

Support Team : If you use two different database, the Shared Session State provider might not create a required index on the session collection. As a results, a MongoDB instance can be high overloaded and start producing symptoms like in your case.

Using Robomongo tool, can you please check if there is the e_1__id.s_1__id.a_1 index in the session collection?

The index definition must look like below:

Name: e_1__id.s_1__id.a_1

Keys:

{

    “e” : 1,

    “_id.s” : 1,

    “_id.a” : 1

}

We : We found this index only on session database. It is there in Session and Not Shared Session DB. Do you want us to create it manually?

Support Team :

Yes, you must create the  e_1__id.s_1__id.a_1 index to avoid overloads of your MongoDB server. Otherwise, the Sitecore.SessionProvider.MongoDB.MongoSessionStateProvider might put down the server when retrieving contact’s sessions for your web site visitors.

Using Robomongo tool, please add manually the index:

• Name: e_1__id.s_1__id.a_1
• Keys:

{

    “e” : 1,

    “_id.s” : 1,

    “_id.a” : 1

}

I have registered this issue as a bug in the current version of Sitecore. To track the future status of this bug report, please use the reference number 114604.

Support Team :  Sitecore Azure modules has been designed to work with xDB Cloud only. Technically, you can bring your own MongoDB server, Processing/Aggregation, Reporting Services and Search server and plug them in, but you have to do it manually, and then reconfigure Editing Farm as Content Management (CM) role and Delivery Farm as Content Delivery (CD) role. Otherwise, the module deploy configuration from a deployment center as is and a farm will behave as CM/CD/Processing/Aggregation, Reporting Services, which cause many collisions and unpredictable behavior.

If you bring your own Sitecore xDB, you must:

    Disable Sitecore xDB Cloud for both Delivery and Editing Farms. Select the /sitecore/system/Modules/Azure/{Environment}/{Location}/{Farm}/Role01/{Staging | Production}/Options/xDB Cloud Edition item and release/uncheck the Enabled check box in the Data section.
    Update the Deployment Type Exclude Files [Applied to specific Deployment types only (CD/CE)] field in the /sitecore/system/Modules/Azure/{Environment}/{Location}/{Farm}/Role01/{Staging | Production} item with a list of disabled *.config files, which you can find in the following article:
        For Delivery Farm – Configure a content delivery server.
        For Editing Farm – Configure a content management server.
    Bring your own:
        Sitecore installation that is configured as the Processing/Aggregation role according to the Configure a processing server article.
        Sitecore installation that is configured as the Reporting Services role according to the Configure a Reporting Service server article.
        Solr server for hosting a distributed sitecore_analytics_index.
        Collection MongoDB Server that is configured according to  Configure a collection database server to the article.
        Reporting SQL Server that is configured according to the Configure a reporting database server article.
        Session database servers that is configured according to the Configuring session database servers article.

For more information what servers Sitecore XP requires please see the Configuring Servers article.

Please note Sitecore xDB Cloud it not only MongoDB server. There are many other components with dedicated servers and services. For more information about xDB Cloud please see the xDB Cloud overview article.

We :  We’ve already done these steps

Support Team : Sitecore XP product is shipped with Sitecore.SessionProvider.MongoDB.MongoSessionStateProvider and Sitecore.SessionProvider.Sql.SqlSessionStateProvider session state providers. Where first uses MongoDB as session storage and the second one uses SQL Server. You can use either one and there isn’t not specific recommendation, but SqlSessionStateProvider provider might be a bit faster, and most of the customers typically prefer using SQL one in combination of Sitecore Azure + Sitecore xDB Cloud.

We : The article presented here . https://doc.sitecore.net/sitecore_experience_platform/81/setting_up__maintaining/xdb/session_state/walkthrough_configuring_a_private_session_state_database_using_the_sql_server_provider/ 
says: “These performance enhancements move the session state store to SQL Server tempDB which is the standard practice recommended by Microsoft. However, this is not supported on Windows Azure.
For more information see Session-State Modes on MSDN.”

Support Team : There is one think you have to do before deploying Sitecore Sessions SQL database to Azure SQL Databases service.

Run the following scrip to drop the CreateTables stored procedure that is not used.

USE [Sitecore_sessions]

GO

/****** Object:  StoredProcedure [dbo].[CreateTables] ******/

IF OBJECT_ID(‘CreateTables’) IS NOT NULL

DROP PROCEDURE [dbo].[CreateTables]

GO

For more information on how to deploy Sitecore databases to Azure SQL Database see the following article:

https://github.com/olegburov/Sitecore-Azure-Content/blob/master/articles/how-to-deploy-sitecore-databases-to-azure-sql-database.md

> Why does the Sitecore document states that performance enhancements are not supported in Windows Azure, can you elaborate and help us understand why?
The performance optimization that the Performance Boost.sql scrip brings is based on the tempdb, which is not supported by Azure SQL Databases. Therefore, you can’t apply the script and have to remove the CreateTables stored procedure, which relates to the script.

Web Analysis:

1. During MVP Summit, lot of people said. Good to use SQL Provider instead of Mongo
2. Good thread : https://community.sitecore.net/developers/f/9/t/118#pi214filter=all&pi214scroll=false “Currently (in SC8), the Session state provider on Mongo is a bit slower than SQL Server (~20%). Apparently this is a thing on the side of the implementation of the session provider itself and it shall be fixed in the upcoming version of Sitecore. The reason for chosing Mongo over SQL is mainly because of simplicity and the characteristic of the session data. You could still use SQL Server with Mongo though.”

   ” At the moment, I would recommend the sql session provider. I have laerned about some performance issues on certain circumstances with the mongo session provider – so right now, I will recommend sql in any situation.”

3. Wait for Sitecore to come up with Redis Provider — Which is happening soon. Redis is a winner for Out Proc session storage — Refer Martina‘s article.

Quick summary:

1. If you can switch to SQL Session State provider do it!
2. If not, then make sure you add Index manually to sharedSession DB