Sitecore Analytics : Exception in GeoIp worker thread

One of our production sites started throwing the below error and occasionally was recycling the Application Pool

Exception Details

[sourcecode language="xml"]Exception: System.Data.SqlClient.SqlExceptionMessage: Transaction (Process ID 3866) was deadlocked on lock 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.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler) at Sitecore.Analytics.Data.DataAccess.DataAdapters.SqlBase.ReadMany(DataSourceQuery query, Action`1 handler) at Sitecore.Analytics.Data.DataAccess.DataAdapters.Sql.SqlLookup.GetVisitsWithEmptyLocation() at Sitecore.Analytics.Lookups.GeoIpWorker.ProcessVisits() at Sitecore.Analytics.Lookups.GeoIpWorker.<Wakeup>b__3(Object a)[/sourcecode]

After some research , We found that the SQL Deadlock was occurring due to multiple production servers were performing the GeoIP lookup using maxmind webservice.

This caused 2 issues on our site

  1. Application pool was getting recycled and the above exception were thrown
  2. Our Maxmind Queries were getting consumed at a very high rate

Resolution

  • Disable GeoIP lookup on all the production (CD) servers by setting the below value in the Sitecore.Analytics.config file.The below setting should be set to TRUE on only one server (for example : least loaded server, as a rule - CM server)
  • [sourcecode language="xml"]<setting name="Analytics.PerformLookup" value="false" />[/sourcecode]

  • We also configured the Auto Detection of BOTS in our Analytics configuration so that it does not consume a lot of queries , Analytics.AutoDetectBots: This setting is present in the Sitecore Analytics File and ensures that the auto detection of BOTS is enabled
  • [sourcecode language="xml"] <setting name="Analytics.AutoDetectBots" value="true" />[/sourcecode]

  • Ensure that your Sitecore solution has this include config file enabled (Sitecore.Analytics.ExcludeRobots.config), It contains list of user agent strings that are used by search engines. Requests with following user agent strings will be ignored by Sitecore Analytics and will not be processes.The above analytics file also contains a section called excludedIPAddresses, this setting allows you to specify a range of IP addresses and the requests originating from those IP addresses will be ignored by Sitecore Analytics.

NOTE: This website http://iplists.com/ keeps a list of up to date IP address of search engines.so it would not hurt to check the website for periodical updates to ensure that the IP addresses are up to date

Summary

Once we did the above changes, everything was back to normal and it also greatly reduced our consumption of maxmind web service queries

Should you have any questions, Please reach me on twitter @sjain_hi or email me at sjain@horizontalintegration.com