MemSQL allows you to define resource pools, which specify 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_PERCENTAGEMemory 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 onmaximum_memory, see Memory Management. -
QUERY_TIMEOUTTo control query execution time, you can define a query execution time limit that cancels a running query after that timeout value has been reached.
Each client connection is assigned to a resource pool that specifies one (or both) of these limits.
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 and do not have a timeout limit set. To use a resource pool with different limits, run the CREATE RESOURCE POOL command from the master aggregator and define the memory and/or query timeout limits for users connecting to the database.
CREATE RESOURCE POOL test_pool WITH MEMORY_PERCENTAGE = 60, QUERY_TIMEOUT = 20;
Resource pools specify limits for all databases in a cluster. It is not possible to set limits at the database level.
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 system 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 GLOBALcommand.SET GLOBAL resource_pool = <pool_name>; -
To change the resource pool during an existing client connection, use the
SETcommand.SET resource_pool = <pool_name>;
Resource pools are defined on the master aggregator; however, as with other system variables, you must set the resource_pool value on every client connection; otherwise it will be set to default_pool. See SET GLOBAL for more information.
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.
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_Percent | Query_Timeout|
+--------------+----------------+--------------+
| default_pool | 100 | NULL |
| executive | 60 | NULL |
| general | 30 | 30 |
| low_pri | 10 | 20 |
+--------------+----------------+--------------+
For more information, see System Variables, and SHOW RESOURCE POOLS.
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;
You can set MEMORY_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.
The properties of default_pool are fixed at MEMORY_PERCENTAGE = 100% and QUERY_TIMEOUT = 0. These values cannot be changed.
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.
