Select a Resource Pool Dynamically
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(64) 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(64) or VARCHAR(64) 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 Security Models Used by Procedural Extensions.
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.