MemberService high CPU / DTU usage (database)

MichaelRevolvo · March 12, 2025, 12:40pm

Hi there.

We’re running an Umbraco web app on Azure App Service. I’ts running on 4 web server instances, and a single database with 400 DTUs.

Most of the time, the app is running just fine, even with 2-400 requests per second. But we’re noticing spikes in the database DTU usage, where it spikes from ~30 to 100% usage. If this happens during peak hours, it can sink the app due to requests timing out, which results in even more requests from the users.

I think I’ve narrowed it down to this query:

(@0 uniqueidentifier,@1 int,@3 bigint,@2 bigint)SELECT [cmsMember].[nodeId] AS [NodeId], [cmsMember].[Email] AS [Email], [cmsMember].[LoginName] AS [LoginName], [cmsMember].[Password] AS [Password], [cmsMember].[passwordConfig] AS [PasswordConfig], [cmsMember].[securityStampToken] AS [SecurityStampToken], [cmsMember].[emailConfirmedDate] AS [EmailConfirmedDate], [cmsMember].[failedPasswordAttempts] AS [FailedPasswordAttempts], [cmsMember].[isLockedOut] AS [IsLockedOut], [cmsMember].[isApproved] AS [IsApproved], [cmsMember].[lastLoginDate] AS [LastLoginDate], [cmsMember].[lastLockoutDate] AS [LastLockoutDate], [cmsMember].[lastPasswordChangeDate] AS [LastPasswordChangeDate] , [umbracoContentVersion].[id] AS [ContentVersionDto__Id], [umbracoContentVersion].[nodeId] AS [ContentVersionDto__NodeId], [umbracoContentVersion].[versionDate] AS [ContentVersionDto__VersionDate], [umbracoContentVersion].[userId] AS [ContentVersionDto__UserId], [umbracoContentVersion].[current] AS [ContentVersionDto__Current], [umbracoContentVersion].[text] AS [ContentVersionDto__Text], [umbracoContentVersion].[preventCleanup] AS [ContentVersionDto__PreventCleanup] , [umbracoContent].[nodeId] AS [ContentDto__NodeId], [umbracoContent].[contentTypeId] AS [ContentDto__ContentTypeId] , [umbracoNode].[id] AS [ContentDto__NodeDto__NodeId], [umbracoNode].[uniqueId] AS [ContentDto__NodeDto__UniqueId], [umbracoNode].[parentId] AS [ContentDto__NodeDto__ParentId], [umbracoNode].[level] AS [ContentDto__NodeDto__Level], [umbracoNode].[path] AS [ContentDto__NodeDto__Path], [umbracoNode].[sortOrder] AS [ContentDto__NodeDto__SortOrder], [umbracoNode].[trashed] AS [ContentDto__NodeDto__Trashed], [umbracoNode].[nodeUser] AS [ContentDto__NodeDto__UserId], [umbracoNode].[text] AS [ContentDto__NodeDto__Text], [umbracoNode].[nodeObjectType] AS [ContentDto__NodeDto__NodeObjectType], [umbracoNode].[createDate] AS [ContentDto__NodeDto__CreateDate] , [umbracoNode].[text] AS [variantName] FROM [cmsMember] INNER JOIN [umbracoContent] ON [cmsMember].[nodeId] = [umbracoContent].[nodeId] INNER JOIN [umbracoNode] ON [umbracoContent].[nodeId] = [umbracoNode].[id] INNER JOIN [umbracoContentVersion] ON [umbracoContent].[nodeId] = [umbracoContentVersion].[nodeId] INNER JOIN [cmsContentType] ON [umbracoContent].[contentTypeId] = [cmsContentType].[nodeId] WHERE (([umbracoNode].[nodeObjectType] = @0)) AND ([umbracoContentVersion].[current] = @1) ORDER BY [cmsMember].[LoginName] , [ContentDto__NodeDto__NodeId]

