Getting Error - [mvexpand] / max_mem_usage_mb has been reached

[sabari80]

getting below error 

ommand.mvexpand: output will be truncated at 3200 results due to excessive memory usage. Memory threshold of 500MB as configured in limits.conf / [mvexpand] / max_mem_usage_mb has been reached.

this is the query i am running

index="dynatrace" sourcetype="dynatrace:usersession"
| spath output=user_actions path="userActions{}"
| mvexpand user_actions
| spath output=pp_user_action_name input=user_actions path=name
| spath output=pp_user_action_response input=user_actions path=visuallyCompleteTime
| where pp_user_action_name like "%newintakeprocess.aspx%"
| eval pp_user_action_name=substr(pp_user_action_name,0,40)
| stats count(pp_user_action_response) As "Total_Calls" ,avg(pp_user_action_response) AS "User_Action_Response" by pp_user_action_name
| eval User_Action_Response=round(User_Action_Response,0)
| sort -Total_Calls

how i can optimize this search and resolve the mvexpand limit issue?

[bowesmana · Accepted solution]

As you just need name and visuallyCompleteTime, you should only spath out those two fields, combine them, filter them for the ones required, expand them, then filter again and finally do the calcs.

That removes ALL unnecessary fields from the expansion, as you would have been expanding the huge _raw as well as a huge user_actions.

This assumes a 1:1 relationship with name and visuallyCompleteTime fields

index="dynatrace" sourcetype="dynatrace:usersession"
``` SPATH out names and visuallyComplete fields ```
| spath output=names path="userActions{}.name" 
| spath output=complete path="userActions{}.visuallyCompleteTime"
``` Filter events that contain this name --SOMEWHERE-- ```
| search names="*newintakeprocess.aspx*"
``` combine the two fields ```
| eval data=mvzip(names, complete, "#!#!#!")
``` and remove ALL others ```
| fields data
| fields - _raw
``` Now EXPAND the data and separate out the fields again ```
| mvexpand data
| eval data=split(data, "#!#!#!")
| eval pp_user_action_name=mvindex(data, 0), pp_user_action_response=mvindex(data, 1)
``` and search again to find the real ones with the filter ```
| search pp_user_action_name="*newintakeprocess.aspx*"
| fields - data
| eval pp_user_action_name=substr(pp_user_action_name,0,40)
| stats count(pp_user_action_response) As "Total_Calls" ,avg(pp_user_action_response) AS "User_Action_Response" by pp_user_action_name
| eval User_Action_Response=round(User_Action_Response,0)
| sort -Total_Calls

[LearningGuy]

@bowesmana 
Your suggested solution solved memory issue. Thank you!! 

[sabari80]

this is working as expected and faster than the other queries. Thanks 🙂

[kamlesh_vaghela]

@sabari80 

Can you please try this?

index="dynatrace" sourcetype="dynatrace:usersession" 
| spath output=user_actions path="userActions{}" 
| stats count by user_actions 
| spath output=pp_user_action_name input=user_actions path=name 
| spath output=pp_user_action_response input=user_actions path=visuallyCompleteTime 
| where pp_user_action_name like "%newintakeprocess.aspx%" 
| eval pp_user_action_name=substr(pp_user_action_name,0,40) 
| stats count(pp_user_action_response) As "Total_Calls" ,avg(pp_user_action_response) AS "User_Action_Response" by pp_user_action_name 
| eval User_Action_Response=round(User_Action_Response,0) 
| sort -Total_Calls

Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.

[sabari80]

its retrieving all the expected results set for yesterday (306098) without any error/exception. It took long time to retrieve the result set. How i can optimize this search?

[kamlesh_vaghela]

@sabari80 

Can you please share some valid sample events So we can help you further?

meanwhile, you can try this as well.

