How to troubleshoot unexpected pay-as-you-go fee increases - MaxCompute

You incur fees when you use MaxCompute. If your daily spending is typically stable but suddenly increases, and this is not due to normal business growth, you can perform a cost analysis to identify the projects and jobs that caused the spike. You can then promptly optimize and adjust jobs to control your costs. This article describes how to troubleshoot an unexpected increase in MaxCompute pay-as-you-go fees by using bill details and the MaxCompute metadata service (Information Schema).

Background

To troubleshoot an unexpected increase in MaxCompute pay-as-you-go fees, follow these steps:

In the Expenses and Costs console, identify the date of the unexpected cost increase. Then, determine which project and billable item incurred the high fees on that day.
Analyze the specific reason for the cost increase:
- If compute fees are higher than expected, you can use the TASKS_HISTORY view in Information Schema to analyze the job volume and identify the top-cost jobs.
- If the storage fee is higher than expected, you can download a usage record to analyze changes in storage costs.
- If fees for outbound traffic over the public network are higher than expected, you can use the TUNNELS_HISTORY view in Information Schema to track changes in download fees.

Step 1: Obtain the Information Schema

Starting March 1, 2024, MaxCompute no longer automatically installs the project-level Information Schema for new projects. If you need to query metadata, you can use the tenant-level Information Schema to obtain more comprehensive information. For details on using the tenant-level Information Schema, see Tenant-level Information Schema.

For existing MaxCompute projects, do the following before you use the Information Schema service:

As a project owner or a RAM user with the Super_Administrator management role, you must install the Information Schema permission package to gain access to project metadata. For more information about how to grant roles to users, see Grant roles to users.
Use one of the following installation methods. For more information about the features and limitations of Information Schema, see Information Schema overview.
- Method 1: Log on to the MaxCompute client and run the following command:
```
install package Information_Schema.systables;
```
- Method 2: Log on to the DataWorks console and go to the ad hoc query page. For more information about how to run ad hoc queries, see Run SQL statements in an ad hoc query (optional). Then, run the following command:
```
install package Information_Schema.systables;
```
If you need to analyze metadata from multiple MaxCompute projects, you must install the Information Schema permission package for each project separately. Then, insert the metadata backups from each project into a single table for centralized analysis.
We recommend using the tenant-level Information Schema. It allows you to query metadata and usage history for all projects that the current user can access.

(Optional) Step 2: Grant permissions to users other than the project owner

The Information Schema views contain all user data at the project level. By default, only the project owner can access this data. If other users or roles in the project need to access it, you must grant them permissions. For more information, see Access resources across projects based on packages.

Syntax for granting permissions:

grant <actions> on package Information_Schema.systables to user <user_name>;
grant <actions> on package Information_Schema.systables to role <role_name>;

actions: The operation permission to be granted. The value is Read.
user_name: An Alibaba Cloud account or RAM user that has been added to the project.

You can execute the list users; command in the MaxCompute client to obtain user accounts.
role_name: A role that has been added to the project.

You can execute the list roles; command in the MaxCompute client to obtain the role name.

Example:

grant read on package Information_Schema.systables to user RAM$Bob@aliyun.com:user01;

(Optional) Step 3: Download and back up metadata

MaxCompute retains the history of completed jobs in a project for the last 14 days. If you frequently need to query data older than 14 days, we recommend setting up a scheduled backup of your metadata to a project table. You can skip this step if you only need to run ad hoc queries on job history within the last 14 days.

Log on to the MaxCompute client and run the following command to create metadata backup tables.

Command to create metadata backup tables

-- <project_name> is the name of your MaxCompute project.
-- Create the tasks_history backup table.
CREATE TABLE IF NOT EXISTS <project_name>.tasks_history
(
    task_catalog STRING
    ,task_schema STRING
    ,task_name STRING
    ,task_type STRING
    ,inst_id STRING
    ,status STRING
    ,owner_id STRING
    ,owner_name STRING
    ,result STRING
    ,start_time DATETIME
    ,end_time DATETIME
    ,input_records BIGINT
    ,output_records BIGINT
    ,input_bytes BIGINT
    ,output_bytes BIGINT
    ,input_tables STRING
    ,output_tables STRING
    ,operation_text STRING
    ,signature STRING
    ,complexity DOUBLE
    ,cost_cpu DOUBLE
    ,cost_mem DOUBLE
    ,settings STRING
    ,ds STRING
);
-- Create the tunnels_history backup table.
CREATE TABLE IF NOT EXISTS <project_name>.tunnels_history
(
tunnel_catalog  STRING
,tunnel_schema   STRING
,session_id      STRING
,operate_type    STRING
,tunnel_type     STRING
,request_id      STRING
,object_name     STRING
,partition_spec  STRING
,data_size       BIGINT
,block_id        BIGINT
,offset          BIGINT
,length          BIGINT
,owner_id        STRING 
,owner_name      STRING 
,start_time      DATETIME
,end_time        DATETIME 
,client_ip       STRING 
,user_agent      STRING 
,object_type     STRING 
,columns         STRING 
,ds  STRING
);

