運算單元自動調度簡介
您設定為使用運算單元自動調度功能的預留項目,會自動調整已分配的容量,以符合工作負載需求。隨著工作負載增加或減少,BigQuery 會動態調整運算單元至適當的等級。只有 BigQuery 版本支援運算單元自動調度的保留項目。
使用自動調度資源保留項目
您不需要先購買運算單元承諾,即可建立自動調整大小保留項目。運算單元承諾可為持續使用的運算單元提供折扣費率,但在自動調度資源預留情況下,則為選用項目。如要建立自動調度資源預留項目,請為預留項目指派運算單元數量上限 (預留項目大小上限)。您可以透過將預留項目的運算單元數量上限,減去指派給該預留項目的任何選用基準運算單元數量,找出自動調度資源運算單元數量上限。
建立自動調度資源預訂時,請考量下列事項:
- BigQuery 幾乎可立即調整保留項目,直到達到執行工作所需的運算單元數量,或達到保留項目可用的運算單元數量上限為止。運算單元一律會自動調度為 50 的倍數。
- 系統會根據實際用量進行升級,並將數量四捨五入至最接近的 50 個插槽增量。
- 在擴充時,系統會根據相關聯的版本收取容量運算價格來計費。系統會依據調整後的運算單元數量收費,而非實際使用的運算單元數量。即使導致 BigQuery 擴充的工作失敗,這筆費用仍會收取。因此,請勿使用工作資訊結構定義來比對帳單。請改為參閱「使用資訊結構定義監控自動調整大小功能」。
- 雖然插槽數量一律以 50 的倍數調整,但在一個步驟中可能會調整超過 50 個插槽。舉例來說,如果工作負載需要額外 450 個運算單元,BigQuery 會嘗試一次擴充 450 個運算單元,以符合容量需求。
- 當與預留狀態相關聯的工作不再需要容量時,BigQuery 會縮減容量 (最短為 1 分鐘)。
任何自動調整的容量都會保留至少 60 秒。這段 60 秒的時間稱為縮減時間窗格。任何新的容量高峰都會重設縮減時間窗格,並將整個容量層級視為新的授權。不過,如果上次增加容量後已過 60 秒或更久,且需求減少,系統會減少容量,但不會重設縮減時間窗,讓系統能夠連續減少容量,且不會造成延遲。
舉例來說,如果初始工作負載容量擴充至 100 個時段,則峰值會保留至少 60 秒。如果在縮減資源的時間區間內,工作負載縮減至 200 個時段的新峰值,系統就會開始新的縮減資源時間區間,為期 60 秒。如果在這個縮減時間窗格中沒有新的峰值,工作負載會在 60 秒結束時開始縮減。
請參考下列詳細範例:在 12:00:00,您的初始容量會調整為 100 個時段,且使用時間為 1 秒。這個峰值會從 12:00:00 開始,持續至少 60 秒。60 秒過後 (12:01:01),如果新用量為 50 個時段,BigQuery 就會縮減至 50 個時段。如果在 12:01:02,新用量為 0 個時段,BigQuery 會再次立即縮減至 0 個時段。縮減期限結束後,BigQuery 可以連續縮減多次,而不需要新的縮減期限。
如要瞭解如何使用自動調度資源,請參閱「使用時段自動調度資源」。
使用基準和自動調度資源運算單元模式的預留項目
除了指定預留空間大小上限之外,您也可以視需要指定每個預留項目的運算單元數量基準值。「基準數量」是指一律會分配給預留項目的運算單元最小數量,而且必定會產生這個數量的運算單元費用。系統只會在使用完所有基準運算單元 (以及閒置運算單元,如果適用的話) 後,才會新增自動調度運算單元。您可以將一個預留項目中的閒置基準運算單元,共用給其他需要運算能力的預留項目。
您可以每隔幾分鐘就增加預留項目的基準運算單元數量。如果您想減少基準運算單元,且最近變更了基準運算單元容量,而基準運算單元超出承諾使用運算單元,則只能每小時變更一次。否則,您可以每隔幾分鐘就減少基準時段。
基準和自動調度資源的運算單元旨在根據近期的工作負載提供容量。如果您預期工作負載會大幅增加,且與近期的工作負載截然不同,建議您在事件發生前提高基準容量,而非依賴自動調度資源的空缺來因應工作負載容量。如果在增加基準容量時遇到問題,請等待 15 分鐘後再重試要求。
如果預留作業沒有基準運算單元,或是未設定從其他預留作業借用閒置運算單元,BigQuery 就會嘗試調整。否則,必須先充分利用基準配額,才能進行調整。
預留項目會依據下列優先順序使用及新增運算單元:
- 基準運算單元。
- 閒置運算單元共用功能 (如果已啟用)。預留項目只能共用閒置基準或已提交的運算單元,這些運算單元必須是使用相同版本和區域建立的其他預留項目。
- 自動調度運算單元。
在下列範例中,投放量會從指定的基準金額開始調整。etl
和 dashboard
預留項目的基準大小分別為 700 和 300 個運算單元。
在這個範例中,etl
預留項目可調度至 1300 個運算單元 (700 個基準運算單元加上 600 個自動調度運算單元)。如果 dashboard
保留項目未使用,etl
保留項目可以使用 dashboard
保留項目的 300 個運算單元 (如果沒有執行的工作),因此最多可使用 1,600 個運算單元。
dashboard
預留項目可擴充至 1100 個運算單元 (300 個基準運算單元加上 800 個自動調度運算單元)。如果 etl
預留項目完全閒置,dashboard
預留項目最多可擴充至 1, 800 個運算單元 (300 個基準運算單元加上 800 個自動調度運算單元,加上 etl
預留項目中的 700 個閒置運算單元)。
如果 etl
預留項目需要超過 700 個一律可用的基準運算單元,系統會依序使用下列方法嘗試新增運算單元:
- 700 個基準運算單元。
- 與
dashboard
預留項目中的 300 個基準運算單元共用閒置運算單元。您的預留項目只會與使用相同版本建立的其他預留項目共用閒置基準運算單元。 - 將額外運算單元增加至預留項目大小上限。
使用運算單元承諾使用合約
以下範例說明如何使用容量承諾功能,為時段進行自動調整。
與預留項目基準數量相同,運算單元承諾使用合約可讓您分配固定數量的運算單元,供所有預留項目使用。與基準運算單元不同,承諾在約期內不得減少。運算單元承諾使用合約為選用項目,但如果需要長時間使用基準運算單元,這項功能可以節省成本。
在本例中,您需要按照預先定義的費率支付承諾容量的運算單元費用。在自動調度資源功能啟用後,系統會根據自動調度資源費率,針對預留狀態為調度狀態的自動調度資源數量收取費用。就自動調度資源配置費率而言,您需要為已調整的運算單元數量付費,而非已使用的運算單元數量。
以下範例顯示預訂量超出已提交的預訂量時的情況。
在本範例中,兩個預留項目之間共有 1000 個基準運算單元,其中 etl
預留項目有 500 個,dashboard
預留項目有 500 個。不過,承諾只涵蓋 800 個時段。在這種情況下,系統會依即付即用 (PAYG) 費率收取超出部分的運算單元費用。
可用的運算單元數量上限
您可以計算預留項目可使用的運算單元數量上限,方法是將基準運算單元數量、自動調度運算單元數量上限,以及使用相同版本建立的承諾中未包含在基準運算單元中的任何運算單元數量加總起來。上圖中的範例設定如下:
- 承諾每年使用 1000 個運算單元。這些運算單元會指派為
etl
預留項目和dashboard
預留項目的基準運算單元。 - 指派給
etl
預留項目的 700 個基準運算單元。 - 指派給
dashboard
預留項目的 300 個基準運算單元。 etl
預訂的自動調度運算單元為 600 個。- 為
dashboard
預訂建立 800 個自動調度運算單元。
對於 etl
預留項目,運算單元數量上限等於 etl
基準運算單元數量 (700) 加上 dashboard
基準運算單元數量 (300,如果所有運算單元都處於閒置狀態) 加上自動調度資源運算單元數量上限 (600)。因此,在本例中,etl
預留項目可使用的運算單元數量上限為 1600 個。這個數字超過容量承諾中的數量。
在以下範例中,每年承諾的運算單元數量超過指派的基準運算單元數量。
在這個範例中,我們有:
- 承諾每年使用 1,600 個運算單元。
- 預留項目大小上限為 1500 (包括 500 個自動調度運算單元)。
- 指派給
etl
預留項目的 1000 個基準運算單元。
預留項目可用的運算單元數量上限等於基準運算單元數量 (1000),加上任何未專用於基準運算單元的承諾閒置運算單元數量 (1600 個年度運算單元 - 1000 個基準運算單元 = 600 個),再加上自動調度運算單元數量 (500 個)。因此,這個預留項目的最大可用運算單元數量為 2100。自動調度運算單元是指超過承諾容量的額外運算單元。
自動調整最佳做法
首次使用自動配置器時,請根據過去和預期的效能,將自動調度資源的運算單元數量設為有意義的數字。建立預留後,請主動監控失敗率、成效和帳單,並視需要調整自動調度資源的空缺數量。
自動配置器在縮減資源前,至少需要 1 分鐘的時間,因此請務必設定自動調整的槽數上限,以便在效能和成本之間取得平衡。如果自動調度運算單元數量上限過大,且工作可在幾秒內使用所有運算單元完成,您仍會為整分鐘的運算單元數量上限付費。如果您將上限的空缺數量調低至目前數量的一半,預留數量就會縮減,且工作可在該分鐘使用更多
slot_seconds
,進而減少浪費。如要瞭解如何判斷您的時段需求,請參閱「監控工作成效」。如要改用其他方法來判斷運算單元需求,請參閱「查看版本運算單元建議」。運算單元用量有時會超過基準值加上調節運算單元的總和。您只需為超過基準運算單元數量加上調整後運算單元的運算單元使用量付費。
自動配置器最適合用於處理耗用大量資源且執行時間較長的工作負載,例如含有多筆並行查詢的工作負載。請避免一次傳送多個查詢,因為每個查詢都會調整預留空間,且預留空間會維持至少 1 分鐘。如果您持續傳送查詢,導致工作負載持續增加,設定基準並購買承諾,即可以折扣價格取得固定容量。
BigQuery 自動調度功能的運算能力取決於可用運算能力。BigQuery 會根據過往的使用情形,盡力滿足客戶的容量需求。如要確保容量,您可以設定選用的運算單元基準值,也就是保證保留項目中的運算單元數量。使用基準值時,您可以立即使用時段,無論是否使用,都必須付費。如要確保有足夠的容量來因應大量非有機需求 (例如流量高峰的假日),請提前幾週與 BigQuery 團隊聯絡。
系統一律會向您收取基準運算單元的費用。如果容量承諾到期,您可能需要手動調整預留空間中的基準運算單元數量,以免產生不必要的費用。舉例來說,假設您有 100 個運算單元的 1 年承諾,以及 100 個運算單元的保留量。承諾期已過,且沒有續約計畫。承諾期到期後,您必須以即付即用費率支付 100 個基準運算單元。
監控自動調度資源
使用管理資源圖表監控運算單元用量時,您可能會發現調節運算單元的數量遠高於運算單元用量,這是因為圖表會在校正期間平滑使用中的運算單元數量。如要查看自動調度時段的使用情形,並取得更精確的詳細資料,請縮短時間範圍選項。這會自動將對齊週期更新為較小的增量。
在下列範例中,圖表顯示的擴充槽數遠超過工作負載需求。
不過,如果您縮短時間範圍選項,讓對齊期間為兩秒,您就會發現自動調度器會根據工作負載需求進行調度,並顯示更準確的資料。您可以拖曳時間範圍選項的開始和結束範圍,調整時間範圍選項。如要顯示最準確的工作負載需求資料,請從「指標」清單中選取「p99」。
如要最準確地查看自動調度資源使用情形,請使用 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
:專案 IDREGION_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 簡介」。
- 如要進一步瞭解時段,請參閱「瞭解時段」。
- 如要進一步瞭解預留項目,請參閱「預留項目簡介」。