Do You Need to Put Your Query on a Budget?

Before we scare you away with the word “budget,” rest assured that after reading this blog, you won’t have to give up your favorite activities or sell your car. What you will be able to do is understand how Vertica resource pool parameters affect query budget.

An insufficient query budget could be slowing your queries down. But fear not.

Like a balance book (yes, they still exist), Vertica provides you with a way to review and alter resource budgets for your queries.

First, what is a query budget?

Vertica Query Budget

When you execute a query in Vertica, the initiator node determines where Vertica stores the data, which projections are available, available CPU bandwidth, and other factors. From this information, the initiator node creates a query plan and sends it to each node that will help execute the query.

 

Each node then estimates the amount of memory and concurrency it needs to execute its part of the query. This is the query budget.

Normally, the calculated query budget provides the best possible performance for your queries. However, much like ignoring your balance book, neglecting your resource pool parameters can negatively affect query performance.

Query Budget Factors

Vertica estimates the query budget based on the following parameters associated with the resource pool where the query will execute:

• MEMORYSIZE
• MAXMEMORYSIZE
• PLANNEDCONCURRENCY

In addition, each resource pool has a queuing threshold. For each resource pool:

• Queueing threshold = 95% of the pool’s MAXMEMORYSIZE

When the amount of memory used by all requests against a resource pool exceeds the queuing threshold, new requests against the pool are queued until memory becomes available. Think of this process as a “pending” account where charges aren’t processed until more money is available in your account. (Wouldn’t that be nice?)

If you don’t specify a MAXMEMORYSIZE for a resource pool, the MAXMEMORYSIZE will equal the pool’s MEMORYSIZE plus the GENERAL pool’s MAXMEMORYSIZE. Thus, if the MEMORYSIZE for your pool isn’t set (0 is the default), the MAXMEMORYSIZE is simply the GENERAL pool’s MAXMEMORYSIZE.

You can view all these values in the RESOURCE_POOL_STATUS system table.

Query Budget Calculation

Using the pool parameters listed above, Vertica calculates each resource pool’s query budget in the following ways:

GENERAL pool:

• Query budget = Queueing threshold of the GENERAL pool / PLANNEDCONCURRENCY of the GENERAL pool.

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

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

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

• Query budget = Queueing threshold of the pool/ PLANNEDCONCURRENCY of the pool.

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

• Query budget = MEMORYSIZE of the pool/ PLANNEDCONCURRENCY of the pool

The following graphic shows examples of the above calculations:

Tuning the Query Budget

By tuning the MEMORYSIZE and PLANNEDCONCURRENCY parameters, you can restrict the amount of memory used by a query. In other words, you can set the query budget.

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, when you reduce MAXMEMORYSIZE, be sure to also reduce the value of PLANNEDCONCURRENCY.

Using a portion of the RECORD 1 example show above for the GENERAL pool, let’s see how this might work.

 

Suppose you reduce MAXMEMORYSIZE by a third. The new query budget is reduced by the same amount:

 

To return to the original query budget so that your queries run as usual, reduce PLANNEDCONCURRENCY to 3. The query budget is now closer to its original value of 708420.

Conclusion

So, that’s the lowdown on how your resource pool parameters can affect your query budget. Adjust them with care. Most importantly, if you have to lower the MAXMEMORYSIZE of a resource pool, adjust the PLANNEDCONCURRENCY so that you don’t inadvertently affect query performance by reducing the size of the query budget.

Learn More

See Resource Pool Architecture in the Vertica documentation.