---

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.

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.

The following limits can be set:

• MEMORY_PERCENTAGE Memory usage for users can be limited from 5% to 100% of the query execution memory resources available (maximum_memory - persistent memory for tables, databases, indexes, etc.) in the system. This is a hard limit for each resource pool, which means the pool can not utilize memory above this limit. For more information on maximum_memory, see Memory Management.

• SOFT_CPU_LIMIT_PERCENTAGE CPU usage for users can be limited from 1% - 100% of available processing capacity. This is a soft limit that allows CPU usage for a given pool to temporarily burst above the limit during a period of low/idle CPU usage; however, if other pools need CPU resources, then any pools above their soft limit will be immediately pushed down to their assigned limit.

• QUERY_TIMEOUT To control query execution time, you can define a query execution time limit that cancels a running query after that timeout value has been reached.

• MAX_CONCURRENCY To limit the amount of concurrent queries, you can define the maximum amount of concurrent queries that can be run across the cluster.

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

Note: Resource limits also apply to any LOAD DATA queries, internal sampling queries, or stored procedures that are run by a user within a given resource pool.

Creating a new resource pool

By default, every MemSQL cluster has a resource pool named default_pool. Queries in this pool can have up to 100% of available query execution memory, up to 100% CPU utilization, do not have a timeout limit set or a defined number of concurrent queries. To use a resource pool with different limits, run the CREATE RESOURCE POOL command from the master aggregator and define the resource limits for users connecting to the database.

CREATE RESOURCE POOL test_pool
WITH MEMORY_PERCENTAGE = 60,
QUERY_TIMEOUT = 20,
SOFT_CPU_LIMIT_PERCENTAGE = 50,
MAX_CONCURRENCY = 30;

For more information, see CREATE RESOURCE POOL.

Setting the resource_pool value

The resource pool of any new connection is set to the value of the engine variable resource_pool. Every new connection defaults to the persisted resource_pool value defined for that node unless overridden. If no user-defined pool value is set, then the built-in default_pool is used. You can change this to another pool name through the SET command in two different scenarios:

• To change the resource pool for new client connections, use the SET GLOBAL command.

  SET GLOBAL resource_pool = <pool_name>;
  

• To change the resource pool during an existing client connection, use the SET command.

  SET resource_pool = <pool_name>;
  

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       |
+-----------------+
1 row in set (0.01 sec)

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 |
+---------+-----------+-----------------+------------------+----------------+------------------+---------------------+-----------------------+

Changing resource limits

Resource limits can be changed for any user-defined pools through the ALTER RESOURCE POOL command.

ALTER RESOURCE POOL test_pool SET QUERY_TIMEOUT = 40, MAX_CONCURRENCY = 25;

You can set MEMORY_PERCENTAGE or SOFT_CPU_LIMIT_PERCENTAGE values for your pools to total over 100%, which gives you the ability to “oversubscribe” the values in your list. This allows you to change values for your resource pools without having to update them in a pre-defined order.

To ensure pools have available memory up to their limit value, you should make the combined pools’ memory sum to 100% or less. Also, changes for a pool will take effect immediately, but running queries in the pool won’t be killed if the pool currently has over its new memory limit.

For more information, see ALTER RESOURCE POOL.

Query execution behavior

New queries will be queued on a given aggregator if the number of currently running queries is equal to FLOOR(C/N), where C is the maximum concurrency of the resource group pool and N is the number of aggregators. Even if FLOOR(C/N) = 0 for a resource pool, MemSQL allows the resource pool to run at least one query per aggregator. If an actively running query requires more memory than is available in its pool, it will be terminated when that memory limit is reached.

Also, queries in a pool that run for longer than the QUERY_TIMEOUT value are terminated. A timeout of 0 means unlimited.

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               |
+--------------+------+-------------+------------+---------------------+-----------------------+

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    |
+-------------+--------+-------------+------------+-----------------------+----------+

For more information, see ALTER USER.