In the DataWorks console, go to the DataStudio page, create an ODPS SQL node named history_backup, and configure a schedule to periodically write data to the tasks_history and tunnels_history backup tables. After you complete the configuration, click the icon in the upper-left corner to save.

The following sample commands are run in the ODPS SQL node:

-- <project_name> is the name of your MaxCompute project.
USE <project_name>; 
-- Back up tasks_history.
INSERT INTO TABLE <project_name>.tasks_history 
SELECT task_catalog,task_schema ,task_name,task_type STRING,inst_id,`status`,owner_id,owner_name,result
  ,start_time,end_time,input_records,output_records,input_bytes,output_bytes
  ,input_tables,output_tables,operation_text,signature,complexity,cost_cpu,cost_mem,settings,ds 
  FROM information_schema.tasks_history WHERE ds ='${datetime1}';
-- Back up tunnels_history.
INSERT INTO TABLE <project_name>.tunnels_history 
SELECT tunnel_catalog,tunnel_schema,session_id,operate_type,tunnel_type,request_id,object_name
  ,partition_spec,data_size,block_id,offset,length,owner_id,owner_name,start_time,end_time
  ,client_ip,user_agent,object_type,columns,ds 
  FROM information_schema.tunnels_history WHERE ds ='${datetime1}';

The ${datetime1} variable is a scheduling parameter in DataWorks. On the right side of the ODPS SQL node, click the Properties tab. In the Basic Properties section, set the Parameters to datetime1=${yyyymmdd}.

Note

To analyze metadata from multiple MaxCompute projects, create multiple ODPS SQL nodes and write the metadata from each project to the same backup table.

Step 4: Analyze high-cost projects and items

Log on to the Expenses and Costs console. Use the following methods to analyze the projects and billable items causing high costs. For more details, see View bill details.

In the left-side navigation pane, choose Billing > bill details. Set Statistic Item to Instance and Statistical Period to Day.
- Method 1: Identify the project (instance ID) that incurred the high fees. If a project's cost is significantly higher than expected, focus your analysis on that project.
- Method 2: Identify the billable item that incurred the high fees. You can search for a specific project by using Resource Instance Name/ID and analyze its high-cost billable items.
In the left-side navigation pane, choose Cost Analysis. The Cost Analysis page provides a visual way to identify billable items with rising costs.

Step 5: Investigate the root cause

Analyze the high-cost projects and billable items to investigate the reason for the cost increase.

High SQL compute fees

A high overall cost for SQL jobs, including jobs on external tables, might be caused by a single high-cost job, repeated job executions, or misconfigured scheduling properties.

Query the instance ID (inst_id) of the high-cost job to view its execution details.
1. Log on to the MaxCompute client , use the use command to switch to the project with high consumption that you identified in Step 4, and query job consumption details from TASKS_HISTORY. A command example is as follows.
```
-- Enable the MaxCompute V2.0 data type edition. For more information, see MaxCompute V2.0 data types.
SET odps.sql.decimal.odps2=true;
SELECT  
inst_id --- Instance ID
,input_bytes --- Data input size
,complexity
,CAST(input_bytes/1024/1024/1024 * complexity * 0.3 AS DECIMAL(18,5) ) cost_sum
,GET_JSON_OBJECT(settings, "$.SKYNET_ID") SKYNET_ID --- DataWorks scheduling task ID
FROM    information_schema.tasks_history 
-- If you are querying metadata older than 14 days, query the backup table created in Step 3, which is named <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
;
```
  Note
  - The compute fee for a single SQL job is calculated as: Input Data Size × SQL Complexity × Unit Price (USD 0.0438 per GB).
  - In the example, task_type = 'SQL' indicates a standard SQL job, and task_type = 'SQLRT' indicates an SQL query acceleration job.
2. View the SKYNET_ID (DataWorks scheduling task ID) of high-consumption SQL jobs.
  - If an ID exists, check the node execution details in DataWorks.
  - If no ID is available, this indicates that this task was not initiated by a DataWorks scheduling node. You can view the specific execution information based on the inst_id, as shown in the following command example.
```
SELECT operation_text 
  FROM information_schema.tasks_history 
 WHERE ds='<date_partition_of_job_execution>' AND inst_id='<instance_id>';
```

Find frequently repeating jobs.

Log on to the MaxCompute client, use the use command to switch to the high-cost project identified in Step 4, and query for repeatedly running jobs by using TASKS_HISTORY. The sample command is as follows.

