---

Through its Resource Governor feature, MemSQL allows you to define resource pools, which can specify resource limits. As an example, you can use these resource limits to prevent one user’s query from making the system unusable for others connected to the database. This allows you to prevent non-critical workloads from overloading the system.

You can set the limits MEMORY_PERCENTAGE, SOFT_CPU_LIMIT_PERCENTAGE, HARD_CPU_LIMIT_PERCENTAGE, QUERY_TIMEOUT, MAX_CONCURRENCY, and MAX_QUEUE_DEPTH in resource pools. For information on these settings, see CREATE RESOURCE POOL.

Each client connection is assigned to a resource pool that specifies some (or all) of these limits.

Related to setting resource limits through governance is the concept of workload management. Workload management is a component of MemSQL that automatically manages cluster workloads by limiting execution of queries that require fully distributed execution, to ensure that they are matched with available system resources. For more information, see Workload Management.

Creating a New Resource Pool

To create a new resource pool, run CREATE RESOURCE POOL from the master aggregator. For information on how to use this command and an example, see the CREATE RESOURCE POOL topic.

Modifying an Existing Resource Pool

To modify an existing resource pool, run ALTER RESOURCE POOL from the master aggregator. For information on how to use this command and an example, see the ALTER RESOURCE POOL topic.

The Built-in Resource Pools

Every cluster contains the built-in resource pools system_optimizer_pool, default_pool, and system_auto.

The system_optimizer_pool runs when enable_background_statistics_collection is set to ON. If the system_optimizer_pool is running and the resource_governor_cpu_limit_mode is set to HARD, the pool reserves 5% of CPU utilization for collection of background statistics.

By default, client connections to MemSQL use the default_pool. To use a different pool, see the next section, Setting the resource_pool Value. Queries in the default_pool can have up to 100% of available query execution memory, up to 100% CPU utilization (depending on the resource_governor_cpu_limit_mode setting), and do not have a timeout limit set or a defined number of concurrent queries.

Setting the resource_pool Value

Set the engine variable resource_pool to specify the resource pool to be used by an existing or new client connection. For an existing connection, use SET resource_pool = <pool_name>;. For all new connections, use SET GLOBAL resource_pool = <pool_name>;. By default, resource_pool is set to default_pool, unless a default resource pool has been set for the user who is connected.

For an example of how resource pools can be used together, consider the following example. Two resource groups, executive and general, have been created on a cluster.

CREATE RESOURCE POOL executive WITH MEMORY_PERCENTAGE = 60;
CREATE RESOURCE POOL general WITH MEMORY_PERCENTAGE = 40;

At any point in time, executive can use at most 60% of query execution memory and general can use at most 40% of query execution memory. This allows concurrent queries in both pools to consume all available query execution memory. CPU limits using SOFT_CPU_LIMIT_PERCENTAGE can be set the same way.

The resource_pool variable is set to general so that all new connections are assigned to that pool by default.

SET GLOBAL resource_pool = general;

When a new user connects to the cluster, it will be part of the general resource pool. You can then change its resource pool by changing the current value to executive. This will enable usage of a larger portion of the available query execution memory.

SET resource_pool = executive;

Thus an application controlling client connections would give priority to “executive” users by setting their resource_pool to executive after those users connect. This prevents an accidental memory-intensive query from a user in the general resource pool from consuming memory that should be kept available for users in the executive pool.

Finding the Current resource_pool Value

You can determine the current resource pool by querying for the resource_pool variable.

SHOW VARIABLES like '%resource_pool%';
****
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| resource_pool | test_pool  |
+---------------+------------+

SELECT @@resource_pool;
****
+-----------------+
| @@resource_pool |
+-----------------+
| test_pool       |
+-----------------+

To query for the complete list of available resource pools, use the SHOW RESOURCE POOLS command.

SHOW RESOURCE POOLS;
****
+--------------+-------------------+---------------+-----------------+---------------------------+
| Pool_Name    | Memory_Percentage | Query_Timeout | Max_Concurrency | Soft_CPU_Limit_Percentage |
+--------------+-------------------+---------------+-----------------+---------------------------+
| default_pool |               100 |          NULL |            NULL |                       100 |
| executive    |                60 |          NULL |            NULL |                        50 |
| general      |                40 |          NULL |            40   |                        50 |
+--------------+-------------------+---------------+-----------------+---------------------------+

