Real environments have multiple consuming applications that have varying workloads and usage patterns. All of them will have their corresponding service level objectives or SLAs, so it's very important to define workload management plans in the data virtualization layer in order to ensure that SLAs are consistently met.

Denodo Platform provides mechanisms for limiting the maximum used resources in the overall server, including:

• Limiting the number of concurrent queries, and queuing the rest. This avoids flooding the system with an excessive number of requests.
• Limiting the number of concurrent connections received in Denodo from a certain published data service.
• Limiting the number of concurrent connections that Denodo sends to a data source or to the cache.
• Timeout configurations to avoid slow data sources or cache accesses to provoke accumulation of queued requests.

Besides that, Denodo Platform allows managing (and limiting if needed) the memory consumed by the execution of a particular view. This can be done through the max.memory and swapping settings of the view. It is also possible to set a global memory limit for the execution of any query in the server or in a particular Virtual DataPort database. This provides a simple and general setting to guarantee no query can monopolize server resources

Denodo Platform extends and simplifies resource management capabilities with the Resource Manager: a UI-based tool included in the Web Design Studio to enforce workload management policies (plans and rules) for dynamically limiting the usage of resources in function of priorities.

Using the Resource Manager, administrators can create a full set of rules which will allow them to dynamically assign resources (CPU, memory, priority) to queries based on criteria such as business priorities. For example, based on the role of the user executing the query or the type of client used to connect to Virtual DataPort.

What are you going to see in this tutorial?

Denodo Administrators will find how to use the Denodo's Resource Manager and see several examples of its usage:

• How to use the Resource Manager
• Real configuration examples:
• Rule for Denodo Administrators
• Rules for Operational environments
• Rule for Analytical scenarios

To guarantee Quality of Service (QoS) in large scale data virtualization deployments, in general, we can distinguish between two main types of workloads with different QoS requirements:

• Informational workloads: these workloads are common in reporting applications that are characterized by the need to handle high volumes of data. There is usually no need for low latency because users are not typically on-line waiting for results.
• Operational / Transactional workloads: these scenarios are typical in operational applications (e.g. Single Customer View application) and are associated with high concurrency levels of queries handling small dataset sizes. These processes need immediate responses.

In an enterprise-level Virtual DataPort(VDP) deployment, both operational and informational workloads can be in place since VDP can be used across many different use cases. In this section, we are going to see how to access the Resource Manager and how to create Rules and Plans. It will allow you to classify queries based on the attributes of the session, and to allocate resources to that query in a way that optimizes resource utilization for your application environment.

In summary, the Resource Manager introduces two main concepts:

• Plans: restrictions assigned to a user session (e.g. limiting the rows returned from a query)
• Rules: assigns a plan to the session of a user if a certain condition is met (e.g. when the source IP address is 10.0.20.80)

Accessing Resource Manager

To open the Resource Manager, click the Administration > Resource Manager option in the top menu

Once the Web Design Studio is opened, you can add Rules and Plans. Plans have to be created first because later, in the rule configuration window, you will have to associate the plan with a rule. Let's see how to create a plan:

1. Click on the Plans tab.
2. Click on New to create a new plan.
   
3. Click on New.
   
4. In this dialog, you have to select two things: when to execute the plan and what actions have to be performed.
   

• When: Always or when the CPU usage goes over a certain percentage
• Actions: Stop query, set priority of threads, switch query to another plan or set the number of concurrent requests, among others.

5. Click Ok to save the restriction.
6. Click Ok again to save the plan.

Now, we can create a new rule and associate the previously created plan. Let's see how:

1. Click on the Rules tab.
2. Click on New to create a new rule.
   
3. Enter a name and a description for the new rule.
4. In this dialog, you have to select if the rule will be applied to all the user sessions (Always) or only to some of them (Specify condition)

• In this case you have to add a condition based on the attributes of the session by clicking ctrl+space which list the: client IP, access interface (JDBC, ODBC, WS-REST, ...), authentication (LOCAL, LDAP, Kerberos), ...
• The attributes can be listed using the key ctrl + space.

5. Select the plan that will be applied to the user when the condition is met.
6. Click Ok to save the rule.

How it works

The Resource Manager acts when a user session is opened against a Virtual DataPort server. The management procedure is the following:

1. A user opens a connection to a Virtual DataPort server.
2. The Resource Manager evaluates the condition of the first rule.
3. If the condition is not met, it evaluates the condition of the second rule.
4. If this second condition is not met, it evaluates the condition of the next rule, and so on.
5. When it finds a rule that meets the condition, it assigns the plan of the rule to the user session and stops evaluating more rules.

Do you need an example?

It's your lucky day! The following section will show you some Resource Manager examples of Rules/Plans.

In a previous tutorial, Denodo Basics, you learned how to access a customer database to get personal and contact information about the company's customers. It is strongly advised to complete the Denodo Basics tutorial before starting this one, as we are going to be using the same elements (data source, base views and integrations) created on it.

If you have already completed it, please proceed to the next section. Otherwise, follow the subsequent instructions:

1. Launch the resources needed (check how in the Installation & Bootstrapping tutorial).
2. Log in Denodo Design Studio (user/password: admin/admin)
3. Import this VQL to Denodo by clicking into File > Import. Drag ‘n' drop the file and select "tutorial" as the Database:

