Question

Photo of Greg Lawless

0

How do I increase the database command timeout

4

I'm running in to a problem with universal search. When I enable indexing on a group type that has a large number of groups I am getting "The wait operation timed out" after 30 seconds or so. I get the same thing if I try to save any edits on a group type that already has indexing enabled. Any Ideas

  • Photo of Daniel Hazelbaker

    0

    The timeout is hard coded into the internals of Rock at 30 seconds. There are some individual components that allow you to override the timeout (such as DataViews), but there is no way to change it globally. Enabling indexing does all the heavy lifting in the background on another thread, so it shouldn't cause a timeout (in fact on my server it takes a number of minutes to complete, but the page reloads almost instantly). I would suspect you have something else going on in your database that is causing it to go slow. A couple things to check:

    1. Make sure your Rock Database Maintenance job is running successfully.

    2. If you are self-hosted, check the SQL server CPU usage during one of those timeouts and see if it is maxing out.

    3. If you are Azure hosted, check the SQL server DTU usage during one of those timeouts and see if it is maxing out (you might need to increase the size of your DTU allocation if it is too small).

    4. Check the CPU usage of the IIS server during one of those timeouts and see if it is maxing out.

  • Photo of Greg Lawless

    0

    Thanks for the response! 

    1. The maintenance job was not running so I ran it successfully. This did not resolve the error.

    2. SQL CPU usage was at or below 10% during the operation

    3. IIS CPU usage was at or below 30% during the operation


    The number of groups for each group type we index is in the hundreds with 20 to 50 members in each group and we do have tons of attributes on some of them. Does that make a difference?

    • Daniel Hazelbaker

      Daniel Hazelbaker

      It shouldn't. I'm looking at the code now and I can't see anything that would be causing a timeout related to indexing. As far as I can tell, when you enable indexing on a group type, all it does is set a flag. It doesn't do any actual processing. You might need to go find the exception related to this timeout and post it (the exception title, and the full stack trace) to give more information on exactly where the timeout is happening.

    • Photo of Greg Lawless

      0

      How did you reply to the previous post? I don't see the option to do that.


      Anyhow, here is the stack trace from the group type page:


      Error

      The wait operation timed out

         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.TryConsumeMetaData()

         at System.Data.SqlClient.SqlDataReader.get_MetaData()

         at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)

         at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)

         at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)

         at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

         at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

         at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)

         at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)

         at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

         at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

         at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)

         at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)

         at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()

         at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)

         at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)

         at System.Data.Entity.Core.Objects.DataClasses.EntityReference`1.Load(MergeOption mergeOption)

         at System.Data.Entity.Core.Objects.DataClasses.RelatedEnd.DeferredLoad()

         at System.Data.Entity.Core.Objects.Internal.LazyLoadBehavior.LoadProperty[TItem](TItem propertyValue, String relationshipName, String targetRoleName, Boolean mustBeNull, Object wrapperObject)

         at System.Data.Entity.Core.Objects.Internal.LazyLoadBehavior.<>c__DisplayClass7`2.<GetInterceptorDelegate>b__2(TProxy proxy, TItem item)

         at System.Data.Entity.DynamicProxies.Group_E17290BB248D3DAAF0B6C4E5AEFB4BD34D3557FD8D4AC50BCCDFC22BEEDE6B98.get_GroupType()

         at Rock.UniversalSearch.IndexModels.GroupIndex.LoadByModel(Group group)

         at Rock.Model.GroupType.BulkIndexDocumentsByGroupType(Int32 groupTypeId)

         at Rock.Model.GroupType.PreSaveChanges(DbContext dbContext, EntityState state)

         at Rock.Data.DbContext.RockPreSave(DbContext dbContext, PersonAlias personAlias, Boolean enableAuditing)

         at Rock.Data.DbContext.SaveChanges(Boolean disablePrePostProcessing)

         at RockWeb.Blocks.Groups.GroupTypes.<>c__DisplayClass1f.<btnSave_Click>b__17() in c:\inetpub\Rock\Blocks\Groups\GroupTypeDetail.ascx.cs:line 596

         at Rock.Data.DbContext.WrapTransaction(Action action)

         at RockWeb.Blocks.Groups.GroupTypes.btnSave_Click(Object sender, EventArgs e) in c:\inetpub\Rock\Blocks\Groups\GroupTypeDetail.ascx.cs:line 572

         at System.Web.UI.WebControls.LinkButton.OnClick(EventArgs e)

         at System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument)

         at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

         at RockWeb.Global.Application_Error(Object sender, EventArgs e) in c:\inetpub\Rock\App_Code\Global.asax.cs:line 460

      • Daniel Hazelbaker

        Daniel Hazelbaker

        There is an Add Comment button that appears after you have made a certain number of posts. It looks like I was wrong, as the stack trace clearly shows it trying to re-index. It seems like it's buried pretty deep in the core logic of Group Types. I created an issue on the tracker here: https://github.com/SparkDevNetwork/Rock/issues/3176


        For extra details, can you tell me:



        1. How many groups of this group type there are?

        2. How many Group attributes you have defined on the Group Type?

        3. How many Group Member attributes you have defined on the Group Type?

        4. A bit hard to determine, but to the best of your knowledge, do any of the groups of this type have multiple 10's of thousands of members? (i.e. a single group with 30,000 members)

      • Photo of Greg Lawless

        0

        Here are the requested details:


        1.  Groups: 644
        2. Attributes: 36
        3. Member Attributes: 3
        4. None that large, they average about 14 per group.