Query budgeting
Before it can execute a query, Vertica devises a query plan, which it sends to each node that will participate in executing the query. The Resource Manager evaluates the plan on each node and estimates how much memory and concurrency the node needs to execute its part of the query. This is the query budget, which Vertica stores in the query_budget_kb column of system table
V_MONITOR.RESOURCE_POOL_STATUS.
A query budget is based on several parameter settings of the resource pool where the query will execute:
-
MEMORYSIZE -
MAXMEMORYSIZE -
PLANNEDCONCURRENCY
You can modify MAXMEMORYSIZE and PLANNEDCONCURRENCY for the GENERAL resource pool with
ALTER RESOURCE POOL. This resource pool typically executes queries that are not assigned to a user-defined resource pool. You can set all three parameters for any user-defined resource pool when you create it with
CREATE RESOURCE POOL, or later with
ALTER RESOURCE POOL.
Important
You can also limit how much memory that a pool can allocate at runtime to its queries, by setting parameterMAXQUERYMEMORYSIZE on that pool. For more information, see
CREATE RESOURCE POOL.
Computing the GENERAL pool query budget
Vertica calculates query budgets in the GENERAL pool with the following formula:
queryBudget = queuingThresholdPool / PLANNEDCONCURRENCY
Note
Vertica calculates the GENERAL pool's queuing threshold as 95 percent of itsMAXMEMORYSIZE setting.
Computing query budgets for user-defined resource pools
For user-defined resource pools, Vertica uses the following algorithm:
-
If
MEMORYSIZEis set to 0 andMAXMEMORYSIZEis not set:queryBudget = queuingThresholdGeneralPool / PLANNEDCONCURRENCY -
If
MEMORYSIZEis set to 0 andMAXMEMORYSIZEis set to a non-default value:query-budget = queuingThreshold / PLANNEDCONCURRENCYNote
Vertica calculates a user-defined pool's queuing threshold as 95 percent of itsMAXMEMORYSIZEsetting. -
If
MEMORYSIZEis set to a non-default value:queryBudget = MEMORYSIZE / PLANNEDCONCURRENCY
By carefully tuning a resource pool's MEMORYSIZE and PLANNEDCONCURRENCY parameters, you can control how much memory can be budgeted for queries.
Caution
Query budgets do not typically require tuning, However, if you reduce the MAXMEMORYSIZE because you need memory for other purposes, be aware that doing so also reduces the query budget. Reducing the query budget negatively impacts the query performance, particularly if the queries are complex.
To maintain the original query budget for the resource pool, be sure to reduce parameters MAXMEMORYSIZE and PLANNEDCONCURRENCY together.
See also
Do You Need to Put Your Query on a Budget? in the Vertica User Community.