此页面由 Cloud Translation API 翻译。

槽自动扩缩简介

配置为使用槽自动扩缩的预留会自动扩缩分配的容量，以满足您的工作负载需求。随着工作负载的增加或减少，BigQuery 会动态地将槽调整为适当的级别。具有槽自动扩缩功能的预留仅适用于 BigQuery 版本。

使用自动扩缩预留

您无需在创建自动扩缩预留之前购买槽承诺。槽用量承诺可以为持续使用的槽提供折扣费率，但可选配自动扩缩预留。如需创建自动扩缩预留，请为预留分配槽数上限（预留大小上限）。您可以通过将预留大小上限减去分配给预留的任何可选基准槽来确定自动扩缩槽数上限。

创建自动扩缩预留时，请考虑以下事项：

BigQuery 几乎可以即时扩容预留，直到达到执行作业所需的槽数，或者达到预留可用的槽数上限。槽始终自动扩缩为 50 的倍数。
纵向扩容基于实际用量，并向上取整到最接近的 50 个槽增量。
在纵向扩容时，您的自动扩缩槽将按关联版本的容量计算价格付费。您需要按扩缩的槽数付费，而不是按使用的槽数付费。即使导致 BigQuery 纵向扩容的作业失败，您也需要支付此费用。因此，请勿使用作业信息架构来匹配结算信息。请改为参阅使用信息架构监控自动扩缩。
虽然槽数始终按 50 的倍数进行扩容，但只需一步即可扩容超过 50 个槽。例如，如果您的工作负载需要额外的 450 个槽，BigQuery 可以尝试一次性扩容到 450 个槽来满足容量要求。
当与预留关联的作业不再需要该容量时（最少 1 分钟），BigQuery 会纵向缩容。

任何自动扩缩的容量都会保留至少 60 秒。这 60 秒的时间段称为纵向缩容时段。容量出现任何高峰都会重置纵向缩容时段，并将整个容量级别视为新的授权。但是，如果自上次容量增加以来超过 60 秒或更长时间且需求减少，则系统会在不重置纵向缩容时段的情况下减少容量，从而实现连续减少，而不会强制延迟。

如需了解如何使用自动扩缩功能，请参阅使用槽自动扩缩。

将预留与基准槽和自动扩缩槽搭配使用

除了指定预留大小上限之外，您还可以选择为每个预留指定基准槽数。基准是始终分配给预留的槽数下限，您始终需要为这些槽付费。只有在使用所有基准槽（以及空闲槽 [如果适用]）后，系统才会添加自动扩缩槽。您可以将一项预留中的空闲基准槽与需要容量的其他预留共享。

您可以每隔几分钟增加一次预留中的基准槽。如果您想减少基准槽，并且您最近更改了基准槽容量，而且您的基准槽超过了承诺的槽，则每小时只能减少一次。否则，您可以每隔几分钟减少一次基准槽。

基准槽和自动扩缩槽旨在根据您近期的工作负载提供容量。如果您预计会有与近期工作负载截然不同的大量工作负载，我们建议您在事件发生之前增加基准容量，而不是依赖自动扩缩槽来覆盖工作负载容量。如果您在增加基准容量时遇到问题，请等待 15 分钟后重试该请求。

如果预订没有基准槽或未配置为从其他预留借用空闲槽，则 BigQuery 会尝试进行扩缩。否则，必须先充分利用基准槽位，然后才能进行伸缩。

预留按照以下优先级使用和添加槽：

基准槽。
空闲槽共享（如果已启用）。预留只能共享具有同一版本和同一区域的其他预留的空闲基准槽或承诺槽。
自动扩缩槽。

在以下示例中，槽数从指定的基准数量进行扩缩。etl 和 dashboard 预留的基准大小分别为 700 个和 300 个槽。

无承诺的自动扩缩示例。

在此示例中，etl 预留可以扩容到 1,300 个槽（700 个基准槽加上 600 个自动扩缩槽）。如果 dashboard 预留未使用，则 etl 预留可以使用 dashboard 预留中的 300 个槽（如果没有作业在其中运行），最多可以使用 1600 个槽。

dashboard 预留可以扩容到 1,100 个槽（300 个基准槽加上 800 个自动扩缩槽）。如果 etl 预留完全空闲，则 dashboard 预留最多可扩容到 1800 个槽（300 个基准槽加上 800 个自动扩缩槽，再加上 etl 预留中的 700 个空闲槽）。

如果 etl 预留需要 700 个以上的基准槽（始终可用），则它会尝试使用以下方法按顺序添加槽：

700 个基准槽。
与 dashboard 预留中的 300 个基准槽共享的空闲槽。您的预留仅会与使用相同版本创建的其他预留共享空闲基准槽。
将额外 600 个槽纵向扩容到预留大小上限。

使用槽承诺

以下示例展示了使用容量承诺进行槽自动扩缩。

自动扩缩示例

与预留基准一样，槽承诺可让您分配固定数量的槽以供所有预留使用。与基准槽不同的是，在相应期限内，承诺不能减少。槽承诺是可选的，但如果长期需要基准槽，则可以节省费用。

