This topic describes how to analyze profiles to determine resource issues and tune performance. You can analyze either of the following profiles:
A downloaded profile by using SQL. The downloaded profile is the profile_attempt_0.json file. To download the profile and add it to Dremio as a dataset, see Uploading Profile Data.
A raw profile by viewing Job Metrics. A raw profile is a UI-generated profile that is a subset of the data that you can download. You can analyze the Job Metrics based on the views of a raw profile. For more information, go to Viewing a Raw Profile.
Querying Profile Datasets
To perform a simple query on your uploaded profile dataset:
- In the SQL editor, type in
select * from "profile_attempt_0".
- Click the Preview button.
A major consideration is whether a thread is in a running, blocked and waiting for data (or to send data), or sleeping state. A thread is usually sleeping if the thread is ready to run but another thread is currently using the CPU.
It is useful to enable
planner.verbose_profile to obtain more data if there is a planning or substitution issue.
A thread is usually in a blocked state for one of the following reasons:
- It's waiting on some data from another thread (a child phase in the tree).
- It's trying to send data to another thread (a parent phase) but the receiving thread isn't responding.The receiving thread may be too slow or overwhelmed with work. What occurs is that the sending thread is forced to block until the receiver is able to receive the data.
Network activities are tracked as part of the blocked metric.
- If a thread is blocked, nothing much can be done.
- If a thread is sleeping, ensure that
task.on_idle_load_shedis set to true.
The following general areas should be reviewed:
To troubleshoot errors, consider the following:
- Dremio nodes -- Determine whether the coordinator or executor nodes are impacted.
- Verbose error
- Out of memory
- Incorrect results
- No results
To troubleshoot performance, consider the following:
- Planning time versus execution time
- Number of threads which impact process time
- Row count versus rows
- Blocked versus sleep
- Setup time version wait time
- Operator metrics (contact firstname.lastname@example.org)