Best Practices for Managing Resource Pools

This document is intended for Vertica administrators and provides several examples about creating custom resource pools to handle well-defined and ad-hoc workloads.

It is likely that your Vertica environment will require running a variety of multiple queries at the same time. When running more than one query on the system, the queries share resources. Therefore, each query can take longer to run than it would if it were running by itself. The resource manager helps you prioritize resource usage and handles resources using resource pools. A resource pool is a pre-allocated subset of the system resources, with an associated queue. This document details some suggested use cases to assist you with creating resource pools for your database.

Creating Custom Resource Pools

When you create custom resource pools, there are many parameters you can consider setting. For more information about other parameters, see ALTER RESOURCE POOL in the Vertica documentation. This document focuses on the following five parameters:

• MEMORYSIZE: The initial memory assigned to the pool. Setting this parameter for a resource pool helps block memory that is available for queries running against the pool. However, if the memory is not used, it remains blocked, and is not available for queries from other pools.
• MAXMEMORYSIZE: The cap on the total memory acquired by queries running against the pool. In most cases, the resource pool may not always acquire the share of memory it is entitled to and therefore, it makes sense to set MAXMEMORYSIZE higher than what pool is entitled to.
• MAXCONCURRENCY: The maximum number of concurrent queries that can run against the pool.
• PLANNEDCONCURRENCY: An estimate of the number of concurrent queries that may run against the pool. This parameter is used to calculate the query budget for a resource pool. The query budget is the initial memory assigned to a query. Depending on the complexity of the query and the value of the query budget, Vertica assigns parallel threads per operator that impact query performance. For more information, see this blog post.
• EXECUTIONPARALLELISM: Limits the number of threads used to process any single query issued in the resource pool.

Understanding Workloads

You may categorize workloads based on the query type (ETL v/s SELECT), applications, user groups and query complexity. For more details, see the Appendix at the end of this document, which has two view definitions that further explain workloads.

Example: Create and Assign Resource Pools

This example takes a look at two different organizations:

• fastAnalytics, an organization with well-defined workloads
• quickAnalytics, an organization with ad-hoc workloads

We picked these two common examples to show how to create custom resource pools for each type of workload.

The organization fastAnalytics is running ETL jobs 24x7 and has to meet predefined SLA's for loading data. They have a 10 node cluster, and each node has 256GB of memory and 48 cores. As a first step, they split resources between ETL and SELECT queries by creating two user resource pools named etl_pool and select_pool for ETL and SELECT queries, respectively. The following table shows resource pool definitions for the two new pools:

The general pool is the default pool for users without predefined resource pools. If you do not have predefined pools, your queries are probably running against the general pool. In this example, the default query budget for the general pool was 5GB and users without the predefined pool consumed a lot more memory. Database administrators should identify these users and assign them resource pools. You can also set the PLANNEDCONCURRENCY of the general pool to 120 so that queries running against the general pool have a query budget of 2GB, which uses less memory.

The table above shows only user-created resource pools. We made assumptions that the system resource pools, including the metadata resource pool have a reserved memory of 10GB on each node. The etl_pool and the select _pool each have a query budget of 3GB (MAXMEMORYSIZE/PLANNEDCONCURRENCY). The advantage of creating user pools is that the SELECT queries cannot consume more than 60% of the memory, which leaves about 40% of the memory for ETL queries. You want to set MAXMEMORYSIZE for pools such that no pool or set of pools can starve other pools. We typically recommend that the sum of the MAXMEMORYSIZE of user-defined pools does not exceed 20% above the available physical memory.

After further analysis of the workload using the script in the Appendix, the database administrator at fastAnalytics identified three categories of users:

• Dashboard application users
• Data analysts
• Users running complex batch data processing jobs

Dashboard application users were running short queries that ran in sub-seconds, data analysts were running medium-sized queries that ran between a few seconds to less than a minute, and users doing batch processing jobs were issuing complex batch queries that ran for more than a minute.

After this analysis, the next step was to replace the select_pool with three resource pools that aligned with the short, medium, and complex queries assign them to the specific user types:

• short_pool for dashboard application users
• medium_pool for data analysts
• large_pool for users doing batch jobs

The following table shows resource pool definitions after the three new pools were created to replace the select_pool:

This table shows we set the EXECUTIONPARALLELISM for different pools because the default value is AUTO. This value is the same as the number of cores on the host. For fastAnalytics, 48 was too high for concurrent workloads.

We set MAXMEMORYSIZE and PLANNEDCONCURRENCY for each of the user-defined resource pools to put limits on memory consumption for each users' pools, and set a query budget of 2GB, 4GB, and 8GB for each pool based on size. The etl_pool was set for a query budget of 3GB and an EXECUTIONPARALLELISM value of 4 since we did not have live aggregate projections or flattened tables. If you do have live aggregate projections or flattened tables, set the EXECUTIONPARALLELISM value higher, such as 16.