For more information, see the list of engine variables, and SHOW RESOURCE POOLS.

MV_RESOURCE_POOL_STATUS

You can also show resource pool status across your pools with the MV_RESOURCE_POOL_STATUS information_schema table. The MV_RESOURCE_POOL_STATUS view collects data from all nodes. It will give active details about queued queries for resource pools that had set a maximum concurrency, as well as some historical data (killed, finished queries and total and average queue time).

SELECT * FROM MV_RESOURCE_POOL_STATUS;
****
+---------+-----------+-----------------+------------------+----------------+------------------+---------------------+-----------------------+
| NODE_ID | POOL_NAME | RUNNING_QUERIES | QUEUEING_QUERIES | KILLED_QUERIES | FINISHED_QUERIES | TOTAL_QUEUE_TIME_US | AVERAGE_QUEUE_TIME_US |
+---------+-----------+-----------------+------------------+----------------+------------------+---------------------+-----------------------+
|       3 | pool1     |               0 |                0 |              0 |                0 |                   0 |                     0 |
|       1 | pool1     |               0 |                0 |              0 |                0 |                   0 |                     0 |
|       2 | pool1     |               0 |                0 |              0 |                0 |                   0 |                     0 |
+---------+-----------+-----------------+------------------+----------------+------------------+---------------------+-----------------------+

For more information, see ALTER RESOURCE POOL.

Query Execution Behavior

For information on query execution behavior, see the CREATE RESOURCE POOL topic.

Setting the Default Resource Pool for a User

The default resource pool of a user is the resource_pool value that will be set when a user first connects to MemSQL server. You can set this value when you create a user, or modify this value later. To set this value when creating a user, use the following syntax:

CREATE USER general_user WITH DEFAULT RESOURCE POOL = general;

Run the SHOW USERS command to see the default pool assigned to each user.

SHOW USERS;
****
+--------------------+--------+-------------+------------+-----------------------+
| User               | Type   | Connections | Is deleted | Default resource pool |
+--------------------+--------+-------------+------------+-----------------------+
| 'general_user'@'%' | Native |           0 |            | general               |
| 'root'@'%'         | Native |           1 |            |                       |
+--------------------+--------+-------------+------------+-----------------------+

If the default resource pool is not explicitly set for the user, the default resource pool for that user is the pool set by the command SET GLOBAL resource_pool = foo, or default_pool if the session variable is not set globally. The default resource pool information is also in the information schema table.

SELECT * FROM INFORMATION_SCHEMA.USERS;
+--------------+------+-------------+------------+---------------------+-----------------------+
| USER         | HOST | CONNECTIONS | IS_DELETED | LAST_UPDATED        | DEFAULT_RESOURCE_POOL |
+--------------+------+-------------+------------+---------------------+-----------------------+
| root         | %    |           1 |          0 | 2018-06-29 11:51:51 |                       |
| general_user | %    |           0 |          0 | 2018-06-29 11:54:38 | general               |
+--------------+------+-------------+------------+---------------------+-----------------------+

You can also change the default pool of a user using the ALTER USER command. For more information, see ALTER USER.

Changing the default resource pool for a user

You can change the default pool of an existing user with the ALTER USER command.

Execute the SHOW USERS command to view default the resource pools assigned to each user.

SHOW USERS;
****
+-------------+--------+-------------+------------+-----------------------+----------+
| User        | Type   | Connections | Is deleted | Default resource pool | Is local |
+-------------+--------+-------------+------------+-----------------------+----------+
| 'root'@'%'  | Native |           2 |            |                       | LOCAL    |
| 'rw'@'%'    | Native |           0 |            |                       | LOCAL    |
| 'view'@'%'  | Native |           0 |            |                       | LOCAL    |
| 'write'@'%' | Native |           0 |            |                       | LOCAL    |
+-------------+--------+-------------+------------+-----------------------+----------+

Create a resource pool.

CREATE RESOURCE POOL limReg WITH MEMORY_PERCENTAGE = 50, QUERY_TIMEOUT = 10, MAX_QUEUE_DEPTH = 5, MAX_CONCURRENCY = 5;

Run SHOW RESOURCE POOLS to view the list of available resource pools.