index="dynatrace" sourcetype="dynatrace:usersession" 
| spath output=user_actions path="userActions{}" 
| stats count by user_actions 
| spath output=pp_user_action_name input=user_actions path=name 
| where pp_user_action_name like "%newintakeprocess.aspx%" 
| spath output=pp_user_action_response input=user_actions path=visuallyCompleteTime 
| eval pp_user_action_name=substr(pp_user_action_name,0,40) 
| stats count(pp_user_action_response) As "Total_Calls" ,avg(pp_user_action_response) AS "User_Action_Response" by pp_user_action_name 
| eval User_Action_Response=round(User_Action_Response,0) 
| sort -Total_Calls

KV

[sabari80]

for one day, this search is taking more than 120 seconds.

Here is the sample event 

{ [-]
   applicationType: WEB_APPLICATION
   bounce: false
   browserFamily: MicrosoftEdge
   browserMajorVersion: MicrosoftEdge105
   browserType: DesktopBrowser
   clientType: DesktopBrowser
   connectionType: UNKNOWN
   dateProperties: [ [+]
   ]
   displayResolution: HD
   doubleProperties: [ [+]
   ]
   duration: 2262208
   endReason: USER_ACTION_LIMIT
   endTime: 1667916283243
   errors: [ [+]
   ]
   events: [ [+]
   ]
   hasError: true
   hasSessionReplay: false
   internalUserId: 1667914023413LF1NFVLVTMIJ7EG3T3JKOTBVQAD45EU4
   ip: 10.147.53.109
   longProperties: [ [+]
   ]
   matchingConversionGoals: [ [+]
   ]
   matchingConversionGoalsCount: 0
   newUser: true
   numberOfRageClicks: 0
   numberOfRageTaps: 0
   osFamily: Windows
   osVersion: Windows10
   partNumber: 0
   screenHeight: 720
   screenOrientation: LANDSCAPE
   screenWidth: 1280
   startTime: 1667914021035
   stringProperties: [ [+]
   ]
   syntheticEvents: [ [+]
   ]
   tenantId: 3905aa6f-4130-439f-b336-dd2af9fa40d4
   totalErrorCount: 55
   totalLicenseCreditCount: 1
   userActionCount: 113
   userActions: [ [+]
   ]
   userExperienceScore: TOLERATED
   userId: MCCANTS,MICHAEL
   userSessionId: RKTHQWBCUQHKDTTVLPOPQURUKUFAPCCB-0
   userType: REAL_USER
}

Show as raw text

[johnhuang]

Could you expand the userAction field?

[sabari80]