在此示例中，您需要按容量承诺槽的预定义费率付费。在自动扩缩激活并且预留处于升级状态后，您需要按自动扩缩费率为自动扩缩槽数付费。对于自动扩缩费率，您需要按照扩缩的槽数付费，而不是按使用的槽数付费。

可用槽数上限

如需计算预留可以使用的最大槽数，您可以把基准槽数、最大自动扩缩槽数以及使用相同版本创建但未被基准槽覆盖的承诺中的所有槽数相加。上图中的示例设置如下：

每年 1000 个槽的容量承诺。这些槽会作为 etl 预留和 dashboard 预留中的基准槽进行分配。
分配给 etl 预留的 700 个基准槽。
分配给 dashboard 预留的 300 个基准槽。
为 etl 预留分配 600 个自动扩缩槽。
dashboard 预留的自动扩缩槽数为 800。

对于 etl 预留，可能的槽数上限等于 etl 基准槽 (700) 加上 dashboard 基准槽（如果所有槽都处于空闲状态，则为 300 个）加上自动扩缩槽数上限 (600)。因此，在此示例中，etl 预留可以使用的槽数上限为 1,600。此数量超过容量承诺中的槽数。

在以下示例中，年度承诺超出分配的基准槽数。

计算可用槽数

此示例包含：

每年 1600 个槽的容量承诺。
预留大小上限为 1,500（包括 500 个自动扩缩槽）。
分配给 etl 预留的 1,000 个基准槽。

预留可用的槽数上限等于基准槽 (1,000) 加上任何不专用于基准槽的承诺空闲槽（1600 个年度槽 - 1,000 个基准槽 = 600 个槽），再加上自动扩缩槽 (500)。因此，此预留中的最大潜在槽数为 2100。自动扩缩槽是指超出容量承诺之外的额外槽。

自动扩缩最佳实践

首次使用自动扩缩器时，请根据过去和预期的性能将自动扩缩槽数设置为有意义的数字。创建预留后，请主动监控失败率、性能和账单，并根据需要调整自动扩缩槽的数量。
自动扩缩器在纵向缩容之前至少需要 1 分钟的时间，因此请务必设置自动扩缩槽的数量上限，以便在性能和费用之间取得平衡。如果自动扩缩槽数上限过大，并且您的作业可以使用所有槽数在几秒钟内完成作业，您仍需为整个分钟内的最大槽数支付费用。如果将槽数上限降低到当前数量的一半，则预留会缩减到较小的数字，并且作业可以在该分钟内使用更多 slot_seconds，从而减少浪费。如需有关确定槽要求方面的帮助，请参阅监控作业性能。如需了解确定槽要求的替代方法，请参阅查看版本槽建议。
槽用量有时可能会超过基准槽数和扩缩的槽数之和。您不需要为超过基准槽数和扩缩的槽数的槽用量付费。
自动扩缩器最适合长时间运行的高负荷工作负载，例如具有多个并发查询的工作负载。请避免一次发送一个查询，因为每个查询都会扩缩预留，使其至少保持 1 分钟。如果您持续发送查询，使工作负载保持不变，则设置基准并购买承诺可按折扣价格提供稳定的容量。
BigQuery 自动扩缩功能受容量可用性的限制。BigQuery 会尝试根据历史用量来满足客户容量需求。如需实现容量保证，您可以设置可选的槽基准，即预留中保证的槽数。设置基准后，槽会立即可用。无论您是否使用这些槽，都需要支付其费用。为确保产生非自然的大量需求（例如高流量节假日）时有容量可用，请提前数周与 BigQuery 团队联系。
基准槽始终都会收费。如果容量承诺到期，您可能需要手动调整预留中的基准槽数，以免产生任何不必要的费用。例如，假设您的 1 年期承诺包含 100 个槽，一个预留包含 100 个基准槽。承诺会到期，并且没有续订套餐。承诺使用合约到期后，您需要按随用随付费率支付 100 个基准槽的费用。

监控自动扩缩

使用管理资源图表监控槽用量时，您可能会发现扩缩的槽数远远超过槽用量，这是因为这些图表会平滑处理校准时间段内已用的槽数。如需查看自动扩缩槽用量并获取更精确的详细信息，请缩短时间范围选项。这会自动将对齐周期更新为较小的增量。

在以下示例中，图表显示的扩缩槽数量远远超过工作负载需求。

校准时间段设置为一分钟间隔，扩缩槽数量超过槽用量需求。

不过，如果您缩短时间范围选项，使校准时间段为 2 秒，则可以看到自动扩缩器会根据工作负载需求进行扩缩，并显示更准确的数据。您可以通过拖动时间范围选项的开始时间和结束时间范围来调整时间范围选项。如需显示最准确的工作负载需求数据，请从指标列表中选择 p99。

校准时间段设置为 2 秒间隔，扩缩槽数量适合工作负载需求。

如需最准确地查看自动扩缩用量，请使用 1 到 15 秒之间的校准时间段。如需详细了解管理资源图表的校准时间段，请参阅时间范围选项。

如需了解如何查看槽用量，请参阅查看管理资源图表。

