Query Memory Analysis
Usually, we first use Query Profile to analyze query memory usage. If the sum of the memory of each operator counted in Query Profile is much smaller than the memory counted by Query Memory Tracker, it means that the operator memory counted by Query Profile is much different from the actual memory used. Then, we often need to use Heap Profile for further analysis. If Query is Canceled due to memory limit overrun and cannot be completed, Query Profile is incomplete and may not be analyzed accurately. Usually, we directly use Heap Profile to analyze query memory usage.
Query Memory View
If you see a large value of Label=query, Type=overview Memory Tracker anywhere, it means that the query memory usage is high.
MemTrackerLimiter Label=query, Type=overview, Limit=-1.00 B(-1 B), Used=83.32 MB(87369024 B), Peak=88.33 MB(92616000 B)
If you already know the query to be analyzed, skip this section and continue with the analysis below. Otherwise, refer to the following method to locate the large memory query.
First locate the QueryID of the large memory query. In the BE web page http://{be_host}:{be_web_server_port}/mem_tracker?type=query, you can see the real-time large memory query by sorting by Current Consumption. You can find the QueryID in label.
When the error process memory exceeds the limit or the available memory is insufficient, the lower part of the Memory Tracker Summary in the be.INFO log contains the Memory Tracker of the top 10 tasks (query/load/compaction, etc.) with the highest memory usage. The format is MemTrackerLimiter Label=Query#Id=xxx, Type=query. Usually, the QueryID of the large memory query can be located in the top 10 tasks.
The memory statistics of historical queries can be viewed in peakMemoryBytes of each query in fe/log/fe.audit.log, or search Deregister query/load memory tracker, queryId in be/log/be.INFO to view the peak memory of each query on a single BE.
Use Query Profile to analyze query memory usage
Find query information including SQL in fe/log/fe.audit.log based on QueryID, get the query plan by explain SQL, and get the query profile by executing SQL after set enable_profile=true. For a detailed introduction to Query Profile, refer to the document Query Profile. Here we only introduce the memory-related content in Query Profile, and locate the Operator and data structure that use a lot of memory based on it.
- Locate Operators or memory data structures that use a lot of memory
Query Profile is divided into two parts:
MergedProfile
MergedProfile is the aggregated result of all Instance Profiles of Query, which shows the sum, avg, max, and min of memory usage of each Operator in each Pipeline of each Fragment on all Instances, including the Operator's peak memory PeakMemoryUsage and the peak memory of major memory data structures such as HashTable and Arena. Based on this, Operators and data structures that use a lot of memory can be located.
MergedProfile
Fragments:
Fragment 0:
Pipeline : 0(instance_num=1):
RESULT_SINK_OPERATOR (id=0):
- MemoryUsage: sum , avg , max , min
EXCHANGE_OPERATOR (id=20):
- MemoryUsage: sum , avg , max , min
- PeakMemoryUsage: sum 1.16 KB, avg 1.16 KB, max 1.16 KB, min 1.16 KB
Fragment 1:
Pipeline : 1(instance_num=12):
AGGREGATION_SINK_OPERATOR (id=18 , nereids_id=1532):
- MemoryUsage: sum , avg , max , min
- HashTable: sum 96.00 B, avg 8.00 B, max 24.00 B, min 0.00
- PeakMemoryUsage: sum 1.58 MB, avg 134.67 KB, max 404.02 KB, min 0.00
- SerializeKeyArena: sum 1.58 MB, avg 134.67 KB, max 404.00 KB, min 0.00
EXCHANGE_OPERATOR (id=17):
- MemoryUsage: sum , avg , max , min
- PeakMemoryUsage: sum 2.25 KB, avg 192.00 B, max 768.00 B, min 0.00
Execution Profile
Execution Profile is the result of each specific Instance Profile of Query. Usually, after locating the Operator and data structure that use a lot of memory based on MergedProfile, you can analyze the reasons for their memory usage based on the query plan after explain SQL. If you need to analyze the memory value of Query in a certain BE node or a certain Instance in some scenarios, you can further locate it based on Execution Profile.
Execution Profile 36ca4f8b97834449-acae910fbee8c670:(ExecTime: 48sec201ms)
Fragments:
Fragment 0:
Fragment Level Profile: (host=TNetworkAddress(hostname:10.16.10.8, port:9013)):(ExecTime: 48sec111ms)
Pipeline :1 (host=TNetworkAddress(hostname:10.16.10.8, port:9013)):
PipelineTask (index=80):(ExecTime: 6sec267ms)
DATA_STREAM_SINK_OPERATOR (id=17,dst_id=17):(ExecTime: 1.634ms)
- MemoryUsage:
- PeakMemoryUsage: 1.50 KB
STREAMING_AGGREGATION_OPERATOR (id=16 , nereids_id=1526):(ExecTime: 41.269ms)
- MemoryUsage:
- HashTable: 168.00 B
- PeakMemoryUsage: 404.16 KB
- SerializeKeyArena: 404.00 KB
HASH_JOIN_OPERATOR (id=15 , nereids_id=1520):(ExecTime: 6sec150ms)
- MemoryUsage:
- PeakMemoryUsage: 3.22 KB
- ProbeKeyArena: 3.22 KB
LOCAL_EXCHANGE_OPERATOR (PASSTHROUGH) (id=-12):(ExecTime: 67.950ms)
- MemoryUsage:
- PeakMemoryUsage: 1.41 MB
HASH_JOIN_SINK_OPERATORtakes up too much memory
HASH_JOIN_SINK_OPERATOR (id=12 , nereids_id=1304):(ExecTime: 1min14sec)
- JoinType: INNER_JOIN
- BroadcastJoin: true
- BuildRows: 600.030257M (600030257)
- InputRows: 600.030256M (600030256)
- MemoryUsage:
- BuildBlocks: 15.65 GB
- BuildKeyArena: 0.00
- HashTable: 6.24 GB
- PeakMemoryUsage: 21.89 GB
It can be seen that the BuildBlocks and HashTable in the Hash Join Build phase mainly use memory. Usually, the Hash Join Build phase uses too much memory. First, confirm whether the Join Reorder order is reasonable. Usually, the correct order is to use small tables for Hash Join Build and large tables for Hash Join Probe. This can minimize the overall memory usage of Hash Join and usually has better performance.
In order to confirm whether the Join Reorder order is reasonable, we find the profile of HASH_JOIN_OPERATOR with id=12. We can see that ProbeRows has only 196240 rows. Therefore, the correct order of this Hash Join Reorder should be to swap the positions of the left and right tables. You can set disable_join_reorder=true to turn off Join Reorder and manually specify the order of the left and right tables and then perform Query verification. For further information, please refer to the relevant documents on Join Reorder in the query optimizer.
HASH_JOIN_OPERATOR (id=12 , nereids_id=1304):(ExecTime: 8sec223ms)
- BlocksProduced: 227
- MemoryUsage:
- PeakMemoryUsage: 0.00
- ProbeKeyArena: 0.00
- ProbeRows: 196.24K (196240)
- RowsProduced: 786.22K (786220)
Use Heap Profile to analyze query memory usage
If the query profile above cannot accurately locate the memory usage location, if the cluster can be easily restarted and the phenomenon can be reproduced, refer to Heap Profile Memory Analysis to analyze the query memory.
Dump the Heap Profile once before the query is executed, and dump the Heap Profile again during the query execution. By using jeprof --dot lib/doris_be --base=heap_dump_file_1 heap_dump_file_2 to compare the memory changes between the two Heap Profiles, you can get the memory usage ratio of each function in the code during the query execution. Compare the code to locate the memory usage location. Because the memory changes in real time during the query execution, you may need to dump the Heap Profile multiple times during the query execution and compare and analyze.
