Query Plan Pinning

Setting Up Query Plan Pins

1. Initial and configure storage for query plan pins

   Before plan pins can be used, storage must be initialized and configured for use. This process begins by running the command BOOTSTRAP PLAN PIN DATABASE, which requires root permissions. This creates a database s2_plan_metadata and then populates it with various stored procedures and tables. Please note that while this is a “user” database that can be managed like any other SingleStore database, negative side effects can occur if its contents are modified directly by end users.

   SQL

   Copy

   BOOTSTRAP PLAN PIN DATABASE;

   SQL

   Copy

   SHOW DATABASES;

   +--------------------+
   | Database           |
   +--------------------+
   | cluster            |
   | information_schema |
   | memsql             |
   | s2_plan_metadata   |
   +--------------------+

2. Create a link to interact with the plan pinning database

   The cluster (or multiple clusters) must be configured with credentials to connect using the CREATE LINK syntax, creating a new link with the name cluster.plan_pinning_link. Multiple SingleStore clusters can interact with the same plan pinning database and share metadata.

   SQL

   Copy

   CREATE LINK cluster.plan_pinning_link AS MYSQL 
   CONFIG '{
       "host": "<hostname>","port":<port>}' 
   CREDENTIALS '{
       "username":"<username>","password":"<password>"}';

3. Enable plan pinning

   The local database that stores pins is created by setting the engine variable enable_plan_pinning to ON. If this engine variable remains in its default value of OFF, no plan pinning actions will take place.

   SQL

   Copy

   SET GLOBAL enable_plan_pinning = ON;

4. Creating pins

   Pins are created by executing the PIN statement with the designated query text, either from the plancache for all queries, a specific plan_id and query text, or for all existing and new queries in the plancache. The PIN syntax can also be used to overwrite existing pins on a given query by using the OVERWRITE argument.

   SQL

   Copy

   PIN [OVERWRITE] <query_text>;
   PIN [OVERWRITE] <plan_id> FROM PLANCACHE;
   PIN [OVERWRITE] ALL FROM PLANCACHE;
   PIN [OVERWRITE] <plan_id> [ACTIVATE|DEACTIVATE] FROM PLANCACHE FOR QUERY <query_text>;
   SET GLOBAL pin_all_plans = 'AUTO';

5. Using pins

   Setting the following engine variable to AUTO will make the optimizer use plan pins that have been created:

   SQL

   Copy

   SET SESSION use_plan_pins = AUTO;
   SET GLOBAL use_plan_pins = AUTO;

   Individual query plan pins are activated by running the ACTIVATE PIN statement with the specific query text. Conversely, individual query plan pins can be deactivated by running the DEACTIVATE PIN statement with the specific query text.

   SQL

   Copy

   ACTIVATE PIN <query_text>;
   DEACTIVATE PIN <query_text>;

Pin-Matching Criteria

While identical (parametrized) queries across identical tables are supported for the purposes of pinning, the pin matching checks are more sophisticated and allow for tables to be modified and even replaced while minimizing the loss of previously pinned plans.

The criteria to ensure a pin match are as follows:

• The parameterized query text of the original query must match.

• The same column names from the same table must be referenced in the pinned query.

• The types of these columns must remain consistent.

• Shard keys and table types of reference tables should remain unchanged.

• Relevant table features present during pin creation must still exist.

Despite the complexity of these criteria, they ensure that many common management operations do not impede the use of work invested in plan pins. These operations include:

• Dropping and recreating a table

• Adding new columns to the table

• Adding new indexes to a table

However, certain SingleStore features are crucial for specific plans, and changes to these features may render pins unusable:

• Projections - If a projection of a base table is used in the pinned plan, it must still exist when the pin is loaded for it to be used.

• Unique keys - Removing a unique key, including the primary key, from a table will make previously existing pins using the table unusable.

• Computed columns - While removing a computed column explicitly referenced in a query will prevent pin reuse, if a computed column is matched within the query and replaced in the pinned plan, the column becomes a relevant feature.

Understanding Plan Pinning State

SHOW PIN FOR ALL;
SHOW PIN FOR QUERY <query_text>;
SHOW PIN FOR TABLE <table_name>;
SHOW PIN FOR DATABASE <database_name>;

