Defining a Plan¶

After enabling the Resource Manager, you have to create one or more plans and later, one or more rules linked to one of these plans.

To define a plan, do the following:

1. Click the Plans tab.

2. Click New to create a plan. In the “Create plan” dialog, enter the name of the plan and optionally, a description.

Resource Manager: creating a new plan¶

3. Click New restriction. In this dialog, select two things:

   1. Execute when: select when the plan will be activated. The options are:

      1. Always.

      2. When the average CPU usage goes over a certain percentage during a specific period of time. This restriction is evaluated when the query starts and every 20 seconds. For example, you can create a plan to stop low priority queries when the server CPU has been very high during the last 30 seconds.

         Maximum value: 3600 (seconds).

      Important

      Virtual DataPort stores the CPU usage history of the last 1 hour, so the maximum time window that can be specified on the resource manager plan is 3600 seconds.

   2. Select the actions the Resource Manager will perform when the condition above is met. These actions are applied to SELECT or CALL statements, not to DDL (CREATE VIEW, CREATE REST WEBSERVICE, etc.) or DML ones (INSERT, UPDATE, etc.)

      • Stop query always. Stops the query immediately.

      • Switch query to plan: the query will be moved to a different plan.

      • Stop query when the maximum execution time has been reached.

      • Stop query when the maximum number of returned rows has been reached.

      • Stop query when the average CPU usage of the query has been equal to or greater than the threshold during the specified period of time. For example, you can configure a restriction to stop the query if it has been using more than the 90% of the CPU in the last 60 seconds.

      • Stop query when the estimated cost condition has been reached. The condition to decide whether or not to stop the query may be based on the following stats:

        • total_query_cost: total estimated cost of the execution plan selected by the optimizer.

        • total_datasource_cost: sum of the estimated costs for all the data sources involved in the execution plan.

        • average_datasource_cost: average of the estimated cost of all the data sources involved in the execution plan.

        • any_datasource_cost: cost of any of the data sources involved in the execution plan.

        • any_datasource_provider: provider of any of the data sources in the plan.

        • any_datasource_region: region of any of the data sources in the plan.

        Important

        Please note that cost estimations depend on many factors that are specific to the particular query being executed and the particular environment where it is executed. In general, when comparing the estimates for two different queries, you cannot assume that the query with lower cost estimation will run faster. Therefore, if you create a plan with this action, set up the rules so it applies only to queries that are very similar to the ones you used as reference to obtain cost values.

        Below are some examples:

        • “Stop query if the execution plan cost is greater than 100000” - total_query_cost > 100000

        • “Stop query if the execution plan cost is greater than 100000 or the average data source cost is greater than 25000” - total_query_cost > 100000 OR average_datasource_cost > 25000

        • “Stop query if any of the data sources involved has an estimated cost greater than 25000” - any_datasource_cost > 25000

        • “Stop query if uses any data source from Azure or Google Cloud Platform” - any_datasource_provider IN (‘Azure’, ‘Google Cloud Platform’)

        • “Stop query if uses any data source from Azure in the region of Australia or from Amazon Web Services in any region” - (any_datasource_provider = ‘Azure’ AND any_datasource_region = ‘australiacentral / Australia Central’ ) OR (any_datasource_provider = ‘Amazon Web Services’)

      • Limit the maximum memory that can be used by the query. When the query reaches this limit during its execution, it will be stopped.

      • Set priority of the threads that execute the query: changes the priority of the threads spawned by the Virtual DataPort server to run the query. 1 is the lowest priority and 10, the highest.

        Increasing the priority of the threads makes the query run faster.

        Important

        If the Virtual DataPort server runs on Linux, this action will only be applied if the following two conditions are met:

        1. Virtual DataPort is started with root privileges (i.e. with the root user account or with sudo ./vqlserver_startup.sh)

        2. And the following has been added to the “JVM options” of the Virtual DataPort server: -XX:ThreadPriorityPolicy=1.

        The reason for having to meet these conditions is that on Linux, only processes launched by the root user can change the priority of its threads dynamically.

        This reminder does not affect Virtual DataPort servers that run on Windows because on Windows, processes can change the priority of its own threads.

      • Set the maximum number of concurrent queries. Sets the maximum number of concurrent queries run by the group of users that meet the condition of a rule (rules are explained in the next section).

        For example, let us say that we create a rule with the condition “access_interface=JDBC” and assign to it a plan that limits the maximum number of concurrent queries to 30. If 35 JDBC clients execute a query at the same time and they do not meet the condition of another rule that is higher on the list of rules, the Server will only execute 30 queries concurrently. The other ones will be queued.

        The value of this action does not override the maximum number of concurrent queries set in the “Server Configuration” dialog.

        When using this action in a plan restriction that is conditioned to some value of CPU it is important to be aware that once the CPU condition evaluates to true and the action is executed, the concurrency is set for all queries assigned to that plan, and it will not be reset automatically when the CPU goes below the CPU threshold. It has to be another restriction with different CPU threshold in charge of reset the concurrency. For example, let us say that we create a rule that assigns the plan “adjustConcurrency” to all queries and we define the plan with a restriction “If CPU > 90% for the last 30 seconds then limit concurrency to 20”. When running a query, the plan condition is evaluated and the CPU condition is true, so the Resource Manager sets the concurrency for plan “adjustConcurrency” to 20. That means that all queries will be under the 20 concurrent request limit, even if the CPU goes down below 90% because all queries belong to that plan. So, the correct way to adjust concurrency based on CPU is using two restrictions on the same plan. Following the example: one that sets concurrency to zero (unlimited) if CPU is greater than 0% and one that sets concurrency to 20 (or whatever value) if CPU is greater than 90% during the last 30 seconds.

      • Set the maximum number of concurrent queries per user. Similar to the previous one, but only counting queries of the same user.

        For example, let us say that we create a rule with the condition “access_interface=JDBC” and assign to it a plan that limits the maximum number of concurrent queries per user to 15. If 2 different users run 20 queries each at the same time through JDBC, the Server will execute 30 queries concurrently, 15 from one user and 15 from the other. The other ones will be queued.

        When using in a restriction with a condition based on CPU, what we explained in the point above applies here too. So be aware that you should use additional restrictions to restore concurrency levels when the CPU goes down the threshold.

      • Set the maximum number of queued queries. Sets the maximum number of queued queries run by the group of users that meet the condition of a rule.

        The value of this action does not override the maximum number of queued queries set in the “Server Configuration” dialog.

        The explanation above about limiting concurrency based on CPU usage levels applies to this action too. So be aware that you should use additional restrictions to restore the maximum number of queued queries when the CPU goes down the threshold.

      • Enable/disable the automatic simplification of this query. The process of automatic simplification of queries is explained on the section Automatic Simplification of Queries.

      • Set the maximum number of queries per time unit. Sets the maximum number of queries per time unit (per minute, per hour, per day or per month) run by the group of users that meet the condition of a rule. This action is only available when the option Execute when is Always.

      • Add variable values to the execution context. Click Add or Remove to add or remove pairs variable name / variable value. To define the variable value, you can use any expression than can be casted to a text value. The expression can use same field set as resource manager rules. The appendix Resource Manager: Available Fields to Evaluate a Rule lists all the fields you can use in these expressions.

        When this plan applies to a running query, the variables defined here are available on the execution context. The variable values can be read in:

        • The query, using function GETVAR

        • In the initial SQL query of a JDBC data source.

        • In the Global Security Policy when defining the audience based on the attributes of the user’s session.

   3. Click Ok to save the restriction and again, to save the plan.

You can create a plan with several restrictions. For example,

• A restriction that is triggered when the CPU usage is above 50%.

• A restriction that is triggered when the CPU usage is above 80%.