SHOW RESOURCE POOLS;
****
+-----------------------+-------------------+---------------+-----------------+---------------------------+---------------------------+-----------------+
| Pool_Name             | Memory_Percentage | Query_Timeout | Max_Concurrency | Soft_CPU_Limit_Percentage | Hard_CPU_Limit_Percentage | Max_Queue_Depth |
+-----------------------+-------------------+---------------+-----------------+---------------------------+---------------------------+-----------------+
| default_pool          |               100 |          NULL |            NULL |                      NULL |                      NULL |            NULL |
| system_auto           |               100 |          NULL |            NULL |                      NULL |                      NULL |            NULL |
| system_optimizer_pool |               100 |          NULL |            NULL |                         5 |                      NULL |            NULL |
| limReg                |                50 |            10 |               5 |                      NULL |                      NULL |               5 |
+-----------------------+-------------------+---------------+-----------------+---------------------------+---------------------------+-----------------+

Use the ALTER USER command to change the default resource pool for the 'rw'@'%' user.

ALTER USER 'rw'@'%' SET DEFAULT RESOURCE POOL = limReg;

SHOW USERS;
****
+-------------+--------+-------------+------------+-----------------------+----------+
| User        | Type   | Connections | Is deleted | Default resource pool | Is local |
+-------------+--------+-------------+------------+-----------------------+----------+
| 'root'@'%'  | Native |           1 |            |                       | LOCAL    |
| 'rw'@'%'    | Native |           0 |            | limReg                | LOCAL    |
| 'view'@'%'  | Native |           0 |            |                       | LOCAL    |
| 'write'@'%' | Native |           0 |            |                       | LOCAL    |
+-------------+--------+-------------+------------+-----------------------+----------+

User Privileges and Resource Pools

Users can view the available resource pools and their status as per the granted permissions. By default, users can access the following resource pools:

• Built-in resource pools system_optimizer_pool, default_pool, and system_auto.

• The default resource pool set for the user when the user is created.

  CREATE USER general_user WITH DEFAULT RESOURCE POOL = general;
  

• All available resource pools, if the user has SUPER permission.

Permissions for additional resource pools can be given using the GRANT command:

GRANT USAGE ON RESOURCE POOL <resource_pool_name> to '<user_name>'@'%';

Use * to grant permissions on all resource pools, even if the user does note have the SUPER permission:

GRANT USAGE ON RESOURCE POOL * to '<user_name>'@'%';

Selecting a Resource Pool Dynamically

You can use a user-defined scalar function (UDF) to select the resource pool that an existing or new client connection uses.

Enabling Dynamic Resource Pool Selection

To enable the current connection to select a resource pool dynamically, run SET resource_pool = system_auto. To enable all new connections to select a resource pool dynamically, run SET GLOBAL resource_pool = system_auto.

Registering and Defining the Resource Pool Selector Function

After enabling dynamic resource pool selection, set the resource pool selector function to be called when the user runs a query:

SET GLOBAL resource_pool_statement_selector_function = '<database name>.<function name>';

Define the selector function as follows:

CREATE FUNCTION <database name>.<function name> RETURNS
  VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci AS
  DECLARE
    <Any needed variables declared here>
    BEGIN
      <Logic that determines the resource pool name to return in a RETURN statement>
    END

The selector function must return the name of a resource pool. The function’s return type can be VARCHAR(64) or VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci. If you return the former data type, it will be automatically converted to the latter data type. COLLATE utf8_general_ci indicates that the data type is not case-sensitive.

Example selector functions are provided later in this section.

Resource Pool Selector Function Built-Ins that Provide Estimates

When you write the resource pool selector function, you can use the built-in functions ESTIMATED_QUERY_LEAF_MEMORY() and ESTIMATED_QUERY_RUNTIME() to help you select the resource pool that you return from the function.

To provide accurate estimates, statistics must first be collected on the queries that these functions operate on. Statistics on these queries are collected on ten minute intervals, after the queries have run once. You can trigger manual collection of statistics by running ANALYZE MEMORY.

You can use ESTIMATED_QUERY_LEAF_MEMORY() and ESTIMATED_QUERY_RUNTIME() outside the resource pool selector function. However, they are intended to be used inside this function.

Disabling Dynamic Resource Pool Selection

To disable dynamic resource pool selection, set resource_pool to a value other than system_auto. The value must be an existing resource pool.

