Hologres:Monitor and analyze serverless computing resource usage

Required permissions

Querying hologres.hg_serverless_computing_query_log requires the following permissions:

• Query data scans of all databases in an instance

  • You have the superuser role:

    -- A superuser can execute this command. Replace "Account ID" with the actual Alibaba Cloud account ID. For a RAM user, prefix the ID with "p4_".
    ALTER USER "Account ID" SUPERUSER;

  • Alternatively, you are a regular user added to the pg_read_all_stats user group:

    Note

    To view all slow query logs as a regular user, ask an instance superuser to add you to this group.

    GRANT pg_read_all_stats TO "Alibaba Cloud account ID";-- Standard PostgreSQL authorization model
    CALL spm_grant('pg_read_all_stats', 'Alibaba Cloud account ID');  -- Simple permission model (SPM)
    CALL slpm_grant('pg_read_all_stats', 'Alibaba Cloud account ID'); -- Schema-level permission model (SLPM)

• View data scans of the current database

  • Enable the simple permission model (SPM) or the schema-level permission model (SLPM) for the database and add the user to the db_admin user group:

    CALL spm_grant('<db_name>_admin', 'Alibaba Cloud account ID');  -- SPM
    CALL slpm_grant('<db_name>.admin', 'Alibaba Cloud account ID'); -- SLPM

  • Regular users can query the data scan volume for their own queries in the current database.

Query the resource usage of a single SQL statement

Example:

SELECT
    *,
    queue_time_ms,	-- The duration of the SQL statement queueing for serverless computing resources, in milliseconds (ms).
    serverless_allocated_cores,	-- The number of serverless CUs allocated to the SQL statement.
    serverless_resource_used_time_ms, -- The time that serverless computing resources were in use by the SQL statement, in ms.
    (serverless_allocated_cores::DECIMAL(38, 4)) * (serverless_resource_used_time_ms::DECIMAL(38, 4)) AS serverless_cums
FROM
    hologres.hg_serverless_computing_query_log;

Query the resource usage within a time period

Example:

SELECT
    (SUM((serverless_allocated_cores::DECIMAL(38, 4)) * (serverless_resource_used_time_ms::DECIMAL(38, 4))) / 1000 / 60 / 60)::bigint AS serverless_cuh
FROM
    hologres.hg_serverless_computing_query_log
WHERE
    status = 'SUCCESS'
    AND serverless_allocated_cores IS NOT NULL
    AND serverless_resource_used_time_ms IS NOT NULL
    AND query_end BETWEEN '2024-05-01 00:00:00' AND '2024-05-31 24:00:00';-- Specify the time period.

Set daily quota

Hologres V3.1.5+ allows a superuser to limit the daily usage of serverless computing resources using the following command:

-- Set a resource quota for a DB.
ALTER DATABASE <db_name> SET hg_serverless_computing_daily_max_cuh_usage_threshold = xx;

-- Set a resource quota for a user.
ALTER USER <user_name> SET hg_serverless_computing_daily_max_cuh_usage_threshold = xx;

• Notes

  • The default value of hg_serverless_computing_daily_max_cuh_usage_threshold is -1, which means no limit is enforced.

  • Unit: CU-hour.

  • Hologres updates the total serverless computing resource usage for an instance every 10 minutes. Consequently, enforcement of limits may experience a delay of up to 10 minutes.

  • Before executing an SQL statement, Hologres compares the serverless computing resources used that day with the daily resource quota for the current user or database. If the limit is reached, the SQL statement falls back to instance reserved resources.

  • Hologres calculates serverless computing resource consumption based on the calendar day in the database's default time zone. You can query the database's default time zone by running the following statements within the same session:

    -- Reset the time zone to avoid effects from the client time zone or the user's default time zone.
    RESET timezone;
    
    -- Query the default time zone of the current DB.
    SHOW timezone;

• Suggestions

  • For long-term users: To determine a suitable daily quota for serverless computing resources, analyze your resource usage over a specific time period.

  • For new users: We recommend using the feature extensively in a stable production environment first. After observing your usage patterns, you can then decide whether to set a daily cumulative usage limit.

To prevent SQL statements from falling back to instance reserved resources when the Serverless Computing usage limit is reached, run the following commands as a superuser. This configuration will cause queries to fail and return an error.

-- Set at the DB level.
ALTER DATABASE <db_name> SET hg_serverless_computing_enable_fallback_when_exceed_cuh_threshold = false;

-- Set at the user level.
ALTER USER <user_name> SET hg_serverless_computing_enable_fallback_when_exceed_cuh_threshold = false;

Analyze costs

Monitor and analyze the costs of Serverless Computing resources in the Expenses and Costs console.

1. Log on to Expenses and Costs. In the left navigation pane, click Cost > Cost Analysis.

2. On the Cost Analysis page, in the Conditions section on the right, set Category to Billing Item and set Cost Type and Time Unit.

3. In the Filters section, set the filter items as needed.

For more information, see Cost Analysis.

Set cost alerts

In the Expenses and Costs console, you can manage budgets and set alerts for pay-as-you-go billing items. For more information, see Cost alerts.

Create alerts for long queries

Hologres supports monitoring Serverless Computing metrics. We recommend that you create alert rules for Serverless Computing to prevent unexpected costs.

For example, for the Serverless Computing Longest Active Query Time metric, a recommended alert rule is as follows:

Warn: The value of the "Serverless Computing / Serverless Computing Longest Active Query Time" metric is >= 3,600,000 milliseconds for 5 Consecutive Cycles (1 Cycle = 1 Minute).

For more information, see Configure alert rules.