ETL Running
Scenario 1: A Single ETL Update Task Has Long CPU Usage Duration
Cause
Check the metric Top 20 ETLs by CPU Usage Duration in the Last 30 Days (CPU usage duration >= 10s). If the CPU usage duration of a single ETL exceeds 60 minutes, the ETL run is occupying CPU for too long. Possible causes include:
(1) The ETL update task is blocked and cannot run normally
(2) The ETL update task has complex run logic, such as many output datasets, causing a long update time (>60 minutes)
(3) While the ETL update task is running, many other tasks occupy resources in parallel, causing long queue time or affecting the ETL task run
Troubleshooting Approach
We recommend troubleshooting step by step as follows:
(1) Check whether the run duration of this type of ETL update task is abnormal.
Click the ETL name to jump to it, view ETL run records, and check whether historical runs took a similar amount of time.
?Optimization Measures
If the job duration is unexpected, query logs to investigate the specific reason for the long duration. You can configure the maximum task run duration in Admin Settings > Operations Management > Parameter Configuration to limit resource occupation by abnormal tasks.
(2) Check whether the update time of this type of ETL can be optimized.
?Optimization Measures
When there are multiple output dataset nodes, we recommend:
a. If many nodes are shared in the ETL, process the shared part in a separate ETL and use that ETL output dataset as the input dataset of the original ETL to avoid repeated calculation of the same data.
b. If few nodes are shared in the ETL, split different processing steps into independent ETLs so multiple ETLs can update in parallel and improve calculation efficiency.
(3) Check whether the run time of this type of ETL update task can be optimized.
?Optimization Measures
a. Determine whether these tasks have upstream dependencies. If business is not affected, move tasks without upstream dependencies to off-peak periods.
b. Refer to Node CPU Usage Trend by Time Period and Server CPU Load (System Load) Trend to understand CPU usage peaks and CPU load peaks. Avoid running these tasks during those periods.
Scenario 2: Many Unexpected ETL Jobs
Cause
Check the metric Yesterday ETL Run Time Distribution. If the actual number of ETL runs is more than twice the planned number, there were many unexpected ETL jobs yesterday. Possible causes include:
(1) Many manually triggered ETL run tasks occurred during the problematic period
(2) Task backlog in earlier periods delayed scheduled tasks from previous periods into this period
(3) Cascading update is configured for ETL. Completion of one ETL or dataset update triggers updates of multiple ETLs, worsening ETL update task backlog after cascading updates
Troubleshooting Approach
We recommend troubleshooting step by step as follows:
(1) Check whether many manually triggered ETL run tasks occurred during the problematic period.
In Yesterday ETL Run Time Distribution, find bars with warning signs. Click to drill down and check whether the User column indicates automatic update.
(2) Check whether many cascading ETL update tasks occurred during the problematic period.
In Yesterday ETL Run Time Distribution, find bars with warning signs. Click to drill down, select the operation object to jump to it, and view resource lineage. In resource lineage, trace whether the ETL run was triggered by an upstream task.
(3) Check whether large tasks blocked the problematic period and delayed scheduled tasks.
In Yesterday ETL Run Time Distribution, find the bars before the warning signs. Click to drill down and sort by run duration.
?Optimization Measures
If there are tasks with run duration over 60 minutes, adjust them according to the handling method provided in A Single ETL Update Task Has Long CPU Usage Duration.
(4) Check whether ETL task queue time during the problematic period is long (queue time > 30 minutes)
In Yesterday ETL Run Time Distribution, find the bars before the warning signs. Click to drill down and observe task queue time.
?Optimization Measures
If queue time is long (>30 minutes), observe memory usage during that period. If it has not reached the 95% warning line, consult Guandata to confirm whether the ETL scheduling task concurrency can be increased in Admin Settings > Operations Management > Parameter Configuration. If the issue remains after increasing concurrency, consider expansion. Contact Guandata to evaluate the specific expansion plan.
Additional Recommendations
Refer to Node CPU Usage Trend by Time Period and Server CPU Load (System Load) Trend to understand CPU usage peaks and CPU load peaks. Avoid running manually triggered ETLs, complex ETLs, or tasks with many downstream cascades during those periods.