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.

      • 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.

      • 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.

      • 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.

      • 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 atributes 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%.