Selector Function Security

When you create the resource pool selector function, you must have permission to run everything in the function’s body.

To set the resource_pool_statement_selector_function engine variable, you must have the SUPER permission and the EXECUTE permission on the resource pool selector function.

Example UDFs that Select Resource Pools

Example 1: Selecting a Resource Pool Based on the Current User

The following example UDF selects a resource pool based on the user running the query. It assumes the resource pools executive and general have been created, as shown in the section Setting the resource_pool Value.

DROP DATABASE IF EXISTS memsql_docs_example;
CREATE DATABASE memsql_docs_example;
USE memsql_docs_example;

DELIMITER //
CREATE FUNCTION memsql_docs_example.select_pool() RETURNS
  VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci AS
  BEGIN
    IF CURRENT_USER() = 'root@%' THEN
      RETURN 'executive';
    ELSE
      RETURN 'general';
    END IF;
  END //
DELIMITER ;

SET resource_pool = system_auto;

SET GLOBAL resource_pool_statement_selector_function = 'memsql_docs_example.select_pool';

/* Return the executive resource pool from the select_pool UDF if the
current user is the root user. Otherwise, return the general resource pool. */

SELECT 1, CURRENT_RESOURCE_POOL();

Example 2: Selecting a Resource Pool Based on the Current Database

The following example creates two resource pools with memory usage limits and defines a UDF selects a resource pool based on the current database.

DROP DATABASE IF EXISTS memsql_docs_example;
CREATE DATABASE memsql_docs_example;
USE memsql_docs_example;

/* Resource pool used by users who are in training */
CREATE RESOURCE POOL training WITH MEMORY_PERCENTAGE = 5;

/* Resource pool used by users for other purposes */
CREATE RESOURCE POOL main WITH MEMORY_PERCENTAGE = 95;

CREATE TABLE simple_table (a INT);
INSERT INTO simple_table (a) VALUES (1, 2, 3, 4, 5);

DELIMITER //
CREATE FUNCTION select_pool() RETURNS
  VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci AS
  BEGIN
    IF DATABASE() = 'memsql_docs_example' THEN
      RETURN 'training';
    ELSE
      RETURN 'main';
    END IF;
  END //
DELIMITER ;

SET resource_pool = system_auto;

SET GLOBAL resource_pool_statement_selector_function = 'memsql_docs_example.select_pool';

/* The following query uses the training resource pool returned from the
select_pool UDF, since memsql_docs_example is the current database. */

SELECT a FROM simple_table ORDER BY a;

/* The following SELECT query uses the main resource pool returned from
the select_pool UDF, since information_schema is the current database.
Prefacing simple_table with memsql_docs_example does not make
memsql_docs_example the current database. */

USE information_schema;
SELECT a FROM memsql_docs_example.simple_table ORDER BY a;

Example 3: Selecting a Resource Pool Based on the Estimated Memory Required to Run a Query

The following example creates three resource pools with memory usage limits and defines a UDF that selects a resource pool based on the amount of estimated memory required to run a query.

CREATE RESOURCE POOL low_memory WITH MEMORY_PERCENTAGE = 15;
CREATE RESOURCE POOL medium_memory WITH MEMORY_PERCENTAGE = 70;
CREATE RESOURCE POOL high_memory WITH MEMORY_PERCENTAGE = 15;

DROP DATABASE IF EXISTS memsql_docs_example;
CREATE DATABASE memsql_docs_example;
USE memsql_docs_example;

DELIMITER //
CREATE FUNCTION select_pool() RETURNS
  VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci AS
  DECLARE            
    leaf_memory INT;
  BEGIN
    leaf_memory = ESTIMATED_QUERY_LEAF_MEMORY();
    IF leaf_memory = -1 THEN
      RETURN 'default_pool';                
    ELSIF leaf_memory < 1 THEN
      RETURN 'low_memory';
    ELSIF leaf_memory > 100 THEN
      RETURN 'high_memory';
    ELSE
      RETURN 'medium_memory';
    END IF;
  END //
DELIMITER ;

SET resource_pool = system_auto;
SET GLOBAL resource_pool_statement_selector_function = 'memsql_docs_example.select_pool';

Next, do an initial run of the query, followed by ANALYZE MEMORY to collect statistics on the query. The initial run returns default_pool because statistics have not yet been collected:

