Target Memory Determination for Queries in Concurrent Environments

The resource pool parameters MEMORYSIZE, MAXMEMORYSIZE, and PLANNEDCONCURRENCY allow you to tune the target memory allocated to queries.

What is the Query Budget?

Each resource pool has a query budget, which is the target memory for queries executed on the associated pool. Vertica stores this value in the query_budget_kb column in the V_MONITOR.RESOURCE_POOL_STATUS system table.

Computing the Query Budget

The formula for computing a pool's query budget is as follows:

GENERAL pool:

• Query budget = Queuing threshold of the GENERAL pool / PLANNEDCONCURRENCY

If MEMORYSIZE is set to 0 and MAXMEMORYSIZE is NOT set:

• Query budget = Queuing threshold of the GENERAL pool / PLANNEDCONCURRENCY

If MEMORYSIZE is set to 0 and MAXMEMORYSIZE is set to a value other than the default:

• Query budget = Queuing threshold of the pool / PLANNEDCONCURRENCY

If MEMORYSIZE is set to a value other than the default:

• Query budget = MEMORYSIZE / PLANNEDCONCURRENCY of the pool

The consequence of this is by carefully tuning the MEMORYSIZE and PLANNEDCONCURRENCY parameters, it is possible to restrict the amount of memory used by a query to a desired size.

For a detailed example of query budget calculations, see Do You Need to Put Your Query on a Budget? in the Vertica User Community.

Tuning the Query Budget

Normally, query budgets do not require any specific tuning, However, if you reduce the MAXMEMORYSIZE because you need memory for other purposes, be aware that you are also reducing 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, if you reduce MAXMEMORYSIZE, be sure to also reduce the value of PLANNEDCONCURRENCY.

Parameter Values

The RESOURCE_POOL_STATUS system table contains the values for parameters for all Vertica resource pools.