A few months after these initial changes were made, the database administrator at fastAnalytics did another round of workload analysis on these queries. The administrator found that some of the bulk processing ETL jobs loading data into wide tables were consuming more memory and running for a longer period of time. Their team decided to split ETL jobs into two pipelines: one for trickle load and one for bulk loading into wide tables. This meant the etl_pool would be replaced by two different pools for each workload, the trickle_pool, and the bulk_pool. With these separate pools, the long running ETL jobs would not block trickle jobs.

At fastAnalytics, the senior management was using a reporting application. The database administrator saw a need for a standalone resource pool that could be assigned to the senior management so their queries never had to wait in the queue. A standalone resource pool is configured so it cannot borrow memory from the general pool. A standalone has MEMORYSIZE equal to the MAXMEMORYSIZE.

The following table shows resource pool definitions after the two new pools were created to replace the etl_pool and the new management pool was created:

In the table above, we set the MEMORYSIZE and the MAXMEMORYSIZE to 6GB for the management_pool to ensure that pool always has sufficient memory for its queries. However, the team at fastAnalytics should check this pool's usage periodically to ensure excess memory is not blocked by this standalone pool.

The trickle_pool and the bulk_pool that replaced the etl_pool have query budgets of 2.5GB and 6GB, respectively. The bulk_pool is used for loading wide tables (those with more than 250 columns). You can improve load performance for wide tables with higher query budgets.

While fastAnalytics had well-defined workloads, some organizations do not. For example, the quickAnalytics organization had workloads that were completely ad-hoc. The database administrators at quickAnalytics decided to use the Vertica cascading resource pool feature to handle ad-hoc workloads.

Cascading Resource Pools

When creating a custom resource pools, you can use the CASCADE TO option to specify a secondary resource pool for executing queries that exceed the RUNTIMECAP setting of their assigned resource pool.

When using cascading resource pools, queries may hit the following:

• Secondary resource pools do not have free memory available to move the query. In this situation, the query will be terminated, re-planned against the secondary pool and queued for resources on the secondary resource pool.
• The query being moved has acquired memory greater than the MAXMEMORYSIZE of the secondary pool. In this situation, the execution of the query is terminated and re-planned against the secondary pool.
• Running the query count on the secondary pool is equal to MAXCONCURRENCY. In this situation, the query will not move to the secondary resource pool and continues to run in the primary pool even after the RUNTIMECAP is exceeded. The data collector table DC_RESOURE_POOL_MOVE will have an error message such as "Target pool does not have sufficient resources”.

The database users at quickAnalytics ran ad-hoc queries that were either small, medium, or large. Next, we'll discuss two different modes you can use to implement cascading resource pools.

Resource Pool Set Up Mode A

In setup mode A, you have three different resource pools with the exact same query budget, but different runtime caps. In this scenario, when a query cascades from one pool to another, the execution of the query is not interrupted, but memory is accounted against a new pool. This setup works best for workloads where most queries are short. The advantage is that queries do not re-plan during a cascade, so the execution cycles are not wasted. However, rare complex queries against a pool with a smaller budget may run much longer than expected. For more information, see this blog post.

Suppose we decide to create slow, medium, and fast pools with identical query budgets and different runtime caps:

• A fast pool with a RUNTIMECAP of 5 seconds. Any queries that exceed this cap cascade to the medium pool.
• A medium pool with a RUNTIMECAP of 1 minute. Any queries that exceed this cap cascade to the slow pool.
• A slow pool with a RUNTIMECAP of 5 minutes. Any queries that exceed this cap are terminated.

In the table above, the fast, medium, and slow pools have a query budget of 1GB, 4GB, and 8GB, respectively. The PLANNEDCONCURRENCY is set to help set these query budgets. Since all queries get planned against the fast resource pool and are not re-planed after the cascade, they run with a query budget of 1GB. Medium and slow pools have a higher budget so that users assigned to these pools can take advantage.

The MAXCONCURRENCY is set on the resource pools to limit the number of concurrent queries against the pool.

Resource Pool Set Up Mode B

In setup mode B, you have three different resource pools with very different query budgets, maximum concurrency, and runtime caps. In this setup, when a query cascades from one pool to another, the execution of the query is terminated and the query is re-planned against a new pool, with a higher budget. This setup works best in cases where the same users are submitting mixed workloads with simple, medium, and complex queries. By setting runtime caps on resource pools to small values, we minimize the execution cycles that are lost due to resource pool moves. Queries get re-planned on pools with more appropriate budgets and run faster to make up for the time lost in re-planning.

