Introduction to slots autoscaling
Reservations that you configure to use slots autoscaling automatically scale the allocated capacity to accommodate your workload demands. As your workload increases or decreases, BigQuery dynamically adjusts your slots to an appropriate level. Reservations with slots autoscaling are only available with BigQuery editions .
Use autoscaling reservations
You don't need to purchase slot commitments before creating autoscaling reservations. Slot commitments provide a discounted rate for consistently used slots but are optional with autoscaling reservations. To create an autoscaling reservation, you assign a reservation a maximum number of slots (the max reservation size). You can identify the maximum number of autoscaling slots by subtracting the max reservation size by any optional baseline slots assigned to the reservation.
When you create autoscaling reservations, consider the following:
- BigQuery scales reservations almost instantly until it has reached the number of slots needed to execute the jobs, or it reaches the maximum number of slots available to the reservation. Slots always autoscale to a multiple of 50.
- Scaling up is based on actual usage, and is rounded up to the nearest 50 slot increment.
- Your autoscaled slots are charged at capacity compute pricing for your associated edition while scaling up. You are charged for the number of scaled slots, not the number of slots used. This charge applies even if the job that causes BigQuery to scale up fails. For this reason, don't use the jobs information schema to match the billing. Instead, see Monitor autoscaling with information schema .
- While the number of slots always scales by multiples of 50, it may scale more than 50 slots within one step. For example, if your workload requires an additional 450 slots, BigQuery can attempt to scale by 450 slots at once to meet the capacity requirement.
- BigQuery scales down when the jobs associated with the reservation no longer need the capacity (subject to a 1 minute minimum).
Any autoscaled capacity is retained for at least 60 seconds. This 60-second period is called the scale-down window. Any new peak in capacity resets the scale-down window, treating the entire capacity level as a new grant. However, if 60 seconds or more have passed since the last capacity increase and there is less demand, the system reduces the capacity without resetting the scale-down window, enabling consecutive decreases without an imposed delay.
For example, if your initial workload capacity scales to 100 slots, the peak is retained for at least 60 seconds. If, during that scale-down window, your workload scales to a new peak of 200 slots, a new scale-down window begins for 60 seconds. If there is no new peak during this scale-down window, your workload begins to scale down at the end of the 60 seconds.
Consider the following detailed example: At 12:00:00, your initial capacity scales to 100 slots and the usage lasts for one second. That peak is retained for at least 60 seconds, beginning at 12:00:00. After the 60 seconds have elapsed (at 12:01:01), if the new usage is 50 slots, BigQuery scales down to 50 slots. If, at 12:01:02, the new usage is 0 slots, BigQuery again scales down immediately to 0 slots. After the scale-down window has ended, BigQuery can scale down multiple times consecutively without requiring a new scale-down window.
To learn how to work with autoscaling, see Work with slots autoscaling .
Using reservations with baseline and autoscaling slots
In addition to specifying the maximum reservation size, you can optionallyspecify a baseline number of slots per reservation. The baseline is the minimum number of slots that will always be allocated to the reservation, and you will always be charged for them. Autoscaling slots are only added after all of the baseline slots (and idle slots if applicable) are consumed. You can share idle baseline slots in one reservation with other reservations that need capacity.
You can increase the number of baseline slots in a reservation every few minutes. If you want to decrease your baseline slots, you are limited to once an hour if you have recently changed your baseline slot capacity and your baseline slots exceed your committed slots. Otherwise, you can decrease your baseline slots every few minutes.
Baseline and autoscaling slots are intended to provide capacity based on your recent workload. If you anticipate a large workload that is very different from your workloads in the recent past, we recommend increasing your baseline capacity ahead of the event rather than rely on autoscaling slots to cover the workload capacity. If you encounter an issue with increasing your baseline capacity, retry the request after waiting 15 minutes.
If the reservation doesn't have baseline slots or is not configured to borrow idle slots from other reservations, then BigQuery attempts to scale. Otherwise, baseline slots must be fully utilized before scaling.
Reservations use and add slots in the following priority:
- Baseline slots.
- Idle slot sharing (if enabled). Reservations can only share idle baseline or committed slots from other reservations that were created with the same edition and the same region.
- Autoscale slots.
In the following example, slots scale from a specified baseline amount. The etl
and dashboard
reservations have a baseline size of 700 and 300 slots
respectively.
In this example, the etl
reservation can scale to 1300 slots (700 baseline
slots plus 600 autoscale slots). If the dashboard
reservation is not in use,
the etl
reservation can use the 300 slots from the dashboard
reservation
if no job is running there, leading to a maximum of 1600 possible slots.
The dashboard
reservation can scale to 1100 slots (300 baseline slots plus
800 autoscale slots). If the etl
reservation is totally idle, the dashboard
reservation can scale to a maximum of 1800 slots (300 baseline
slots plus 800 autoscale slots plus 700 idle slots in the etl
reservation).
If the etl
reservation requires more than 700 baseline slots, which are
always available, it attempts to add slots by using the following methods in
order:
- 700 baseline slots.
- Idle slot
sharing with the
300 baseline slots in the
dashboardreservation. Your reservation only shares idle baseline slots with other reservations that are created with the same edition. - Scaling up 600 additional slots to the maximum reservation size.
Using slot commitments
The following example shows slots autoscaling using capacity commitments.
Like reservation baselines, slot commitments allow you to allocate a fixed number of slots that are available to all reservations. Unlike baseline slots, a commitment cannot be reduced during the term. Slot commitments are optional but can save costs if baseline slots are required for long periods of time.
In this example, you are charged a predefined rate for the capacity commitment slots. You are charged at the autoscaling rate for the number of autoscaling slots after autoscaling activates and reservations are in an upscaled state. For autoscaling rate, you are charged for the number of scaled slots, not the number of slots used.
The following example shows reservations when the number of baseline slots exceeds the number of committed slots.
In this example, there is a total of 1000 baseline slots between the two
reservations, 500 from the etl
reservation and 500 from the dashboard
reservation. However, the commitment only covers 800 slots. In this scenario,
the excess slots are charged at the pay as you go (PAYG) rate.
Maximum available slots
You can calculate the maximum number of slots a reservation can use by adding the baseline slots, the maximum number of autoscale slots, and any slots in commitments that were created with the same edition and are not covered by the baseline slots. The example in the previous image is set up as follows:
- A capacity commitment of 1000 annual slots. Those slots are assigned as
baseline slots in the
etlreservation and thedashboardreservation. - 700 baseline slots assigned to the
etlreservation. - 300 baseline slots assigned to the
dashboardreservation. - Autoscale slots of 600 for the
etlreservation. - Autoscale slots of 800 for the
dashboardreservation.
For the etl
reservation, the maximum number of slots possible is equal to
the etl
baseline slots (700) plus the dashboard
baseline slots (300, if
all slots are idle) plus the maximum number of autoscale slots (600). So the
maximum number of slots the etl
reservation could use in this example is
1600. This number exceeds the number in the capacity commitment.
In the following example, the annual commitment exceeds the assigned baseline slots.
In this example, we have:
- A capacity commitment of 1600 annual slots.
- A maximum reservation size of 1500 (including 500 autoscaling slots).
- 1000 baseline slots assigned to the
etlreservation.
The maximum number of slots available to the reservation is equal to the baseline slots (1000) plus any committed idle slots not dedicated to the baseline slots (1600 annual slots - 1000 baseline slots = 600) plus the number of autoscaling slots (500). So the maximum potential slots in this reservation is 2100. The autoscaled slots are additional slots above the capacity commitment.
Autoscaling best practices
-
When first using autoscaler, set the number of autoscaling slots to a meaningful number based on past and expected performance. Once the reservation is created, actively monitor the failure rate, performance, and bill and adjust the number of autoscaling slots as needed.
-
Autoscaler has a 1 minute minimum before scaling down so it is important to set the maximum number of autoscaled slots to balance between performance and cost. If the maximum number of autoscale slots is too large and your job can use all the slots to complete a job in seconds, you still incur costs for the maximum slots for the full minute. If you lower your max slots to half the current amount, your reservation is scaled to a lower number and the job can use more
slot_secondsduring that minute, reducing waste. For help determining your slot requirements, see Monitor job performance . As an alternative approach to determine your slot requirements, see View edition slot recommendations . -
Slot usage can occasionally exceed the sum of your baseline plus scaled slots. You are not billed for slot usage that is greater than your baseline plus scaled slots.
-
Autoscaler is most efficient for heavy, long-running workloads, such as workloads with multiple concurrent queries. Avoid sending queries one at a time, since each query scales the reservation where it will remain scaled for a 1 minute minimum. If you continuously send queries, causing a constant workload, setting a baseline and buying a commitment provides constant capacity at a discounted price.
-
BigQuery autoscaling is subject to capacity availability. BigQuery attempts to meet customer capacity demand based on historical usage. To achieve capacity guarantees, you can set an optional slot baseline, which is the number of guaranteed slots in a reservation. With baselines, slots are immediately available and you pay for them whether you use them or not. To ensure capacity is available for large, inorganic demands, such as high-traffic holidays, contact the BigQuery team several weeks in advance.
-
Baseline slots are always charged. If a capacity commitment expires, you might need to manually adjust the amount of baseline slots in your reservations to avoid any unwanted charges. For example, consider that you have a 1-year commitment with 100 slots and a reservation with 100 baseline slots. The commitment expires and doesn't have a renewal plan. Once the commitment expires, you pay for 100 baseline slots at the pay as you go rate .
Monitor autoscaling
When you monitor slot usage with administrative resource charts , you might see significantly more scaled slots than your slot usage because the charts smooth the number of used slots over the alignment period. To view autoscale slot usage with more precise details, reduce the time frame option. This automatically updates the alignment period to a smaller increment.
In the following example, the chart displays significantly more scaled slots than the workload demands.
However, if you shorten the time frame option so that the alignment period is two seconds, you can see that the autoscaler scales to the workload demand and displays more accurate data. You can adjust the time frame option by dragging the start and end ranges of the time frame option. To display the most accurate workload demand data, select p99from the Metriclist.
For the most accurate view of autoscale usage, use an alignment period between 1 and 15 seconds. For more information about the alignment period of administrative resource charts, see Time frame option .
For information about viewing your slot usage, see View administrative resource charts
Monitor autoscaling with information schema
You can use the following SQL scripts to check the billed slot seconds for a
particular edition. You must run these scripts in the same project the
reservations were created. The first script shows billed slot seconds covered by commitment_plan
while the second script shows billed slot seconds that aren't
covered by a commitment.
You only need to set the value of three variables to run these scripts:
-
start_time -
end_time -
edition_to_check
These scripts are subject to the following caveats:
-
Deleted reservations and capacity commitments are removed from information schema views at the end of the data retention period. Specify a recent window of time which doesn't contain deleted reservations and commitments for correct results.
-
The result of the scripts may not exactly match the bill due to small rounding errors.
The following script checks the slot usage covered by commitments for a particular edition.
Expand to see the script to calculate slot seconds from commitments.
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
The following script checks the slot usage not covered by commitments for a particular edition. This usage contains two types of slots, scaled slots and baseline slots not covered by commitments.
Expand to see the script to calculate slot seconds not covered by commitments
/* 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
Monitor job performance
You may need to adjust your autoscaling max_slots
to avoid
higher costs. The following query provides context on your job performance so
you can choose the correct amount of autoscaling slots for your workload.
The following query provides details on your reservations past job performance:
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
Replace the following:
-
PROJECT_ID: the ID of the project -
REGION_NAME: the region for your project -
START_TIME: the creation time you want to start viewing the data -
END_TIME: the creation time you want to stop viewing the data -
RESERVATION_ID: the reservation ID
The following example gets the job details over a five day period:
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
Quotas
The sum of your maximum reservation size should not exceed your slot quota .
For information about quotas, see Quotas and limits .
What's next
- For more information about BigQuery editions, see Introduction to BigQuery .
- For more information about slots, see Understand slots .
- For more information about reservations, see Introduction to reservations .