SELECT 1, CURRENT_RESOURCE_POOL();
****
+---+-------------------------+
| 1 | CURRENT_RESOURCE_POOL() |
+---+-------------------------+
| 1 | default_pool            |
+---+-------------------------+

Collect statistics on the query:

ANALYZE MEMORY;

Re-run the SELECT query. The expected resource pool is returned when the select_pool UDF runs ESTIMATED_QUERY_LEAF_MEMORY():

SELECT 1, CURRENT_RESOURCE_POOL();
****
+---+-------------------------+
| 1 | CURRENT_RESOURCE_POOL() |
+---+-------------------------+
| 1 | low_memory              |
+---+-------------------------+

Example 4: Selecting a Resource Pool Based on the Estimated, Elpased Time Required to Run a Query

The following example demonstrates selecting a resource pool based on the estimated, elapsed time required to run a query.

Create three resource pools with query timeouts and defines a UDF that selects a resource pool based on the estimated time required to run a query:

CREATE RESOURCE POOL low_run_time WITH QUERY_TIMEOUT = 1;
CREATE RESOURCE POOL medium_run_time WITH QUERY_TIMEOUT = 2;
CREATE RESOURCE POOL high_run_time;

DROP DATABASE IF EXISTS memsql_docs_example;
CREATE DATABASE memsql_docs_example;
USE memsql_docs_example;

DELIMITER //
CREATE FUNCTION select_pool() RETURNS
  VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci AS
  DECLARE
    run_time double;
  BEGIN
    run_time = ESTIMATED_QUERY_RUNTIME();
    IF run_time = -1 THEN
      RETURN 'default_pool';
    ELSIF run_time < 1.0 then
      RETURN 'low_run_time';
    ELSIF run_time < 2.0 THEN
      RETURN 'medium_run_time';
    ELSE
      RETURN 'high_run_time';
    END IF;
  END //
DELIMITER ;

SET resource_pool = system_auto;

SET GLOBAL resource_pool_statement_selector_function = 'memsql_docs_example.select_pool';

Next, do an initial run of three queries, followed by ANALYZE MEMORY to collect statistics on the queries. The initial run of each query returns default_pool because statistics have not yet been collected:

SELECT SLEEP(0.5), CURRENT_RESOURCE_POOL();
****
+------------+-------------------------+
| SLEEP(0.5) | CURRENT_RESOURCE_POOL() |
+------------+-------------------------+
|          0 | default_pool            |
+------------+-------------------------+

SELECT SLEEP(1.5), CURRENT_RESOURCE_POOL();
****
+------------+-------------------------+
| SLEEP(1.5) | CURRENT_RESOURCE_POOL() |
+------------+-------------------------+
|          0 | default_pool            |
+------------+-------------------------+

SELECT SLEEP(3.0), CURRENT_RESOURCE_POOL();
****
+------------+-------------------------+
| SLEEP(3.0) | CURRENT_RESOURCE_POOL() |
+------------+-------------------------+
|          0 | default_pool            |
+------------+-------------------------+

Collect statistics on the queries:

ANALYZE MEMORY;

Re-run the previous query that takes 0.5 seconds. It returns the low_run_time resource pool from the select_pool UDF:

SELECT SLEEP(0.5), CURRENT_RESOURCE_POOL();
****
+------------+-------------------------+
| SLEEP(0.5) | CURRENT_RESOURCE_POOL() |
+------------+-------------------------+
|          0 | low_run_time            |
+------------+-------------------------+

Re-run the pervious query that takes 1.5 seconds. It returns the medium_run_time resource pool from the select_pool UDF.

SELECT SLEEP(1.5), CURRENT_RESOURCE_POOL();
****
+------------+-------------------------+
| SLEEP(1.5) | CURRENT_RESOURCE_POOL() |
+------------+-------------------------+
|          0 | medium_run_time         |
+------------+-------------------------+

Re-run the previous query that takes 3.0 seconds. It returns the high_run_time resource pool from the select_pool UDF:

SELECT SLEEP(3.0), CURRENT_RESOURCE_POOL();
****
+------------+-------------------------+
| SLEEP(3.0) | CURRENT_RESOURCE_POOL() |
+------------+-------------------------+
|          0 | high_run_time           |
+------------+-------------------------+

For more information, see ALTER USER.