Use query queues
This document provides an overview of query queues in BigQuery.
Query queues let BigQuery automatically determine the number of queries that can run concurrently. When the target is reached, queries are queued until processing resources become available.
You can enable or disable query queues for each reservation or project using the on-demand pricing model. They are disabled by default. To enroll in this preview, complete the enrollment form.
Overview
Without query queues, BigQuery limits the number of concurrently running interactive queries to 100. Queries that exceed this limit return a quota error:
When query queues are enabled, BigQuery dynamically determines the query concurrency based on available compute resources. Optionally, you can specify the target concurrency for a reservation to ensure that each query is allocated some minimum number of slots. Additional queries wait in a queue until there is enough capacity available to begin execution:
Queuing behavior
BigQuery enforces fair scheduling to ensure that no single project can consume all of the slots in 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.
Suppose project A submits a query job to a reservation that has reached its target concurrency and has queries queued. A query in project B could 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.
BigQuery supports running interactive and batch queries. If batch queries make up less than 10% of the concurrency limit, then queries are dequeued in the order in which they were submitted. If batch queries reach 10% of the limit, only interactive queries are dequeued.
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 and 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.
To disable queueing for an individual project, fill out the BigQuery query queues form. After queueing is disabled, queries that exceed the concurrency fail with an error and are not queued.
Set the maximum concurrency target
You can manually set the concurrency target when you create a reservation. If you do not set a target, BigQuery automatically determines concurrency based on the reservation size and currently running queries.
The 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 doesn't guarantee that more queries execute simultaneously. The actual concurrency depends on the available compute resources.
If you set the concurrency to 0, BigQuery dynamically determines the concurrency based on available resources (the default behavior).
bq
To set the 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 projectCONCURRENCY
: the target concurrencyRESERVATION_NAME
: the name of the reservation
API
To set the concurrency target in the
BigQuery Reservation API,
set the concurrency
field in the
reservation resource
and call the
CreateReservationRequest
method.
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 Admin
- BigQuery Resource Admin
- BigQuery Resource Editor
For more information about IAM roles in BigQuery, see Predefined roles and permissions.
Update the maximum concurrency target
You can update the concurrency target for a reservation at any time. Increasing the concurrency target takes effect immediately, provided there are sufficient resources to run additional queries. If you reduce the concurrency target, currently running queries are not impacted and queued queries won't run until the number of conccurent queries falls below the new target.
bq
To update the 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 projectCONCURRENCY
: the target concurrencyRESERVATION_NAME
: the name of the reservation
API
To update the concurrency target in the
BigQuery Reservation API,
set the concurrency
field in the
reservation resource
and call the
UpdateReservationRequest
method.
Required permissions
To update the 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 Admin
- BigQuery Resource Admin
- BigQuery Resource Editor
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
- Within each project, up to 2,000 interactive queries can be queued at one time. Queries that exceed this limit return a quota error.
- By default, query jobs time out after 48 hours if they have not started execution, but users can specify a lower timeout when submitting an individual job.
- You cannot set a concurrency target for on-demand queries. The target is always automatically calculated when query queues are enabled.