Engine Variables Used for Query Plan Pinning

• enable_plan_pinning - The available values are OFF and ON. The default value is OFF.  If set to OFF, no plan pinning actions will take place.

• pin_all_plans - The available values are AUTO, OFF, ACTIVATED, and DEACTIVATED. The default value is OFF. If set to ACTIVATED, all new plans are pinned in the activated state. If set to DEACTIVATED, all new plans are pinned in the deactivated state. If set to OFF, no plans are pinned automatically. AUTO is the same as ACTIVATED.

• plan_pin_read_timeout_ms - This is used to enforce a maximum latency penalty that can be incurred when the server looks for a plan pin before proceeding with compiling a new plan. This behavior would become relevant if the cluster hosting the plan pins became severely overloaded or experienced networking issues. For commands that need to read pins by explicit user action; such as, ACTIVATE PIN and DEACTIVATE PIN, usual timeouts are used instead.

• plan_pin_write_timeout_ms - This is used to enforce a maximum latency penalty that can be incurred when the server writes a plan pin during compilation. This is only relevant for the case when a new query is being compiled without the presence of a pin, and defines the worst-case additional latency when pin_all_plans is set to be enabled. For commands that need to write pins by explicit user action; such as ACTIVATE PIN and DEACTIVATE PIN, usual timeouts are used instead.

• use_plan_pins - The available values are AUTO, TRUE, and FALSE. The default value is OFF.  When set to TRUE, if the plan_pin_id is not found in memory or on disk, the optimizer will search for the plan in the pinning database. If set to FALSE, the optimizer will not look in the plan_pin_id in the pinning database. AUTO is the same as TRUE.

Examples

Using a sample customer rewards table based on birth months and ages to illustrate how to set pins for various use cases.

CREATE DATABASE test1;
USE test1;

CREATE TABLE BD_Rewards (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    BirthMonth INT NOT NULL,
    BirthDay INT NOT NULL,
    Age INT,
    SORT KEY (Age)	
);

The following queries provide a list of names whose birthdays fall in the same month and a list of names of people whose ages are within a certain range.

SELECT FirstName FROM BD_Rewards WHERE BirthMonth = 1;

SELECT FirstName FROM BD_Rewards WHERE Age BETWEEN 18 and 25;

The query plans created by executing these queries can be pinning in the following methods.

 PIN (SELECT FirstName FROM BD_Rewards WHERE BirthMonth = 1);

+---------------------------------------------------------------------------------------+
| PIN                                                                                   |
+---------------------------------------------------------------------------------------+
| ROOT                                                                                  |
| GATHER: `BD_Rewards`.`FirstName` AS FirstName                                         |
| FILTER: (`BD_Rewards`.`BirthMonth` = @_0)                                             |
| TABLE: test1.BD_Rewards AS BD_Rewards, 3 INDICES: (PRIMARY, BirthMonth, __SHARDKEY, ) |
+---------------------------------------------------------------------------------------+

SHOW PLANCACHE;

PIN 24 FROM PLANCACHE;

+---------------------------------------------------------------------------------------+
| PIN                                                                                   |
+---------------------------------------------------------------------------------------+
| ROOT                                                                                  |
| GATHER: `BD_Rewards`.`FirstName` AS FirstName                                         |
| FILTER: (between `BD_Rewards`.`Age` @_0 @_1)                                          |
| TABLE: test1.BD_Rewards AS BD_Rewards, 3 INDICES: (PRIMARY, BirthMonth, __SHARDKEY, ) |
+---------------------------------------------------------------------------------------+

PIN ALL FROM PLANCACHE;

Query OK, 0 rows affected, 42 warnings 

SET GLOBAL pin_all_plans = 'AUTO';

SET SESSION pin_all_plans = AUTO; 

SET SESSION pin_all_plans = OFF; 

ACTIVATE PIN SELECT FirstName, LastName FROM BD_Rewards WHERE Age >= 65;

DEACTIVATE PIN SELECT FirstName, LastName FROM BD_Rewards WHERE Age <= 20;

DROP PIN FOR QUERY SELECT FirstName FROM BD_Rewards WHERE BirthMonth = 1;

DROP PIN FOR TABLE BD_Rewards;

DROP PIN FOR DATABASE test1;

DROP PIN FOR ALL;