Administer Default Resource Pools

The default resource pool of a user is the resource_pool value that will be set when a user first connects to SingleStoreDB 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.

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

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

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