MaxCompute:Troubleshoot sudden spikes in MaxCompute pay-as-you-go costs

SQL computing costs

SQL job costs are determined by: input data volume x SQL complexity x USD 0.0438 per GB.USD 0.1166 per GBUSD 0.0690 per hour per taskUSD 0.0438 per GB

A sudden increase is typically caused by a single abnormal job, a scheduling misconfiguration, or duplicate jobs running repeatedly.

Which SQL jobs consumed the most in a given day?

Log on to the MaxCompute client and switch to the high-cost project using the use command. Query TASKS_HISTORY to rank jobs by estimated cost:

-- Enable the ODPS 2.0 data type.
SET odps.sql.decimal.odps2=true;
SELECT
    inst_id                                                                   -- instance ID
    ,input_bytes                                                              -- input data volume
    ,complexity
    ,CAST(input_bytes/1024/1024/1024 * complexity * 0.0438 AS DECIMAL(18,5)) cost_sum
    ,GET_JSON_OBJECT(settings, "$.SKYNET_ID") SKYNET_ID                      -- DataWorks scheduling job ID
FROM information_schema.tasks_history
-- For data older than 14 days, query the backup table: <project_name>.tasks_history
WHERE (task_type = 'SQL' OR task_type = 'SQLRT')
AND ds = '<date partition to query>'
ORDER BY cost_sum DESC
LIMIT 10000;

SQL computing cost = Input data volume × SQL complexity × Unit price (USD 0.0438 per GB). task_type = 'SQL' — SQL job. task_type = 'SQLRT' — SQL query acceleration job.

Who triggered the expensive jobs?

Check the SKYNET_ID field in the results:

• If a SKYNET_ID is present, the job was triggered by a DataWorks scheduling node. Check that node's execution history in DataWorks.

• If SKYNET_ID is empty, the job was triggered outside DataWorks. Use the inst_id to retrieve the full SQL text:

  SELECT operation_text
    FROM information_schema.tasks_history
   WHERE ds = '<date partition of job execution>' AND inst_id = '<inst_id>';

Which SQL jobs ran repeatedly?

Use the signature field (a hash of the SQL text) to detect repeated execution of the same query:

-- Identify job growth trends by signature.
SELECT  signature
        ,ds
        ,COUNT(*) AS tasknum
FROM    information_schema.tasks_history
-- For data older than 14 days, query the backup table: <project_name>.tasks_history
WHERE (task_type = 'SQL' OR task_type = 'SQLRT')
AND ds >= '<date partition to query>'
GROUP BY ds
         ,signature
ORDER BY tasknum DESC
LIMIT   10000;

-- After identifying an abnormal signature, check its recent execution history.
SELECT  *
FROM    information_schema.tasks_history
-- For data older than 14 days, query the backup table: <project_name>.tasks_history
WHERE   signature = '<abnormal signature>'
AND ds >= '<date partition to query>';

Spark computing costs

Spark job costs are determined by: total billable CPU hours x USD 0.1041 per hour per task.USD 0.1041 per hour per task

Which Spark jobs consumed the most?

Log on to the MaxCompute client and switch to the high-cost project. Query TASKS_HISTORY to rank Spark jobs by cost:

-- Enable the ODPS 2.0 data type.
SET odps.sql.decimal.odps2=true;
SELECT
    inst_id                                                           -- instance ID
    ,cost_cpu                                                         -- CPU consumption (100 = 1 core x second)
    ,CAST(cost_cpu/100/3600 * 0.36 AS DECIMAL(18,5)) cost_sum
FROM information_schema.tasks_history
-- For data older than 14 days, query the backup table: <project_name>.tasks_history
WHERE task_type = 'CUPID' AND status = 'Terminated'
AND ds = '<date partition to query>'
ORDER BY cost_sum DESC
LIMIT 10000;

Spark job computing cost for the day = Total billable hours × Unit price (USD 0.1041 per hour per task). cost_cpu unit: 100 = 1 core x second. Example: 10 cores running for 5 seconds gives cost_cpu = 10 x 100 x 5 = 5000.

What did an abnormal Spark job execute?

SELECT operation_text
  FROM information_schema.tasks_history
 WHERE ds = '<date partition of job execution>' AND inst_id = '<inst_id>';

MapReduce computing costs

MapReduce job costs follow the same CPU-hour formula as Spark.

Which MapReduce jobs consumed the most?

Log on to the MaxCompute client and switch to the high-cost project. Query TASKS_HISTORY:

-- Enable the ODPS 2.0 data type.
SET odps.sql.decimal.odps2=true;
SELECT
    inst_id                                                           -- instance ID
    ,cost_cpu                                                         -- CPU consumption (100 = 1 core x second)
    ,CAST(cost_cpu/100/3600 * 0.36 AS DECIMAL(18,5)) cost_sum
FROM information_schema.tasks_history
-- For data older than 14 days, query the backup table: <project_name>.tasks_history
WHERE task_type = 'LOT' AND status = 'Terminated'
AND ds = '<date partition to query>'
ORDER BY cost_sum DESC
LIMIT 10000;

What did an abnormal MapReduce job execute?

SELECT operation_text
  FROM information_schema.tasks_history
 WHERE ds = '<date partition of job execution>' AND inst_id = '<inst_id>';

Storage fees

Use Usage Details to investigate storage fee changes:

1. Download usage details — see Download usage details.

2. Upload usage details to MaxCompute — see Upload usage details data to MaxCompute.

3. Analyze with SQL — see Analyze MaxCompute billing usage details.

Public network downstream traffic fees

Download fees are charged at USD 0.1166 per GB of downloaded data.

Which time periods and download types had the highest fees?

Log on to the MaxCompute client and switch to the high-cost project. Query TUNNELS_HISTORY:

-- Enable the ODPS 2.0 data type.
SET odps.sql.decimal.odps2=true;
SELECT  ds
        ,operate_type
        ,SUM(CAST(data_size / 1024 / 1024 / 1024 * 0.1166 AS DECIMAL(18,5))) download_fee
FROM    information_schema.tunnels_history
WHERE   operate_type = 'DOWNLOADLOG'
OR      operate_type = 'DOWNLOADINSTANCELOG'
AND     ds >= '<date partition to query>'
GROUP BY ds
         ,operate_type
ORDER BY download_fee DESC;

Download fee = Downloaded data volume × Unit price (USD 0.1166 per GB).

Run the tunnel show history command to view per-request download details. For the full command reference, see Tunnel commands.