Use query queues
This document provides an overview of query queues in BigQuery.
Enable query queues
You can enable or disable query queues for either a project using the on-demand pricing model (an on-demand project), or an entire reservation. Query queues are disabled by default. To enroll in this preview, complete the enrollment form.
When you enable query queues for an on-demand project or reservation, BigQuery automatically determines the number of queries that can run concurrently. When the maximum concurrency target is reached, queries are queued until processing resources become available.
Overview
Without query queues, BigQuery limits the number of concurrently running interactive queries to 100. Queries that exceed this limit return a quota error. The following example shows this behavior:
When query queues are enabled, BigQuery dynamically determines the query concurrency based on available compute resources. The number of concurrently running queries is computed per on-demand project or per reservation. Additional queries wait in a queue until there is enough capacity available to begin execution. For an on-demand project, the queue length is limited to 1,000 interactive queries and 20,000 batch queries. For a reservation, the queue allows up to 1,000 interactive queries and 20,000 batch queries per project assigned to the reservation. The following example shows the behavior for an on-demand project when the computed query concurrency is 202:
For reservations, you have the option to set the maximum concurrency target to ensure that each query is allocated some minimum number of slots. You can't specify a maximum concurrency target for an on-demand project; it is always dynamically computed.
Queuing behavior
Whether or not query queues are enabled, BigQuery enforces fair scheduling to ensure that no single project can consume all of the slots in a reservation.
When query queues are enabled for a reservation, projects that have the smallest share of concurrency are dequeued first. During execution, slots are distributed fairly among projects before being distributed across jobs within a project.
For example, suppose you have a reservation that contains two projects: A and B. BigQuery computes 5 for the concurrency of the reservation. Project A has four currently running queries, project B has one running query, and other queries are queued. A query from project B would be dequeued first even if it was submitted after the query from project A. After a query begins execution, it receives a fair share of slots in the shared reservation.
When query queues are enabled for an on-demand project, query jobs are run in the order in which they are submitted.
Prioritization of interactive and batch queries
BigQuery supports running interactive and batch queries. BigQuery limits the portion of concurrently running batch queries to 30% of the maximum concurrency target. After batch queries reach 30% of the limit, only interactive queries are dequeued.
Changing or deleting a reservation with queued queries
When a reservation is deleted, all running and queued queries in the reservation are canceled. When a project assigned to a reservation gets reassigned to another reservation, all requests currently queued or running continue to do so in the old reservation, while all new requests go to the new reservation. Similarly, when a project assigned to a reservation is removed from the reservation, new requests execute using the on-demand model.
Set the maximum concurrency target
You can manually set the maximum concurrency target when you create a reservation. The maximum concurrency target takes effect once you enable query queues for that reservation. Until then, it has no effect.
By default, the maximum concurrency target is zero, which means that BigQuery dynamically determines the concurrency based on available resources. Otherwise, if you set a nonzero target, the maximum concurrency target specifies an upper bound on the number of queries that run concurrently in a reservation, which guarantees a minimum amount of slot capacity available for each query that runs.
Increasing the maximum concurrency target doesn't guarantee that more queries execute simultaneously. The actual concurrency depends on the available compute resources.
Required permissions
To set the concurrency in a new reservation, you need the following Identity and Access Management (IAM) permission:
bigquery.reservations.create
on the administration project that maintains ownership of the commitments
Each of the following predefined IAM roles includes this permission:
- BigQuery Resource Editor
- BigQuery Resource Admin
- BigQuery Admin
For more information about IAM roles in BigQuery, see Predefined roles and permissions.
Set the maximum concurrency target for a reservation
bq
To set the maximum concurrency target for a new reservation, run the
bq mk
command:
bq mk \ --project_id=ADMIN_PROJECT_ID \ --location=LOCATION \ --concurrency=CONCURRENCY \ --reservation \ RESERVATION_NAME
Replace the following:
ADMIN_PROJECT_ID
: the project that owns the reservationLOCATION
: the location of the reservationCONCURRENCY
: the maximum concurrency targetRESERVATION_NAME
: the name of the reservation
API
To set the maximum concurrency target in the
BigQuery Reservation API,
set the concurrency
field in the
reservation resource
and call the
CreateReservationRequest
method.
Update the maximum concurrency target
You can update the maximum concurrency target for a reservation at any time. If query queues are not enabled for the reservation, then changing the value has no effect.
If query queues are enabled, then increasing the maximum concurrency target takes effect immediately. However, this doesn't guarantee that more queries execute simultaneously. The actual concurrency depends on the available compute resources. If you reduce the maximum concurrency target, then currently running queries are not impacted and queued queries won't run until the number of concurrent queries falls below the new target.
If you set the maximum concurrency target to 0, BigQuery dynamically determines the concurrency based on available resources (the default behavior).
Required permissions
To update the maximum concurrency target for a reservation, you need the following Identity and Access Management (IAM) permission:
bigquery.reservations.update
on the administration project that maintains ownership of the commitments
Each of the following predefined IAM roles includes this permission:
- BigQuery Resource Editor
- BigQuery Resource Admin
- BigQuery Admin
For more information about IAM roles in BigQuery, see Predefined roles and permissions.
Update the maximum concurrency target for a reservation
bq
To update the maximum concurrency target for an existing reservation, run the
bq update
command:
bq update \ --project_id=ADMIN_PROJECT_ID \ --location=LOCATION \ --concurrency=CONCURRENCY \ --reservation \ RESERVATION_NAME
Replace the following:
ADMIN_PROJECT_ID
: the project that owns the reservationLOCATION
: the location of the reservationCONCURRENCY
: the maximum concurrency targetRESERVATION_NAME
: the name of the reservation
API
To update the maximum concurrency target in the
BigQuery Reservation API,
set the concurrency
field in the
reservation resource
and call the
UpdateReservationRequest
method.
Monitoring
To find out which queries are running and which are queued, look at the
INFORMATION_SCHEMA.JOBS_BY_*
and
INFORMATION_SCHEMA.JOBS_TIMELINE_BY_*
views. The state
field is set to RUNNING
for actively running queries and to
PENDING
for queued queries.
You can monitor the query queue length for your reservation by using BigQuery Admin Resource Charts and selecting the Job Concurrency chart. You can also monitor the queue length in Cloud Monitoring by viewing the job count metric and checking the number of jobs in a pending state.
Limitations
- Each on-demand project can queue up to 1,000 interactive queries and 20,000 batch queries at one time. Queries that exceed this limit return a quota error. You cannot request an increase in these limits.
- Within a reservation, each project assigned to that reservation can queue up to 1,000 interactive queries and 20,000 batch queries at one time. Queries that exceed this limit return a quota error. You cannot request an increase in these limits.
- By default, query jobs that haven't started execution time out after 6 hours for interactive queries and 24 hours for batch queries.
- You cannot set the maximum concurrency target for queries running in an on-demand project. BigQuery computes the maximum concurrency target dynamically when query queues are enabled outside of a reservation.
Disable query queues
To disable query queues for an individual on-demand project or a reservation, fill out the BigQuery query queues form. You cannot selectively enable or disable query queues for a project assigned to a reservation. After query queues are disabled, queries that exceed the concurrency fail with an error and are not queued.
What's next
- Learn more about diagnosing and resolving concurrent queries quota errors.