Sign inTry Free

Select a Resource Pool Dynamically

On this page

You can use a user-defined scalar function (UDF) to select the resource pool that an existing or new client connection uses.

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

  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

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.

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.

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.

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:

SELECT 1, CURRENT_RESOURCE_POOL();
+---+-------------------------+
| 1 | CURRENT_RESOURCE_POOL() |
+---+-------------------------+
| 1 | default_pool            |
+---+-------------------------+

Collect statistics on the query:

ANALYZE MEMORY;

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

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:

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:

SELECT SLEEP(0.5), CURRENT_RESOURCE_POOL();
+------------+-------------------------+
| SLEEP(0.5) | CURRENT_RESOURCE_POOL() |
+------------+-------------------------+
|          0 | default_pool            |
+------------+-------------------------+
SELECT SLEEP(1.5), CURRENT_RESOURCE_POOL();
+------------+-------------------------+
| SLEEP(1.5) | CURRENT_RESOURCE_POOL() |
+------------+-------------------------+
|          0 | default_pool            |
+------------+-------------------------+
SELECT SLEEP(3.0), CURRENT_RESOURCE_POOL();
+------------+-------------------------+
| SLEEP(3.0) | CURRENT_RESOURCE_POOL() |
+------------+-------------------------+
|          0 | default_pool            |
+------------+-------------------------+

Collect statistics on the queries:

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:

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.

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:

SELECT SLEEP(3.0), CURRENT_RESOURCE_POOL();
+------------+-------------------------+
| SLEEP(3.0) | CURRENT_RESOURCE_POOL() |
+------------+-------------------------+
|          0 | high_run_time           |
+------------+-------------------------+

For more information, see ALTER USER.

Last modified: August 21, 2024

Was this article helpful?

On this page

Was this article helpful?

Verification instructions

Note: You must install cosign to verify the authenticity of the SingleStore file.

Use the following steps to verify the authenticity of singlestoredb-server, singlestoredb-toolbox, singlestoredb-studio, and singlestore-client SingleStore files that have been downloaded.

You may perform the following steps on any computer that can run cosign, such as the main deployment host of the cluster.

  1. (Optional) Run the following command to view the associated signature files.

    curl undefined

  2. Download the signature file from the SingleStore release server.

    • Option 1: Click the Download Signature button next to the SingleStore file.

    • Option 2: Copy and paste the following URL into the address bar of your browser and save the signature file.

    • Option 3: Run the following command to download the signature file.

      curl -O undefined

  3. After the signature file has been downloaded, run the following command to verify the authenticity of the SingleStore file.

    echo -n undefined |
        cosign verify-blob --certificate-oidc-issuer https://oidc.eks.us-east-1.amazonaws.com/id/CCDCDBA1379A5596AB5B2E46DCA385BC \
          --certificate-identity https://kubernetes.io/namespaces/freya-production/serviceaccounts/job-worker \
          --bundle undefined \
          --new-bundle-format -
    Verified OK