使用信息架构监控自动扩缩

您可以使用以下 SQL 脚本检查特定版本的计费槽秒数。您必须在创建预留所在的项目中运行这些脚本。第一个脚本会显示 commitment_plan 涵盖的已结算的广告位秒数，而第二个脚本会显示未涵盖在承诺中的已结算的广告位秒数。

您只需设置三个变量的值即可运行这些脚本：

start_time
end_time
edition_to_check

这些脚本需注意以下事项：

在数据保留期限结束时，系统会从信息架构视图中移除已删除的预留和容量承诺。指定最近的时间范围，该时间范围不包含已删除的预留和承诺，以获得正确的结果。
由于存在小数舍入误差，脚本的结果可能与账单不完全一致。

以下脚本会检查承诺涵盖的特定版本的槽用量。

展开以查看用于涵盖的场景的脚本

DECLARE start_time,end_time TIMESTAMP;

DECLARE
  edition_to_check STRING;

/* Google uses Pacific Time to calculate the billing period for all customers,
regardless of their time zone. Use the following format if you want to match the
billing report. Change the start_time and end_time values to match the desired
window. */

/* The following three variables (start_time, end_time, and edition_to_check)
are the only variables that you need to set in the script.

During daylight savings time, the start_time and end_time variables should
follow this format: 2024-02-20 00:00:00-08. */

SET start_time = "2023-07-20 00:00:00-07";
SET end_time = "2023-07-28 00:00:00-07";
SET edition_to_check = 'ENTERPRISE';

/* The following function returns the slot seconds for the time window between
two capacity changes. For example, if there are 100 slots between (2023-06-01
10:00:00, 2023-06-01 11:00:00), then during that window the total slot seconds
will be 100 * 3600.

This script calculates a specific window (based on the variables defined above),
which is why the following script includes script_start_timestamp_unix_millis
and script_end_timestamp_unix_millis. */

CREATE TEMP FUNCTION
GetSlotSecondsBetweenChanges(
  slots FLOAT64,
  range_begin_timestamp_unix_millis FLOAT64,
  range_end_timestamp_unix_millis FLOAT64,
  script_start_timestamp_unix_millis FLOAT64,
  script_end_timestamp_unix_millis FLOAT64)
RETURNS INT64
LANGUAGE js
AS r"""
    if (script_end_timestamp_unix_millis < range_begin_timestamp_unix_millis || script_start_timestamp_unix_millis > range_end_timestamp_unix_millis) {
      return 0;
    }
    var begin = Math.max(script_start_timestamp_unix_millis, range_begin_timestamp_unix_millis)
    var end = Math.min(script_end_timestamp_unix_millis, range_end_timestamp_unix_millis)
    return slots * Math.ceil((end - begin) / 1000.0)
""";

/*
Sample CAPACITY_COMMITMENT_CHANGES data (unrelated columns ignored):
+---------------------+------------------------+-----------------+--------+------------+--------+
|  change_timestamp   | capacity_commitment_id | commitment_plan | state  | slot_count | action |
+---------------------+------------------------+-----------------+--------+------------+--------+
| 2023-07-20 19:30:27 | 12954109101902401697   | ANNUAL          | ACTIVE |        100 | CREATE |
| 2023-07-27 22:29:21 | 11445583810276646822   | FLEX            | ACTIVE |        100 | CREATE |
| 2023-07-27 23:10:06 | 7341455530498381779    | MONTHLY         | ACTIVE |        100 | CREATE |
| 2023-07-27 23:11:06 | 7341455530498381779    | FLEX            | ACTIVE |        100 | UPDATE |

The last row indicates a special change from MONTHLY to FLEX, which happens
because of commercial migration.

*/

