The Denodo Resource Manager provides a robust mechanism for managing system resources and optimizing query performance. When configuring the Resource Manager for production environments, it is important to follow best practices that align with your operational needs. This document outlines key recommendations for setting up Resource Manager in production environments, along with a specific use case addressing a common challenge where standard functionality was insufficient.

Adapt Server Resources and Execution Time to Each Use Case

Resource Manager Plans allow server administrators to configure limits on various resources to optimize query execution and manage server load effectively. These configuration options include limiting memory, query execution time, and concurrent queries. Admins should adapt these resources based on the type of workload they are handling.

Operational Use Case

• Description: In environments where most queries are small and require quick responses, administrators should focus on restricting memory, execution time, and allowing a high number of concurrent requests.
• Recommended Plans:

• Memory Limits: Restrict the maximum memory allowed per query to a small value to avoid excessive resource consumption.
• Execution Time: Set a short maximum execution time (e.g., 1 minute) for all queries in these scenarios.
• Concurrency: Allow a higher number of concurrent requests to support rapid, small queries.

Analytical Use Case

• Description: In analytical environments, where larger datasets are processed, execution times may be longer, and more memory may be required. Optimizations that reduce the dataset size, such as query pushdown, are preferred.
• Recommended Plans:

• Memory Limits: Allow a higher memory allocation to accommodate larger datasets.
• Execution Time: Increase the query timeout limit.
• Concurrency: Limit the maximum number of concurrent queries to prevent server overload.

Special Considerations: In scenarios where batch loads or data ingestion tasks are running, it is advisable to restrict access to certain databases, views, users or roles amongst many other parameters to ensure there are no interruptions in the task with Resource Manager Rules. More use cases are further discussed in the Resource Manager Rule Recommendations section.

Limiting Data Transfers and CPU Usage

In production environments, especially in cloud deployments, limiting data transfer can help reduce costs and improve performance by reducing network congestion. The Resource Manager allows administrators to set a limit on the number of rows that can be returned to client applications as well as the number of concurrent queries that are currently running. This helps to enforce users refine their queries by applying filters or using LIMIT clauses in their queries to reduce unnecessary data transfers.

Queries with high computational demands can strain system resources, especially if they involve operations that require extensive CPU usage (e.g., joins, aggregations, sorting). In these cases, administrators can set limits on the maximum average CPU usage of a single query as well as the average CPU usage of the entire server.

 Recommended Plan

• Maximum Returned Rows: Restrict the maximum number of returned rows allowed per query.

• Limit the CPU usage for queries that require a high amount of parallelism, preventing a single query from overwhelming the system.
• Set time-based restrictions on CPU usage, e.g., allowing a query to use a certain percentage of CPU for only a specified amount of time.

Establishing Context Variables for Dynamic Workload Management

Resource Manager Rules can be dynamically adjusted by using context variables, which are particularly useful in cloud environments or for advanced workload management scenarios.

Recommended Plan

• Use context variables to pass information about workload priorities, specific data sources, or partitions to data sources for efficient query management.
• This can also be used to automate workload distribution across virtual warehouses or databases, based on the current workload or query requirements.

Resource Manager Rule Recommendations

In some environments, it may be necessary to apply ad-hoc restrictions on roles, databases, and specific queries for maintenance during critical operations without interrupting other necessary operations in Denodo.

While Resource Manager Plans can be used to stop queries for different use cases (memory limits, concurrent query limits, etc.), Resource Manager Rules can be used to control the execution of those Plans. Here are some sample implementations of the conditional parameters available in Resource Manager to create Rules.

User-Based Rules

User-based Rules allow administrators to control query execution based on specific users. These Rules can be useful for restricting access to certain system resources or enforcing priority-based execution.

• Implementation: Create a rule that applies a Plan to restrict non-administrators from running queries on the server using the admin attribute.

Role-Based Rules

Role-based Rules allow administrators to enforce execution policies for users with specific roles.

• Implementation: Create a rule using the (roles).value attribute to prevent certain roles from accessing the server outside of high-volume usage hours.

Database Rules

These Rules restrict query execution on specific databases within Virtual DataPort.

• Implementation: Use the database_name attribute to create a rule that stops queries on the targeted databases.

Query-Based Rules

Administrators can define Rules that impact specific types of queries based on their characteristics.

• Implementation: Use the query_running attribute to check query size and apply a Plan that stops or modifies execution accordingly.

Example: To stop certain queries against particular elements like a business view.

Automation

Resource Manager Rules can be enabled and disabled automatically by configuring a job event using Denodo Scheduler.

• Implementation: Configure a Denodo Scheduler job using VQL queries to start/stop Rules.

Note: For more information on the syntax for creating/dropping Plans and Rules, refer to the Resource Manager VQL Guide.

Conclusions

• Adapt resource limits to fit workload characteristics and server capacity.
• Limit data transfer to optimize network usage and reduce costs.
• Monitor CPU usage to prevent resource-intensive queries from affecting performance.
• Use Resource Manager Rules to prevent interference with critical operations like batch loads and maintenance.
• Leverage context variables for dynamic resource management based on query characteristics.
• Automate rule activation using Denodo Scheduler jobs to enforce restrictions as needed and utilize the Scheduler REST Client API to trigger the event with external processes.

By implementing these best practices, organizations can enhance system stability, enforce resource governance, and maintain optimal performance in production environments.

References

Resource Manager

Defining A Plan

Managing the Plans of the Resource Manager

Resource Manager: Available Fields to Evaluate a Rule

Creating and Scheduling Jobs

Scheduler REST Client API