BigQuery Query Queues — Query concurrency in your control
Just before I start, many of us address BigQuery as BQ, Just realized for this (BigQuery Query Queues) it will be BQQQ
Previously BigQuery supported a fixed concurrency limit of 100 queries per project. When the number of queries exceeded this limit, users received a quota exceeded error when attempting to submit an interactive job. This could be frustrating if you are running huge workloads and often receiving this message.
Well, not anymore with this feature called BigQuery Query Queues
This feature is enabled for all BigQuery customers by default.
What are query queues and why use them?
BigQuery query queues introduce a dynamic concurrency limit and enable queueing. With this, concurrency is determined in real time, taking into account both the current slot capacity and the number of ongoing queries. While the majority of users will opt for the dynamic concurrency calculation, administrators have the option to establish a maximum concurrency goal for a reservation. This allows them to guarantee that each query is allocated sufficient slot capacity for execution. Additionally, this approach involves the queuing of queries that cannot be immediately processed. These queued queries are executed once the necessary resources become available, preventing them from failing.
For reservations, you have the option to set the maximum concurrency target, an upper bound on the number of queries that can run concurrently in a reservation, 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.
Using query queues
- Dynamic concurrency: BigQuery dynamically determines the concurrency based on available resources and can automatically set and manage the concurrency based on reservation size and usage patterns. While the default concurrency configuration is set to zero, which enables dynamic configuration, experienced administrators can manually override this option by specifying a target concurrency limit. The admin-specified limit can’t exceed the maximum concurrency provided by available slots. The limit is not configurable by administrators for on-demand workloads.
- Queuing: Query queues help to manage scenarios where peak workloads generate a sudden increase in queries that exceed the maximum concurrency limit. With queuing enabled, BigQuery can queue up to 1,000 interactive queries and 20,000 batch queries, ensuring that they are scheduled for execution rather than being terminated due to concurrency limits, as was previously the case. Users no longer need to search for idle times or periods of low usage to optimize when to submit their workload requests. BigQuery automatically runs their requests or schedules them in a queue to run as soon as the current running workloads have finished.
Queuing behavior
BigQuery uses a fair scheduling system to ensure that one project doesn’t hog all the computing resources within a reservation.
- Dequeuing Behavior: When multiple projects are using a reservation, queries from projects with the least number of concurrent queries get priority. So, even if a query from Project B was submitted after one from Project A, it would be processed first if Project B has fewer running queries.
- Fair Slot Distribution: While a query is running, the available slots in the shared reservation are distributed fairly among all the projects before being further distributed among the queries within a project.
Here’s an example, imagine a reservation with 5 concurrency slots assigned to Projects A and B. If Project A has four queries running concurrently and Project B has only one, a new query from Project B will be processed ahead of a new query from Project A. After a query starts, it gets a fair share of the available slots.
- On-Demand Projects: Queries in on-demand projects are processed in the order they are submitted without any concurrency management.
- Dynamic Batch Query Limit: BigQuery determines the maximum number of batch queries a project can run concurrently based on the workload.
- Reservation Changes: When you delete a reservation, all queued queries are canceled. If a project is moved to a different reservation, existing queries continue in the old reservation, while new ones go to the new reservation. If a project is removed from a reservation, running queries stay in the reservation, and new and queued queries run using the on-demand model. You can also cancel individual running or queued queries if needed.
Highlights and metrics
- Target job concurrency: Setting a lower target_job_concurrency for a reservation increases the minimum number of slots allocated per query, which potentially results in faster or more consistent performance, particularly for complex queries. Changes to concurrency are only supported at the reservation level.
- Specs: Within each project, up to 1,000 interactive queries can be queued at once, and 20,000 for batch queries. Batch queries use the same resources as interactive queries.
- Timeouts: Users can now configure a timeout value for each query/job queue. If a query can’t start executing within the specified time, BigQuery will attempt to cancel the query/job instead of queuing it for an extended amount of time. The default timeout value is 6 hours for interactive, 24 hours for batch, and can be set at the organization or project level.
Adjusting queue timeouts
To control the queue timeout for interactive or batch queries, use the ALTER PROJECT SET OPTIONS statement or the ALTER ORGANIZATION SET OPTIONS statement to set the default_interactive_query_queue_timeout_ms or default_batch_query_queue_timeout_ms fields in your project's or organization's default configuration.
To view the queue timeout for interactive or batch queries in your project, query the INFORMATION_SCHEMA.EFFECTIVE_PROJECT_OPTIONS view.
To turn off queuing, set the queue timeout to -1. If you reach your maximum query concurrency, then additional queries fail with an ADMISSION_DENIED error.
Set the maximum concurrency target
You can manually set the maximum concurrency target when you create a reservation. 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 computing resources, which can be increased by adding more slots to your reservation.
Let me know your thoughts on this one.
