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.

You can also limit how much memory that a pool can allocate at runtime to its queries, by setting parameter MAXQUERYMEMORYSIZE 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

Vertica calculates the GENERAL pool's queuing threshold as 95 percent of its MAXMEMORYSIZE setting.

Computing Query Budgets for User-Defined Resource Pools

For user-defined resource pools, Vertica uses the following algorithm:

  1. If MEMORYSIZE is set to 0 and MAXMEMORYSIZE is not set:

    queryBudget = queuingThresholdGeneralPool / PLANNEDCONCURRENCY

  2. If MEMORYSIZE is set to 0 and MAXMEMORYSIZE is set to a non-default value:

    query-budget = queuingThreshold / PLANNEDCONCURRENCY

    Vertica calculates a user-defined pool's queuing threshold as 95 percent of its MAXMEMORYSIZE setting.

  3. If MEMORYSIZE is 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.

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.