WITH
  /*
  Information containing which commitment might have plan
  updated (e.g. renewal or commercial migration). For example:
  +------------------------+------------------+--------------------+--------+------------+--------+-----------+----------------------------+
  |  change_timestamp   | capacity_commitment_id | commitment_plan | state  | slot_count | action | next_plan | next_plan_change_timestamp |
  +---------------------+------------------------+-----------------+--------+------------+--------+-----------+----------------------------+
  | 2023-07-20 19:30:27 | 12954109101902401697   | ANNUAL          | ACTIVE |        100 | CREATE | ANNUAL    |        2023-07-20 19:30:27 |
  | 2023-07-27 22:29:21 | 11445583810276646822   | FLEX            | ACTIVE |        100 | CREATE | FLEX      |        2023-07-27 22:29:21 |
  | 2023-07-27 23:10:06 | 7341455530498381779    | MONTHLY         | ACTIVE |        100 | CREATE | FLEX      |        2023-07-27 23:11:06 |
  | 2023-07-27 23:11:06 | 7341455530498381779    | FLEX            | ACTIVE |        100 | UPDATE | FLEX      |        2023-07-27 23:11:06 |
  */
  commitments_with_next_plan AS (
    SELECT
      *,
      IFNULL(
        LEAD(commitment_plan)
          OVER (
            PARTITION BY capacity_commitment_id ORDER BY change_timestamp ASC
          ),
        commitment_plan)
        next_plan,
      IFNULL(
        LEAD(change_timestamp)
          OVER (
            PARTITION BY capacity_commitment_id ORDER BY change_timestamp ASC
          ),
        change_timestamp)
        next_plan_change_timestamp
    FROM
      `region-us.INFORMATION_SCHEMA.CAPACITY_COMMITMENT_CHANGES_BY_PROJECT`
  ),

  /*
  Insert a 'DELETE' action for those with updated plans. The FLEX commitment
  '7341455530498381779' is has no 'CREATE' action, and is instead labeled as an
  'UPDATE' action.

  For example:
  +---------------------+------------------------+-----------------+--------+------------+--------+
  |  change_timestamp   | capacity_commitment_id | commitment_plan | state  | slot_count | action |
  +---------------------+------------------------+-----------------+--------+------------+--------+
  | 2023-07-20 19:30:27 | 12954109101902401697   | ANNUAL          | ACTIVE |        100 | CREATE |
  | 2023-07-27 22:29:21 | 11445583810276646822   | FLEX            | ACTIVE |        100 | CREATE |
  | 2023-07-27 23:10:06 | 7341455530498381779    | MONTHLY         | ACTIVE |        100 | CREATE |
  | 2023-07-27 23:11:06 | 7341455530498381779    | FLEX            | ACTIVE |        100 | UPDATE |
  | 2023-07-27 23:11:06 | 7341455530498381779    | MONTHLY         | ACTIVE |        100 | DELETE |
  */

  capacity_changes_with_additional_deleted_event_for_changed_plan AS (
    SELECT
      next_plan_change_timestamp AS change_timestamp,
      project_id,
      project_number,
      capacity_commitment_id,
      commitment_plan,
      state,
      slot_count,
      'DELETE' AS action,
      commitment_start_time,
      commitment_end_time,
      failure_status,
      renewal_plan,
      user_email,
      edition,
      is_flat_rate,
    FROM commitments_with_next_plan
    WHERE commitment_plan <> next_plan
    UNION ALL
    SELECT * FROM `region-us.INFORMATION_SCHEMA.CAPACITY_COMMITMENT_CHANGES_BY_PROJECT`
  ),

  /*
  The committed_slots change the history. For example:
  +---------------------+------------------------+------------------+-----------------+
  |  change_timestamp   | capacity_commitment_id | slot_count_delta | commitment_plan |
  +---------------------+------------------------+------------------+-----------------+
  | 2023-07-20 19:30:27 | 12954109101902401697   |              100 | ANNUAL          |
  | 2023-07-27 22:29:21 | 11445583810276646822   |              100 | FLEX            |
  | 2023-07-27 23:10:06 | 7341455530498381779    |              100 | MONTHLY         |
  | 2023-07-27 23:11:06 | 7341455530498381779    |             -100 | MONTHLY         |
  | 2023-07-27 23:11:06 | 7341455530498381779    |              100 | FLEX            |
  */

  capacity_commitment_slot_data AS (
    SELECT
      change_timestamp,
      capacity_commitment_id,
      CASE
        WHEN action = "CREATE" OR action = "UPDATE"
          THEN
            IFNULL(
              IF(
                LAG(action)
                  OVER (
                    PARTITION BY capacity_commitment_id ORDER BY change_timestamp ASC, action ASC
                  )
                  IN UNNEST(['CREATE', 'UPDATE']),
                slot_count - LAG(slot_count)
                  OVER (
                    PARTITION BY capacity_commitment_id ORDER BY change_timestamp ASC, action ASC
                  ),
                slot_count),
              slot_count)
        ELSE
          IF(
            LAG(action)
              OVER (PARTITION BY capacity_commitment_id ORDER BY change_timestamp ASC, action ASC)
              IN UNNEST(['CREATE', 'UPDATE']),
            -1 * slot_count,
            0)
        END
        AS slot_count_delta,
      commitment_plan
    FROM
      capacity_changes_with_additional_deleted_event_for_changed_plan
    WHERE
      state = "ACTIVE"
      AND edition = edition_to_check
      AND change_timestamp <= end_time
  ),

  /*
  The total_committed_slots history for each plan. For example:
  +---------------------+---------------+-----------------+
  |  change_timestamp   | capacity_slot | commitment_plan |
  +---------------------+---------------+-----------------+
  | 2023-07-20 19:30:27 |           100 | ANNUAL          |
  | 2023-07-27 22:29:21 |           100 | FLEX            |
  | 2023-07-27 23:10:06 |           100 | MONTHLY         |
  | 2023-07-27 23:11:06 |             0 | MONTHLY         |
  | 2023-07-27 23:11:06 |           200 | FLEX            |
  */

  running_capacity_commitment_slot_data AS (
    SELECT
      change_timestamp,
      SUM(slot_count_delta)
        OVER (
          PARTITION BY commitment_plan
          ORDER BY change_timestamp
          RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        )
        AS capacity_slot,
      commitment_plan,
    FROM
      capacity_commitment_slot_data
  ),

  /*

  The slot_seconds between each changes, partitioned by each plan. For example:
  +---------------------+--------------+-----------------+
  |  change_timestamp   | slot_seconds | commitment_plan |
  +---------------------+--------------+-----------------+
  | 2023-07-20 19:30:27 |     64617300 | ANNUAL          |
  | 2023-07-27 22:29:21 |       250500 | FLEX            |
  | 2023-07-27 23:10:06 |         6000 | MONTHLY         |
  | 2023-07-27 23:11:06 |            0 | MONTHLY         |
  | 2023-07-27 23:11:06 |      5626800 | FLEX            |
  */

  slot_seconds_data AS (
    SELECT
      change_timestamp,
      GetSlotSecondsBetweenChanges(
        capacity_slot,
        UNIX_MILLIS(change_timestamp),
        UNIX_MILLIS(
          IFNULL(
            LEAD(change_timestamp)
              OVER (PARTITION BY commitment_plan ORDER BY change_timestamp ASC),
            CURRENT_TIMESTAMP())),
        UNIX_MILLIS(start_time),
        UNIX_MILLIS(end_time)) AS slot_seconds,
      commitment_plan,
    FROM
      running_capacity_commitment_slot_data
    WHERE
      change_timestamp <= end_time
  )