userActions: [ [-]
     { [-]
       apdexCategory: SATISFIED
       application: *****
       cdnBusyTime: 1432
       cdnResources: 0
       cumulativeLayoutShift: null
       customErrorCount: 0
       dateProperties: [ [+]
       ]
       documentInteractiveTime: 966
       domCompleteTime: 1158
       domContentLoadedTime: 1158
       domain: ******
       doubleProperties: [ [+]
       ]
       duration: 3046
       endTime: 1667916591573
       firstInputDelay: 45
       firstPartyBusyTime: 1923
       firstPartyResources: 28
       frontendTime: null
       internalApplicationId: APPLICATION-4B82F442A55D1200
       internalKeyUserActionId: APPLICATION_METHOD-DD251367105EAA43
       javascriptErrorCount: 0
       keyUserAction: true
       largestContentfulPaint: null
       loadEventEnd: 1159
       loadEventStart: 1159
       longProperties: [ [+]
       ]
       matchingConversionGoals: [ [+]
       ]
       name: loadingofpage/customer/
       navigationStart: 1667916588527
       networkTime: null
       requestErrorCount: 0
       requestStart: null
       responseEnd: 582
       responseStart: null
       serverTime: null
       speedIndex: 1102
       startTime: 1667916588527
       stringProperties: [ [+]
       ]
       targetUrl: *****
       thirdPartyBusyTime: 1432
       thirdPartyResources: 31
       totalBlockingTime: null
       type: Load
       userActionPropertyCount: 0
       visuallyCompleteTime: 2365
     }
     { [+]
     }
     { [+]
     }

[bowesmana]

Remove every single possible field you won't need before you mvexpand anything. Looks like you're only using user_actions field, so

| fields user_actions

before you mvexpand

You could probably also filter out the names you are looking for before you expand, so only searching for events that have *newintakeprocess.aspx* (although that's not an efficient search with leading wildcards). You could include this as a search parameter on the first line against _raw or after the spath against user_actions

[sabari80]

if i am adding anything in the first search line, the output records got reduced. I am not getting the entire result set 

index="dynatrace" sourcetype="dynatrace:usersession" "userActions{}.name"="*newintakeprocess.aspx*"
| spath output=user_actions path="userActions{}"
| mvexpand user_actions
| spath output=pp_user_action_name input=user_actions path=name
| spath output=pp_user_action_response input=user_actions path=visuallyCompleteTime
| where pp_user_action_name like "%newintakeprocess.aspx%"
| eval pp_user_action_name=substr(pp_user_action_name,0,40)
| stats count(pp_user_action_response) As "Total_Calls" ,avg(pp_user_action_response) AS "User_Action_Response" by pp_user_action_name
| eval User_Action_Response=round(User_Action_Response,0)
| sort -Total_Calls

-- Retrieved 806 records for today -- no mvexpand error though 

index="dynatrace" sourcetype="dynatrace:usersession"
| spath output=user_actions path="userActions{}"
| mvexpand user_actions
| spath output=pp_user_action_name input=user_actions path=name
| spath output=pp_user_action_response input=user_actions path=visuallyCompleteTime
| where pp_user_action_name like "%newintakeprocess.aspx%"
| eval pp_user_action_name=substr(pp_user_action_name,0,40)
| stats count(pp_user_action_response) As "Total_Calls" ,avg(pp_user_action_response) AS "User_Action_Response" by pp_user_action_name
| eval User_Action_Response=round(User_Action_Response,0)
| sort -Total_Calls

-- 8661 events for today with mvexpand error 

[bowesmana]

and with fields statement?

[sabari80]

with fields statement also getting same error, but result set got increased little bit 

index="dynatrace" sourcetype="dynatrace:usersession"
| spath output=user_actions path="userActions{}"
| fields user_actions
| mvexpand user_actions
| spath output=pp_user_action_name input=user_actions path=name
| spath output=pp_user_action_response input=user_actions path=visuallyCompleteTime
| where pp_user_action_name like "%newintakeprocess.aspx%"
| eval pp_user_action_name=substr(pp_user_action_name,0,40)
| stats count(pp_user_action_response) As "Total_Calls" ,avg(pp_user_action_response) AS "User_Action_Response" by pp_user_action_name
| eval User_Action_Response=round(User_Action_Response,0)
| sort -Total_Calls

-- 9568 events for today -- with mvexpand error 

[bowesmana]

after the spath, add the search

| search user_actions{}.name="*newintakeprocess.aspx*"

However, how many events would you expect to get - is the 800 in the right range or 8000? Your 8000 count is ALL the events without the filter, so will include events where the name is not newinstakeprocess.aspx

If you search in a smaller window, the search filter would restrict the count so you should get a smaller number anyway. Validate that first so you are not finding the memory error 

[sabari80]

No records retrieved after adding search command 

index="dynatrace" sourcetype="dynatrace:usersession"
| spath output=user_actions path="userActions{}"
| search user_actions{}.name="*newintakeprocess.aspx*"
| fields user_actions
| mvexpand user_actions
| spath output=pp_user_action_name input=user_actions path=name
| spath output=pp_user_action_response input=user_actions path=visuallyCompleteTime
| where pp_user_action_name like "%newintakeprocess.aspx%"
| eval pp_user_action_name=substr(pp_user_action_name,0,40)
| stats count(pp_user_action_response) As "Total_Calls" ,avg(pp_user_action_response) AS "User_Action_Response" by pp_user_action_name
| eval User_Action_Response=round(User_Action_Response,0)
| sort -Total_Calls

8000+ records are the correct result set.