If all steps have been executed correctly, you should observe the following in the Design Studio's elements tree:

Great! Now it's time to start the tutorial!

This section will cover three configuration examples that will show you how to prepare your Virtual DataPort server Resource Manager for your use cases.

Let's suppose several things:

• Our Denodo administrators sometimes need to perform maintenance tasks with high priority using the Web Design Studio.
• Some queries are from an Operational environment only available from 7am to 8pm and which can't return more than 100 rows.
• Some queries are from an Analytical environment where queries can run for more than 3 hours and have a lower priority than operational queries, but no more than 4 analytical report queries can be running at the same time.

Before creating the plans and rules, we will create a new user. Navigate to the Administration > User Management > Click on New to create a new user.

Input the following information:

• Name : den_user
• Description: Local user
• User type: Normal user
• Authentication type: Normal
• Password : Enter password of your choice

Now let's assign some privileges to this user. Select the user click on Edit Privileges. and give the following privileges

• Give Connect, Execute and Metadata privileges over the Database tutorial
• Connect over Admin Database and save the changes.

Now let's define Resource manager restrictions.

In this first example, we have to add a rule for Denodo Administrators that sets their queries to have the maximum priority

The following steps have to be performed:

1. Create a new Plan called "admin".
2. Click on New, to define the restriction
3. Select Execute when: Always
4. Select the following actions:
   
   Set priority: 10

5. Click Ok to save the restriction.
6. Click Ok again to save the plan.
7. Go to the Rules tab.
8. Click on New.
9. Enter "denodo admin" as name.
10. In this dialog, add the condition: access_interface = 'Web-Design-Studio' AND admin_user = true
11. Select admin as plan.

12. Click ok to save

Open a VQL shell and execute the SELECT * FROM tutorial.bv_crm_client Go to the Execution Trace and click on the first node Execution Plan.

You will see the Resource Manager plan has been applied to the admin user.

In this example, we are going to create a plan to limit the maximum number of rows returned. Using this rule, we will avoid large queries (for example, a typical SELECT * FROM table) to be executed on the server.

The following steps have to be performed:

1. Create a new Plan called "max 10 rows".
2. Click on New to define the restriction.
3. Select Execute when: Always.
4. Select the following actions:
   
   Stop query when the maximum number of returned rows has been reached: 10.

5. Click Ok to save the restriction.
6. Click Ok again to save the plan.
7. Go to the Rules tab.
8. Click on New.
9. Enter "row limit for local user" as name.
10. In this dialog, add the following condition: (user_name = 'dc_user' AND access_interface = 'Web-Design-Studio')
11. Select max 10 rows as a plan.

12. Click Ok to save the rule.

Now logout from the Design Studio session as admin user and login with the local user we created.

Open a VQL shell and execute select * from bv_crm_client

. You will notice that the Resource manager restriction has been applied and the query stopped after retrieving 10 rows.

For the above plan, we have set the access_interface as Design Studio hence when the user access the web design studio and executes a query the limitation is applied. Let us observe a different behavior by logging into other web applications such as Denodo Data Catalog.

From the Denodo Control Center launch the Data Catalog URI or you can use the URL http://<hostname>:9090/denodo-data-catalog/#/ Ensure that the Data Catalog server is started and it's running. Log into the Data Catalog web tool using the same local user den_user

• Search for the view bv_crm_client in the search bar. Open the view and navigate to > Query > Select all. Select all the fields- drag and drop it in the Output columns.
• Then click on Execute
• You will see 80 rows are returned in this case.

From this example, you have understood that using Resource manager you can limit rows for user accessing using different interfaces. In this case, the same user was allowed to see only 10 rows in Design studio but in Data Catalog the same user can see all the result sets.

For this example, we are going to create one rule/plan similar to the previous one with one small difference. This time, we are going to suppose that analytical queries will be performed from Tableau, so they are always going to use the ODBC interface to connect to Virtual DataPort.

Additionally, if the server is under heavy load conditions, we want to limit concurrent analytical queries.

To do this, following steps have to be performed:

1. Create a new Plan called "limit analytical concurrent queries".
2. Click on New restrictions.
3. Select Execute when: Always.
4. Select the following actions:
   
   Set the maximum number of concurrent queries: 4.
   Set priority: 5.

5. Click Ok to save the restriction.
6. Click on New restriction.
7. Select Execute when: When the CPU usage goes over a certain percentage: 50.
8. Select the following actions:
   
   Set the maximum number of concurrent queries: 2.
   Set priority: 4.

9. Click Ok to save the restriction.
10. Click Ok again to save the plan.
11. Go to the Rules tab.
12. Click on New.
13. Enter "analytical report queries" as name.
14. In this dialog you have to add the following condition: user_agent = 'analytical_env' OR access_interface = 'ODBC'
15. Select limit analytical concurrent queries as plan.
16. Click Ok to save the rule.

With these examples, you have finished the Workload Management Tutorial. Now it's time to continue exploring what the Denodo Platform can do for you, take a look at the official reference manuals and play with your own use cases.

Congratulations!