Example: Amount of Consumed Memory

This example determines the amount of consumed memory.

To find out how much memory each executor is consuming during the query process:

1. Upload your profile_attempt_0.json or you can use the sample profile_attempt_0.json file. See Uploading Profile Data for upload instructions.
2. Create a file called operator_type.json and add the sample field values in Example: Operator Type Mapping to the file.
3. Upload the operator type JSON file.
4. Using a SQL query, obtain the amount of memory consumed. Group by and order by nested_2.endpoint.address.

The following is a sample SQL query snippet for obtaining the amount of memory each executor is consuming:

SELECT nested_2.endpoint.address AS address, sum(nested_2.minorFragmentProfile.maxMemoryUsed)/1024/1024 AS maxMemoryUsed_MB
-- nested_2.minorFragmentProfile.memoryUsed AS memoryUsed, nested_2.minorFragmentProfile.maxMemoryUsed AS maxMemoryUsed, nested_2.endpoint.address AS address, nested_2.endpoint.maxDirectMemory AS maxDirectMemory, nested_2.endpoint.provisionId AS provisionId, majorFragmentId
FROM (
 SELECT fragmentProfile, minorFragmentProfile, nested_1.minorFragmentProfile.endpoint AS endpoint, majorFragmentId
 FROM (
   SELECT fragmentProfile, flatten(nested_0.fragmentProfile.minorFragmentProfile) AS minorFragmentProfile, nested_0.fragmentProfile.majorFragmentId AS majorFragmentId
   FROM (
     SELECT flatten(fragmentProfile) AS fragmentProfile
     FROM "@dremio"."profile_attempt_0" AS "profile_attempt_0"
   ) nested_0
 ) nested_1
) nested_2
group by nested_2.endpoint.address
order by nested_2.endpoint.address

Alternative SQL code:

• Substitute sum(nested_2.minorFragmentProfile.memoryUsed AS memoryUsed) for sum(nested_2.minorFragmentProfile.maxMemoryUsed)/1024/1024 AS maxMemoryUsed_MB.
• Substitute nested_2.endpoint.provisionId instead of nested_2.endpoint.address to obtain the yarn container name instead of the executor name.