Set of examples

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 only the Virtual DataPort Administration Tool.
  • 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.

Example 1: Add higher priority to admin queries

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 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 = 'VDP-AdminTool' AND admin_user = true
  11. Select admin as plan.

  12. Click Ok to save the rule.

Example 2: Limit the number of rows returned

In this example, we are going to create two rules to restrict the execution of operational queries to only certain times of the day and 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 in the server.

The following steps have to be performed:

  1. Create a new Plan called "max 100 rows".
  2. Click on New restriction.
  3. Select Execute when: Always.
  4. Select the following actions:
    • Stop query when the maximum number of returned rows has been reached: 100.
    • Set priority > 7.


  5. Click Ok to save the restriction.
  6. Click Ok again to save the plan.
  7. Create a new Plan called "reject all queries".
  8. Click on New restriction.
  9. Select Execute when: Always.
  10. Select Stop query always
  11. .

  12. Click Ok to save the restriction.
  13. Click Ok again to save the plan.
  14. Go to the Rules tab.
  15. Click on New.
  16. Enter "operational hours" as name.
  17. In this dialog, add the following condition: user_agent = 'operational_env' AND (gethour(connection_start_time) < 7 OR gethour(connection_start_time) >= 20)
  18. Select reject all queries as plan.

  19. Click Ok to save the rule.
  20. Click on New to add another rule.
  21. Enter "operational rows" as name.
  22. In this dialog, add the following conditions: user_agent: operational_env (this name has to be set in the application)
  23. Select max 100 rows as plan.

  24. Click Ok to save the rule.

Again, the order is important. If you configure these rules with the wrong order, the 'operational hours' rule will never be executed!

NOTE

Example 3: Change the concurrent requests limit

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 analyical concurrent queries".
  2. Click on New restriction.
  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!