Use query queues

Preview

This product or feature is covered by the Pre-GA Offerings Terms of the Google Cloud Terms of Service. Pre-GA products and features might have limited support, and changes to pre-GA products and features might not be compatible with other pre-GA versions. For more information, see the launch stage descriptions.

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 reservation
• LOCATION: the location of the project
• CONCURRENCY: the target concurrency
• RESERVATION_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 reservation
• LOCATION: the location of the project
• CONCURRENCY: the target concurrency
• RESERVATION_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.