/*

The final result is similar to the following:
+-----------------+--------------------+
| commitment_plan | total_slot_seconds |
+-----------------+--------------------+
| ANNUAL          |           64617300 |
| MONTHLY         |               6000 |
| FLEX            |            5877300 |
*/

SELECT
  commitment_plan,
  SUM(slot_seconds) AS total_slot_seconds
FROM
  slot_seconds_data
GROUP BY
  commitment_plan

以下脚本会检查承诺未涵盖的特定版本的槽用量。此用量包含两种类型的槽：承诺未涵盖的扩缩槽和基准槽。

展开以查看用于未涵盖的场景的脚本

/*
This script has several parts:
1. Calculate the baseline and scaled slots for reservations
2. Calculate the committed slots
3. Join the two results above to calculate the baseline not covered by committed
   slots
4. Aggregate the number
*/

-- variables
DECLARE start_time, end_time TIMESTAMP;

DECLARE
  edition_to_check STRING;

/* Google uses Pacific Time to calculate the billing period for all customers,
regardless of their time zone. Use the following format if you want to match the
billing report. Change the start_time and end_time values to match the desired
window. */

/* The following three variables (start_time, end_time, and edition_to_check)
are the only variables that you need to set in the script.

During daylight savings time, the start_time and end_time variables should
follow this format: 2024-02-20 00:00:00-08. */

SET start_time = "2023-07-20 00:00:00-07";
SET end_time = "2023-07-28 00:00:00-07";
SET edition_to_check = 'ENTERPRISE';

/*
The following function returns the slot seconds for the time window between
two capacity changes. For example, if there are 100 slots between (2023-06-01
10:00:00, 2023-06-01 11:00:00), then during that window the total slot seconds
will be 100 * 3600.

This script calculates a specific window (based on the variables defined above),
which is why the following script includes script_start_timestamp_unix_millis
and script_end_timestamp_unix_millis. */

CREATE TEMP FUNCTION GetSlotSecondsBetweenChanges(
  slots FLOAT64,
  range_begin_timestamp_unix_millis FLOAT64,
  range_end_timestamp_unix_millis FLOAT64,
  script_start_timestamp_unix_millis FLOAT64,
  script_end_timestamp_unix_millis FLOAT64)
RETURNS INT64
LANGUAGE js
AS r"""
    if (script_end_timestamp_unix_millis < range_begin_timestamp_unix_millis || script_start_timestamp_unix_millis > range_end_timestamp_unix_millis) {
      return 0;
    }
    var begin = Math.max(script_start_timestamp_unix_millis, range_begin_timestamp_unix_millis)
    var end = Math.min(script_end_timestamp_unix_millis, range_end_timestamp_unix_millis)
    return slots * Math.ceil((end - begin) / 1000.0)
""";
/*
Sample RESERVATION_CHANGES data (unrelated columns ignored):
+---------------------+------------------+--------+---------------+---------------+
|  change_timestamp   | reservation_name | action | slot_capacity | current_slots |
+---------------------+------------------+--------+---------------+---------------+
| 2023-07-27 22:24:15 | res1             | CREATE |           300 |             0 |
| 2023-07-27 22:25:21 | res1             | UPDATE |           300 |           180 |
| 2023-07-27 22:39:14 | res1             | UPDATE |           300 |           100 |
| 2023-07-27 22:40:20 | res2             | CREATE |           300 |             0 |
| 2023-07-27 22:54:18 | res2             | UPDATE |           300 |           120 |
| 2023-07-27 22:55:23 | res1             | UPDATE |           300 |             0 |

Sample CAPACITY_COMMITMENT_CHANGES data (unrelated columns ignored):
+---------------------+------------------------+-----------------+--------+------------+--------+
|  change_timestamp   | capacity_commitment_id | commitment_plan | state  | slot_count | action |
+---------------------+------------------------+-----------------+--------+------------+--------+
| 2023-07-20 19:30:27 | 12954109101902401697   | ANNUAL          | ACTIVE |        100 | CREATE |
| 2023-07-27 22:29:21 | 11445583810276646822   | FLEX            | ACTIVE |        100 | CREATE |
| 2023-07-27 23:10:06 | 7341455530498381779    | MONTHLY         | ACTIVE |        100 | CREATE |
*/