-- Analyze the job growth trend.
SELECT  signature
        ,ds
        ,COUNT(*) AS tasknum
FROM    information_schema.tasks_history 
--If you are querying metadata older than 14 days, query the backup table created in Step 3, which is named <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, view the recent execution history of the corresponding SQL job.
SELECT  *
FROM    information_schema.tasks_history 
--If you are querying metadata older than 14 days, query the backup table created in Step 3, which is named <project_name>.tasks_history.
where   signature = 'abnormal_signature'
AND ds >= 'date_partition_to_query'
;

High Spark compute fees

Because Spark jobs can have high overall consumption, you can query the inst_id of jobs with abnormal consumption to view detailed execution information.

Log on to the MaxCompute client , use the use command to switch to the project with high consumption that you identified in Step 4, and query the job consumption details by using TASKS_HISTORY. The command example is as follows.

-- Enable the MaxCompute V2.0 data type edition. For more information, see MaxCompute V2.0 data types.
SET odps.sql.decimal.odps2=true;
SELECT  inst_id -- Instance ID
,cost_cpu -- CPU consumption of the job (100 indicates 1 core-second. For example, if 10 cores run for 5s, cost_cpu is 10 * 100 * 5 = 5000).
,CAST(cost_cpu/100/3600 * 0.36 AS DECIMAL(18,5) ) cost_sum
FROM  information_schema.tasks_history 
-- If you are querying metadata older than 14 days, query the backup table created in Step 3, which is named <project_name>.tasks_history.
WHERE   task_type = 'CUPID' AND status='Terminated'
AND ds = 'date_partition_to_query'
ORDER BY cost_sum DESC
LIMIT 10000
;

Note

The daily compute fee for a Spark job is calculated as: Total Compute Hours per Day × Unit Price (USD 0.1041 per hour per task).
task_type = 'CUPID' indicates a Spark job.

The following example shows the command to view detailed execution information for a specific inst_id.

SELECT operation_text 
  FROM information_schema.tasks_history 
WHERE ds='date_partition_of_job_execution' AND inst_id='<instance_id>';

High MapReduce job costs

Because MapReduce jobs can have high overall consumption, you can query the inst_id of a job with abnormal consumption to view its detailed execution information.

Log on to the MaxCompute client . Use the use command to switch to the high-consumption project that you identified in Step 4 and query job consumption by using TASKS_HISTORY. The following is a sample command.

-- Enable the MaxCompute V2.0 data type edition. For more information, see MaxCompute V2.0 data types.
SET odps.sql.decimal.odps2=true;
SELECT  inst_id -- Instance ID
,cost_cpu -- CPU consumption of the job (100 indicates 1 core-second. For example, if 10 cores run for 5s, cost_cpu is 10 * 100 * 5 = 5000).
,CAST(cost_cpu/100/3600 * 0.36 AS DECIMAL(18,5) ) cost_sum
FROM    information_schema.tasks_history 
-- If you are querying metadata older than 14 days, query the backup table created in Step 3, which is named <project_name>.tasks_history.
WHERE   task_type = 'LOT' AND status='Terminated'
AND ds = 'date_partition_to_query'
ORDER BY cost_sum DESC
LIMIT 10000
;

The following example shows the command to view detailed execution information for a specific inst_id.

SELECT operation_text 
  FROM information_schema.tasks_history 
WHERE ds='date_partition_of_job_execution' AND inst_id='<instance_id>';

High storage fees

Use the usage records page to query storage fees.

Download the usage record. For more information, see Download usage records.
Upload the usage record. For more information, see Upload usage record data to MaxCompute.
Analyze the data by using SQL. For more information, see Analyze MaxCompute bill usage details.

High outbound traffic fees

If the overall cost for outbound traffic over the public network is high, follow the steps below to analyze which download category is causing high fees and when.

Log on to the MaxCompute client , use the use command to switch to the project with high costs identified in Step 4, and query the download costs by using TUNNELS_HISTORY. An example command is as follows.

-- Enable the MaxCompute V2.0 data type edition. For more information, see MaxCompute V2.0 data types.
set odps.sql.decimal.odps2=true;
SELECT  ds
        ,operate_type
        ,SUM(CAST(data_size / 1024 / 1024 / 1024 * 0.8 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
;

Note

The fee for a single download is calculated as: Download Data Size × Unit Price (USD 0.1166 per GB).

You can analyze the trend of download costs over a specific period based on the execution results. You can also use the tunnel show history command to view detailed historical information. For more information, see Tunnel command.
```
odps@ sz_mc>tunnel show history;
20xxx                success 'download inttable inttable.txt'
20xxx                success 'upload /Users/xxx.csv maxcomputefee -c "UTF-8" -h "true" -dfp "yyyy-MM-dd HH:mm:ss"'
```

More information

For more articles about cost optimization, see Cost optimization overview.