Understanding Google BigQuery Compute Slots
Bigquery uses Compute Slots to process your SQL Query and return the results. In this tutorial we will go through Bigquery slots in detail
What is a BigQuery slot ?
A BigQuery slot is a virtual CPU used by BigQuery to execute SQL queries. BigQuery automatically calculates how many slots are required by each query, depending on query size and complexity.
There are two pricing models in Bigquery on-demand pricing and flat-rate pricing and the slot allocation differs in both. Lets take a detailed look
Bigquery slot allocation for on-demand customers
Customers with On-demand pricing model (pay as you go) can get up to 2000 slots for processing their queries. The slots are allocated on demand (only when there is a need) and automatically de-allocated once the job is done. The customers only pay for the active usage milliseconds. One caveat here is the 2000 slots is not guaranteed. If the compute resouces are available in the shared pool, google will allocate you, otherwise the individual work units of the query will be queued and will be picked up when the compute resources becomes available
Bigquery slot allocation for flat-rate customers
Customers with flat-rate pricing model will get guaranteed slots for processing their queries. You can explicitly choose how many slots to reserve (Starts from 500 slots). In this model, the slots are always running regardless of you use it or not. For example, if you purchase 2,000 BigQuery slots, your queries in aggregate are limited to using 2,000 virtual CPUs at any given time. You will have this capacity until you delete it, and you will pay for 2,000 slots until you delete them
Query execution using compute slots
Once you submit the query, Bigquery engine will parse the query to create a parse tree. It splits the execution into multiple stages. It will then determine the maximum level of parallelism that can done on this query job during each stage.
BigQuery slots execute individual units of work at each stage of the query. For example, if BigQuery determines that a stage's optimal parallelization factor is 10, it requests 10 slots to process that stage
If a query requests more slots than currently available, BigQuery queues up individual units of work and waits for slots to become available. As forward progress on query execution is made, and as slots free up, these queued up units of work get dynamically picked up for execution.
- A query stage requests 1,000 slots, but only 500 are available.
- BigQuery consumes all 500 available slots and queues up work for the other 500 slots.
- Thereafter, if 100 slots finish their work, they dynamically pick up 100 units of work from the 500 queued up units of work. 400 units of queued up work will remain.
- Thereafter, if 400 slots finish their work, they dynamically pick up 400 units of work and finish the job
BigQuery leverages fair scheduling to allocate resources among competing queries. This means that every query has equal access to all available slots at any time, and capacity is dynamically and automatically re-allocated among active queries as each query's capacity demands change
What are the specifications of a BigQuery compute slot
At this time, Google does not disclose the specifications a Compute Slot (Including Memory, CPU etc.). Google simply refers to it as a virtual VM that can do a unit of work.
Interview Tip: Why do we call BigQuery serverless if it uses compute slots ?
Although Bigquery uses compute slots to fulfill a query, For the end user there is no need to provision and maintain any compute slots, its all happens automatically, so it is in a abstract server less to the end user