WITH
  /*
  The scaled_slots & baseline change history:
  +---------------------+------------------+------------------------------+---------------------+
  |  change_timestamp   | reservation_name | autoscale_current_slot_delta | baseline_slot_delta |
  +---------------------+------------------+------------------------------+---------------------+
  | 2023-07-27 22:24:15 | res1             |                            0 |                 300 |
  | 2023-07-27 22:25:21 | res1             |                          180 |                   0 |
  | 2023-07-27 22:39:14 | res1             |                          -80 |                   0 |
  | 2023-07-27 22:40:20 | res2             |                            0 |                 300 |
  | 2023-07-27 22:54:18 | res2             |                          120 |                   0 |
  | 2023-07-27 22:55:23 | res1             |                         -100 |                   0 |
  */
  reservation_slot_data AS (
    SELECT
      change_timestamp,
      reservation_name,
      CASE action
        WHEN "CREATE" THEN autoscale.current_slots
        WHEN "UPDATE"
          THEN
            IFNULL(
              autoscale.current_slots - LAG(autoscale.current_slots)
                OVER (
                  PARTITION BY project_id, reservation_name
                  ORDER BY change_timestamp ASC, action ASC
                ),
              IFNULL(
                autoscale.current_slots,
                IFNULL(
                  -1 * LAG(autoscale.current_slots)
                    OVER (
                      PARTITION BY project_id, reservation_name
                      ORDER BY change_timestamp ASC, action ASC
                    ),
                  0)))
        WHEN "DELETE"
          THEN
            IF(
              LAG(action)
                OVER (
                  PARTITION BY project_id, reservation_name
                  ORDER BY change_timestamp ASC, action ASC
                )
                IN UNNEST(['CREATE', 'UPDATE']),
              -1 * autoscale.current_slots,
              0)
        END
        AS autoscale_current_slot_delta,
      CASE action
        WHEN "CREATE" THEN slot_capacity
        WHEN "UPDATE"
          THEN
            IFNULL(
              slot_capacity - LAG(slot_capacity)
                OVER (
                  PARTITION BY project_id, reservation_name
                  ORDER BY change_timestamp ASC, action ASC
                ),
              IFNULL(
                slot_capacity,
                IFNULL(
                  -1 * LAG(slot_capacity)
                    OVER (
                      PARTITION BY project_id, reservation_name
                      ORDER BY change_timestamp ASC, action ASC
                    ),
                  0)))
        WHEN "DELETE"
          THEN
            IF(
              LAG(action)
                OVER (
                  PARTITION BY project_id, reservation_name
                  ORDER BY change_timestamp ASC, action ASC
                )
                IN UNNEST(['CREATE', 'UPDATE']),
              -1 * slot_capacity,
              0)
        END
        AS baseline_slot_delta,
    FROM
      `region-us.INFORMATION_SCHEMA.RESERVATION_CHANGES`
    WHERE
      edition = edition_to_check
      AND change_timestamp <= end_time
  ),

  -- Convert the above to running total
  /*
  +---------------------+-------------------------+----------------+
  |  change_timestamp   | autoscale_current_slots | baseline_slots |
  +---------------------+-------------------------+----------------+
  | 2023-07-27 22:24:15 |                       0 |            300 |
  | 2023-07-27 22:25:21 |                     180 |            300 |
  | 2023-07-27 22:39:14 |                     100 |            300 |
  | 2023-07-27 22:40:20 |                     100 |            600 |
  | 2023-07-27 22:54:18 |                     220 |            600 |
  | 2023-07-27 22:55:23 |                     120 |            600 |
  */
  running_reservation_slot_data AS (
    SELECT
      change_timestamp,
      SUM(autoscale_current_slot_delta)
        OVER (ORDER BY change_timestamp RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        AS autoscale_current_slots,
      SUM(baseline_slot_delta)
        OVER (ORDER BY change_timestamp RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        AS baseline_slots,
    FROM
      reservation_slot_data
  ),

  /*
  The committed_slots change history. For example:
  +---------------------+------------------------+------------------+
  |  change_timestamp   | capacity_commitment_id | slot_count_delta |
  +---------------------+------------------------+------------------+
  | 2023-07-20 19:30:27 | 12954109101902401697   |              100 |
  | 2023-07-27 22:29:21 | 11445583810276646822   |              100 |
  | 2023-07-27 23:10:06 | 7341455530498381779    |              100 |
  */
  capacity_commitment_slot_data AS (
    SELECT
      change_timestamp,
      capacity_commitment_id,
      CASE
        WHEN action = "CREATE" OR action = "UPDATE"
          THEN
            IFNULL(
              IF(
                LAG(action)
                  OVER (
                    PARTITION BY capacity_commitment_id ORDER BY change_timestamp ASC, action ASC
                  )
                  IN UNNEST(['CREATE', 'UPDATE']),
                slot_count - LAG(slot_count)
                  OVER (
                    PARTITION BY capacity_commitment_id ORDER BY change_timestamp ASC, action ASC
                  ),
                slot_count),
              slot_count)
        ELSE
          IF(
            LAG(action)
              OVER (PARTITION BY capacity_commitment_id ORDER BY change_timestamp ASC, action ASC)
              IN UNNEST(['CREATE', 'UPDATE']),
            -1 * slot_count,
            0)
        END
        AS slot_count_delta
    FROM
      `region-us.INFORMATION_SCHEMA.CAPACITY_COMMITMENT_CHANGES_BY_PROJECT`
    WHERE
      state = "ACTIVE"
      AND edition = edition_to_check
      AND change_timestamp <= end_time
  ),

  /*
  The total_committed_slots history. For example:
  +---------------------+---------------+
  |  change_timestamp   | capacity_slot |
  +---------------------+---------------+
  | 2023-07-20 19:30:27 |           100 |
  | 2023-07-27 22:29:21 |           200 |
  | 2023-07-27 23:10:06 |           300 |
  */
  running_capacity_commitment_slot_data AS (
    SELECT
      change_timestamp,
      SUM(slot_count_delta)
        OVER (ORDER BY change_timestamp RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        AS capacity_slot
    FROM
      capacity_commitment_slot_data
  ),

  /* Add next_change_timestamp to the above data,
   which will be used when joining with reservation data. For example:
  +---------------------+-----------------------+---------------+
  |  change_timestamp   | next_change_timestamp | capacity_slot |
  +---------------------+-----------------------+---------------+
  | 2023-07-20 19:30:27 |   2023-07-27 22:29:21 |           100 |
  | 2023-07-27 22:29:21 |   2023-07-27 23:10:06 |           200 |
  | 2023-07-27 23:10:06 |   2023-07-31 00:14:37 |           300 |
  */
  running_capacity_commitment_slot_data_with_next_change AS (
    SELECT
      change_timestamp,
      IFNULL(LEAD(change_timestamp) OVER (ORDER BY change_timestamp ASC), CURRENT_TIMESTAMP())
        AS next_change_timestamp,
      capacity_slot
    FROM
      running_capacity_commitment_slot_data
  ),

  /*
  Whenever we have a change in reservations or commitments,
  the scaled_slots_and_baseline_not_covered_by_commitments will be changed.
  Hence we get a collection of all the change_timestamp from both tables.
  +---------------------+
  |  change_timestamp   |
  +---------------------+
  | 2023-07-20 19:30:27 |
  | 2023-07-27 22:24:15 |
  | 2023-07-27 22:25:21 |
  | 2023-07-27 22:29:21 |
  | 2023-07-27 22:39:14 |
  | 2023-07-27 22:40:20 |
  | 2023-07-27 22:54:18 |
  | 2023-07-27 22:55:23 |
  | 2023-07-27 23:10:06 |
  */
  merged_timestamp AS (
    SELECT
      change_timestamp
    FROM
      running_reservation_slot_data
    UNION DISTINCT
    SELECT
      change_timestamp
    FROM
      running_capacity_commitment_slot_data
  ),

  /*
  Change running reservation-slots and make sure we have one row when commitment changes.
  +---------------------+-------------------------+----------------+
  |  change_timestamp   | autoscale_current_slots | baseline_slots |
  +---------------------+-------------------------+----------------+
  | 2023-07-20 19:30:27 |                       0 |              0 |
  | 2023-07-27 22:24:15 |                       0 |            300 |
  | 2023-07-27 22:25:21 |                     180 |            300 |
  | 2023-07-27 22:29:21 |                     180 |            300 |
  | 2023-07-27 22:39:14 |                     100 |            300 |
  | 2023-07-27 22:40:20 |                     100 |            600 |
  | 2023-07-27 22:54:18 |                     220 |            600 |
  | 2023-07-27 22:55:23 |                     120 |            600 |
  | 2023-07-27 23:10:06 |                     120 |            600 |
  */
  running_reservation_slot_data_with_merged_timestamp AS (
    SELECT
      change_timestamp,
      IFNULL(
        autoscale_current_slots,
        IFNULL(
          LAST_VALUE(autoscale_current_slots IGNORE NULLS) OVER (ORDER BY change_timestamp ASC), 0))
        AS autoscale_current_slots,
      IFNULL(
        baseline_slots,
        IFNULL(LAST_VALUE(baseline_slots IGNORE NULLS) OVER (ORDER BY change_timestamp ASC), 0))
        AS baseline_slots
    FROM
      running_reservation_slot_data
    RIGHT JOIN
      merged_timestamp
      USING (change_timestamp)
  ),

  /*
  Join the above, so that we will know the number for baseline not covered by commitments.
  +---------------------+-----------------------+-------------------------+------------------------------------+
  |  change_timestamp   | next_change_timestamp | autoscale_current_slots | baseline_not_covered_by_commitment |
  +---------------------+-----------------------+-------------------------+------------------------------------+
  | 2023-07-20 19:30:27 |   2023-07-27 22:24:15 |                       0 |                                  0 |
  | 2023-07-27 22:24:15 |   2023-07-27 22:25:21 |                       0 |                                200 |
  | 2023-07-27 22:25:21 |   2023-07-27 22:29:21 |                     180 |                                200 |
  | 2023-07-27 22:29:21 |   2023-07-27 22:39:14 |                     180 |                                100 |
  | 2023-07-27 22:39:14 |   2023-07-27 22:40:20 |                     100 |                                100 |
  | 2023-07-27 22:40:20 |   2023-07-27 22:54:18 |                     100 |                                400 |
  | 2023-07-27 22:54:18 |   2023-07-27 22:55:23 |                     220 |                                400 |
  | 2023-07-27 22:55:23 |   2023-07-27 23:10:06 |                     120 |                                400 |
  | 2023-07-27 23:10:06 |   2023-07-31 00:16:07 |                     120 |                                300 |
  */
  scaled_slots_and_baseline_not_covered_by_commitments AS (
    SELECT
      r.change_timestamp,
      IFNULL(LEAD(r.change_timestamp) OVER (ORDER BY r.change_timestamp ASC), CURRENT_TIMESTAMP())
        AS next_change_timestamp,
      r.autoscale_current_slots,
      IF(
        r.baseline_slots - IFNULL(c.capacity_slot, 0) > 0,
        r.baseline_slots - IFNULL(c.capacity_slot, 0),
        0) AS baseline_not_covered_by_commitment
    FROM
      running_reservation_slot_data_with_merged_timestamp r
    LEFT JOIN
      running_capacity_commitment_slot_data_with_next_change c
      ON
        r.change_timestamp >= c.change_timestamp
        AND r.change_timestamp < c.next_change_timestamp
  ),

  /*
  The slot_seconds between each changes. For example:
  +---------------------+--------------------+
  |  change_timestamp   | slot_seconds |
  +---------------------+--------------+
  | 2023-07-20 19:30:27 |            0 |
  | 2023-07-27 22:24:15 |        13400 |
  | 2023-07-27 22:25:21 |        91580 |
  | 2023-07-27 22:29:21 |       166320 |
  | 2023-07-27 22:39:14 |        13200 |
  | 2023-07-27 22:40:20 |       419500 |
  | 2023-07-27 22:54:18 |        40920 |
  | 2023-07-27 22:55:23 |       459160 |
  | 2023-07-27 23:10:06 |     11841480 |
  */
  slot_seconds_data AS (
    SELECT
      change_timestamp,
      GetSlotSecondsBetweenChanges(
        autoscale_current_slots + baseline_not_covered_by_commitment,
        UNIX_MILLIS(change_timestamp),
        UNIX_MILLIS(next_change_timestamp),
        UNIX_MILLIS(start_time),
        UNIX_MILLIS(end_time)) AS slot_seconds
    FROM
      scaled_slots_and_baseline_not_covered_by_commitments
    WHERE
      change_timestamp <= end_time AND next_change_timestamp > start_time
  )

/*
Final result for this example:
+--------------------+
| total_slot_seconds |
+--------------------+
|           13045560 |
*/
SELECT
  SUM(slot_seconds) AS total_slot_seconds
FROM
  slot_seconds_data

监控作业性能

您可能需要调整自动扩缩 max_slots，以避免产生较高的费用。以下查询会提供有关作业性能的背景信息，以便您为工作负载选择正确的自动扩缩槽数。

以下查询会提供有关预留过去作业性能的详细信息：

SELECT
    AVG(TIMESTAMP_DIFF(end_time, creation_time, MILLISECOND)) as avg_latency_ms,
    SUM(total_bytes_processed) as total_bytes,
    COUNT(*) as query_numbers,
FROM
    `PROJECT_ID.region-REGION_NAME`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
WHERE creation_time >= START_TIME
    AND creation_time < END_TIME
    AND (statement_type != "SCRIPT" OR statement_type IS NULL)
    AND reservation_id = RESERVATION_ID

替换以下内容：

PROJECT_ID：项目的 ID
REGION_NAME：项目的区域
START_TIME：您要开始查看数据的创建时间
END_TIME：您要停止查看数据的创建时间
RESERVATION_ID：预留 ID

以下示例会获取五天内的作业详情：

SELECT
    AVG(TIMESTAMP_DIFF(end_time, creation_time, MILLISECOND)) as avg_latency_ms,
    SUM(total_bytes_processed) as total_bytes,
    COUNT(*) as query_numbers,
FROM
    `myproject.region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
WHERE creation_time >= '2024-06-25 00:00:00-07'
    AND creation_time < '2024-06-30 00:00:00-07'
    AND (statement_type != "SCRIPT" OR statement_type IS NULL)
    AND reservation_id = reservationID

配额

预留大小上限的总和不应超出槽配额。

如需了解配额，请参阅配额和限制。

后续步骤

如需详细了解 BigQuery 版本，请参阅 BigQuery 简介。
如需详细了解槽，请参阅了解槽。
如需详细了解预留，请参阅预留简介。