Setting Resource Limits

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.

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

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;
Info

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 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>;
    
Info

Resource pools are defined on the master aggregator; however, as with some 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. 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  |
+---------------+------------+
1 row in set (0.00 sec)
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 System 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.

Info

The properties of default_pool are fixed at MEMORY_PERCENTAGE = 100%, SOFT_CPU_LIMIT_PERCENTAGE = 100%,QUERY_TIMEOUT = 0 and MAX_CONCURRENCY = 0. These values cannot be changed.

For more information, see ALTER RESOURCE POOL.

Query execution behavior

Queries will be queued if insufficient memory is available in their pool for them to start as long as their query execution memory requirement is within the memory limit. Query queueing is done between resource pools, not within resource pools themselves. 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 |            |                       |
+--------------------+--------+-------------+------------+-----------------------+
2 rows in set (0.00 sec)

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               |
+--------------+------+-------------+------------+---------------------+-----------------------+
2 rows in set (0.00 sec)

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

Was this article useful?