Select a Resource Pool Dynamically

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(n) CHARACTER SET utf8 COLLATE utf8_general_ci AS
-- In VARCHAR(n), n can have a maximum length of 256 characters.
  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(n) or VARCHAR(n) 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.

Note

At this time, ESTIMATED_QUERY_LEAF_MEMORY() and ESTIMATED_QUERY_RUNTIME() are experimental functions. They are not formally supported. However, feedback and suggestions are welcome.

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. Functions use the Definer Security Model, which is explained in Procedural Extensions Security.

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

Note

The examples use VACHAR(64), it can be any value, up to the maximum limit of a resource pool name length which is 256 characters.

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 page Work with Resource Pool Engine Variables.

SQL

DROP DATABASE IF EXISTS singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
USE singlestore_docs_example;

DELIMITER //
CREATE FUNCTION singlestore_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 = 'singlestore_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.

SQL

DROP DATABASE IF EXISTS singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
USE singlestore_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() = 'singlestore_docs_example' THEN
      RETURN 'training';
    ELSE
      RETURN 'main';
    END IF;
  END //
DELIMITER ;

SET resource_pool = system_auto;

SET GLOBAL resource_pool_statement_selector_function = 'singlestore_docs_example.select_pool';

/* The following query uses the training resource pool returned from the
select_pool UDF, since singlestore_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 singlestore_docs_example does not make
singlestore_docs_example the current database. */

USE information_schema;
SELECT a FROM singlestore_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.

SQL

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 singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
USE singlestore_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 = 'singlestore_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:

SQL

SELECT 1, CURRENT_RESOURCE_POOL();

+---+-------------------------+
| 1 | CURRENT_RESOURCE_POOL() |
+---+-------------------------+
| 1 | default_pool            |
+---+-------------------------+

Collect statistics on the query:

SQL

ANALYZE MEMORY;

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

SQL

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_QUERY_RUNTIME 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:

SQL

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 singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
USE singlestore_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 = 'singlestore_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:

SQL

SELECT SLEEP(0.5), CURRENT_RESOURCE_POOL();

+------------+-------------------------+
| SLEEP(0.5) | CURRENT_RESOURCE_POOL() |
+------------+-------------------------+
|          0 | default_pool            |
+------------+-------------------------+

SQL

SELECT SLEEP(1.5), CURRENT_RESOURCE_POOL();

+------------+-------------------------+
| SLEEP(1.5) | CURRENT_RESOURCE_POOL() |
+------------+-------------------------+
|          0 | default_pool            |
+------------+-------------------------+

SQL

SELECT SLEEP(3.0), CURRENT_RESOURCE_POOL();

+------------+-------------------------+
| SLEEP(3.0) | CURRENT_RESOURCE_POOL() |
+------------+-------------------------+
|          0 | default_pool            |
+------------+-------------------------+

Collect statistics on the queries:

SQL

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:

SQL

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.

SQL

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:

SQL

SELECT SLEEP(3.0), CURRENT_RESOURCE_POOL();

+------------+-------------------------+
| SLEEP(3.0) | CURRENT_RESOURCE_POOL() |
+------------+-------------------------+
|          0 | high_run_time           |
+------------+-------------------------+

For more information, see ALTER USER.

Select a Resource Pool Dynamically

On this page

Enabling Dynamic Resource Pool Selection

Registering and Defining the Resource Pool Selector Function

Resource Pool Selector Function Built-Ins that Provide Estimates

Disabling Dynamic Resource Pool Selection

Selector Function Security

Example UDFs that Select Resource Pools

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

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

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

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

Was this article helpful?

On this page

Was this article helpful?