Database users at quickAnalytics ran ad-hoc queries that were either small, medium, or large. The administrators created fast, medium, and slow pools with a query budget of 1GB, 2GB, and 5GB, respectively. They set the runtime caps on the pools as follows:

• A fast pool with a RUNTIMECAP of 3 seconds. Any queries that exceed this cap cascade to the medium pool.
• A medium pool with a RUNTIMECAP of 15 seconds. Any queries that exceed this cap cascade to the slow pool.
• A slow pool with a RUNTIMECAP of 1 minute. Any queries that exceed this cap are terminated.

Every user running a SELECT query was assigned to the fast pool. The configuration parameter CascadeResourcePoolAlwaysReplan must be set to 1. When the value is 1, the query will be re-planned when the query is moved to a new resource pool. Users must also have usage privileges on all three resource pools.

Note: The CascadeResourcePoolAlwaysReplan parameter is not listed in the Vertica documentation.

The table above shows the fast, medium, and slow pools have a query budget of 1GB, 2GB, and 5GB, respectively. All queries get re-planned after the cascade so they run with different query budgets after the cascade. We should monitor data collector table DC_RESOURCE_POOL_MOVE to see the percentage of queries cascading to new pools. Vertica administrators should adjust runtime caps on resource pools to keep the percentage of queries moving to new pools to less than 10%.

Resource Manager Analysis

As a resource pool designer we need to do the following:

• Ensure queries are spending no or minimal time waiting in resource queues for resources. You can use the following queries to identify the current state of the resource queue and the time individual historical queries spent in the queue:

=> SELECT* FROM resource_acquisitions;

• Fine tune resource pool budgets to improve query performance. The following query shows the query budget for each pool, along with other important resource pool parameters:

=> SELECT pool_name, memory_size_kb, max_memory_size_kb, planned_concurrency, max_concurrency, 
query_budget_kb FROM resource_pool_status WHERE node_name ilike '%0001%' ORDER BY query_budget_kb desc;

• Identify occasional rogue queries that consume a lot of memory or run for an abnormal amount of time, this impacting other concurrent queries. The following example shows transactions that consume more than 25GB:

=> SELECT * FROM user_workload WHERE mem_kb > '25*1024^2' ORDER BY query_duration_us desc;

You can also monitor resource pools using Management Console. For more information, see the Vertica documentation.

Appendix

For analyzing workloads, use the following two views to create and run queries against as the database administrator.

The USER_WORKLOAD view joins data collector tables dc_resource_acquisitions, dc_requests_issued & dc_requests_completed tables to get the query type, resource pool used, memory used by the query, and the query duration in microseconds for queries executed by database users. This view also has transaction and statement id's that can be used to identify queries consuming large amount of memory and the user issuing that query.

The USER_WORKLOAD_BY_HOUR is a view on top of the USER_WORKLOAD view that is aggregated hourly. It has a number of short, medium, and fast queries run by a user along with the average duration, min,max and average memory in kilobytes aggregated hourly per user. We have made assumptions that an execution time of less than 10 seconds is short, more than 5 minutes is long and between 10 seconds to 5 minutes is medium.

=> CREATE OR REPLACE VIEW USER_WORKLOAD 
AS
SELECT ri.user_name,
ri.time,
ri.request_type,
ra.pool_name,
ra.transaction_id,
ra.statement_id,
(memory_kb) mem_kb,

extract(epoch from rc.time - ri.time) * 1e6 as query_duration_us
FROM
(SELECT transaction_id,
statement_id,
pool_name,
max(memory_kb) memory_kb
FROM dc_resource_acquisitions
WHERE request_type!='Acquire' and pool_name <> 'sysquery'
GROUP BY 1,2,3)ra
JOIN dc_requests_issued AS ri ON ra.transaction_id=ri.transaction_id
AND ra.statement_id=ri.statement_id
JOIN dc_requests_completed as rc on rc.session_id=ri.session_id
AND rc.request_id=ri.request_id
AND rc.node_name=ri.node_name;

=> CREATE OR REPLACE VIEW USER_WORKLOAD_BY_HOUR
AS
SELECT
TIME::varchar(13),
user_name,
pool_name,
CASE
WHEN avg(query_duration_us) > 300000000 THEN 'LONG_QUERY'
WHEN avg(query_duration_us) < 10000000 THEN 'SHORT_QUERY'
ELSE 'MEDIUM_QUERY'
END AS query_size,
count(*) query_count,
avg(query_duration_us)::int avg_qd_us,
min(mem_kb)::int min_mem_kb,
avg(mem_kb)::int avg_mem_kb,
max(mem_kb)::int max_mem_kb
FROM USER_WORKLOAD
GROUP BY 1,2,3
ORDER BY 1,2,3,4;