OFFSET @2 ROWS FETCH NEXT @3 ROWS ONLY

But I cannot figure out when Umbraco makes this query. From the looks of it, it would look like it’s the paging query when browsing the “Members” section in Umbraco. But it fires at times when no one is in the backend.

My question is: do any of you have any idea where this query comes from? And is there any way to make it perform better? Maybe some of you have played around which an application layer caching tecnique for the member service?

FYI: We have about 500.000 members in the database

JasonElkin · March 14, 2025, 9:38am

Hi Michael, and welcome to the new forum!

Maybe I can help narrow this problem down a bit.

There are two methods on the MemberService that look to order by LoginName, firstly the GetAll method.

github.com/umbraco/Umbraco-CMS

src/Umbraco.Core/Services/MemberService.cs

78a8d017a


      
          /// <summary>
          /// Gets a list of paged <see cref="IMember"/> objects
          /// </summary>
          /// <param name="pageIndex">Current page index</param>
          /// <param name="pageSize">Size of the page</param>
          /// <param name="totalRecords">Total number of records found (out)</param>
          /// <returns><see cref="IEnumerable{IMember}"/></returns>
          public IEnumerable<IMember> GetAll(long pageIndex, int pageSize, out long totalRecords)
          {
              using ICoreScope scope = ScopeProvider.CreateCoreScope(autoComplete: true);
              scope.ReadLock(Constants.Locks.MemberTree);
              return _memberRepository.GetPage(null, pageIndex, pageSize, out totalRecords, null, Ordering.By("LoginName"));
          }

This is used by the backoffice, as you say, but also when Examine indexes are rebuilt here:

github.com/umbraco/Umbraco-CMS

src/Umbraco.Infrastructure/Examine/MemberIndexPopulator.cs

78a8d017a


      
          protected override void PopulateIndexes(IReadOnlyList<IIndex> indexes)
          {
              if (indexes.Count == 0)
              {
                  return;
              }
          
              const int pageSize = 1000;
              var pageIndex = 0;
          
              IMember[] members;
          
              // no node types specified, do all members
              do
              {
                  members = _memberService.GetAll(pageIndex, pageSize, out _).ToArray();
          
                  // ReSharper disable once PossibleMultipleEnumeration
                  foreach (IIndex index in indexes)
                  {

This file has been truncated. show original

There’s also the FindByUsername method. That doesn’t look to be used by Umbraco itself, but it’s part of the public API.

github.com/umbraco/Umbraco-CMS

src/Umbraco.Core/Services/MemberService.cs

78a8d017a


      
          /// <summary>
          /// Finds a list of <see cref="IMember"/> objects by a partial username
          /// </summary>
          /// <param name="login">Partial username to match</param>
          /// <param name="pageIndex">Current page index</param>
          /// <param name="pageSize">Size of the page</param>
          /// <param name="totalRecords">Total number of records found (out)</param>
          /// <param name="matchType">The type of match to make as <see cref="StringPropertyMatchType"/>. Default is <see cref="StringPropertyMatchType.StartsWith"/></param>
          /// <returns><see cref="IEnumerable{IMember}"/></returns>
          public IEnumerable<IMember> FindByUsername(string login, long pageIndex, int pageSize, out long totalRecords, StringPropertyMatchType matchType = StringPropertyMatchType.StartsWith)
          {
              using ICoreScope scope = ScopeProvider.CreateCoreScope(autoComplete: true);
              scope.ReadLock(Constants.Locks.MemberTree);
              IQuery<IMember>? query = Query<IMember>();
          
              switch (matchType)
              {
                  case StringPropertyMatchType.Exact:
                      query?.Where(member => member.Username.Equals(login));
                      break;

This file has been truncated. show original

MichaelRevolvo · March 14, 2025, 10:02am

Hi Jan.

Thank you very much!
I will try to disable automatic rebuilding of the Examine indexess, and put it on a controlled schedule.

Cheers!

system · March 19, 2025, 10:03am

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.