Query Plan Pinning
On this page
Note
Query Plan Pinning is a preview feature that must be enabled for your workspace(s).
Please contact Support to enable this feature.
SingleStore now supports query plan pinning.
Query plan pinning helps to:
-
Preventing query plan regression after automatic upgrades.
-
Avoiding changes to query plans after statistics change or global settings change that affect query plans.
-
Avoiding changes to query plans when a cluster is resized.
Setting Up Query Plan Pins
-
Initial and configure storage for query plan pins
Please contact our support team to facilitate the initializing and configuring storage needed for query pin planning.
-
Create a link to interact with the plan pinning database
Please contact our support team to facilitate the creation of the link to interact with the plan pinning database.
-
Enable plan pinning
The local database that stores pins is created by setting the engine variable
enable_
toplan_ pinning ON
.If this engine variable remains in its default value of OFF
, no plan pinning actions will take place.SET GLOBAL enable_plan_pinning = ON; -
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 theOVERWRITE
argument.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'; -
Using pins
Setting the following engine variable to
AUTO
will make the optimizer use plan pins that have been created: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.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.
-
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
Pins and the Plancache
The OPTIMIZER_
field in the information_
view now contains information about how plan pinning was used when creating the plancache entry.
-
pinned_
- set to 1 if a pin was written to the configured storage based on the compiled resultplan -
used_
- set to 1 if a lookup was made to the plan pinning service and an activated pin was found matching the given querypinned_ plan
In addition, these plan features can be viewed (if any exist) in a plan_
JSON key.plan_
key in the OPTIMIZER_
field and have the following properties:
-
table_
: the table the feature applies toname -
feature_
: the type of featuretype -
identifier
: a type-specific means of identifying what the feature’s required value is -
hash
: a version of the identifier used internally
For example, a query may require a unique key constraint exists.feature_
key would display the “Unique Key” value and the identifier
key would contain the column(s) of the key.
Commands to View Pinning State
To view the current pinning state, run any of the following commands:
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_
- The available values areplan_ pinning OFF
andON
.The default value is OFF
.If set to OFF
, no plan pinning actions will take place. -
pin_
- The available values areall_ plans AUTO
,OFF
,ACTIVATED
, andDEACTIVATED
.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 asACTIVATED
. -
plan_
- 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.pin_ read_ timeout_ ms 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
andDEACTIVATE PIN
, usual timeouts are used instead. -
plan_
- This is used to enforce a maximum latency penalty that can be incurred when the server writes a plan pin during compilation.pin_ write_ timeout_ ms 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_
is set to be enabled.all_ plans For commands that need to write pins by explicit user action; such as ACTIVATE PIN
andDEACTIVATE PIN
, usual timeouts are used instead. -
use_
- The available values areplan_ pins AUTO
,TRUE
, andFALSE
.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 asTRUE
.
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 a Plan by Query Text
A plan can be pinned using the literal query text:
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, ) |
+---------------------------------------------------------------------------------------+
Pin a Plan by Plan ID
First, obtain the plan id for the query you want to pin:
SHOW PLANCACHE;
Then, pin the plan using the following command:
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 Plans in the Current In-memory Plan Cache
All plans in the plancache can be pinned with the following command:
PIN ALL FROM PLANCACHE;
Query OK, 0 rows affected, 42 warnings
If there are plans that are not pinnable or already pinned, a warning message will be displayed.SHOW WARNINGS
command to view them.
Continuous PIN Storage
The PIN ALL FROM PLANCACHE
command will apply only to existing queries.pin_
engine variable to AUTO
:
SET GLOBAL pin_all_plans = 'AUTO';
Per Session PIN Enabling/Disabling
If you want to enable/disable pin per current session, run the following commands respectively.pin_
is OFF
:
SET SESSION pin_all_plans = AUTO;
SET SESSION pin_all_plans = OFF;
Activate PIN on Query
If you need to load a pin upon running a query, while also needing to disregard a deactivated pin you can activate pins on individual queries:
ACTIVATE PIN SELECT FirstName, LastName FROM BD_Rewards WHERE Age >= 65;
Deactivate PIN on Query
If your cluster is set up for all queries in your plancache to be pinned but you want to deactivate pins on an individual query, you can deactivate pins on individual queries:
DEACTIVATE PIN SELECT FirstName, LastName FROM BD_Rewards WHERE Age <= 20;
Drop PINS from Query, Table, Database, or All
If you want to drop pins and not just deactivate them, the following commands can be run based if you want to drop by table, database, or all pins on your cluster:
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;
These commands will not return how many pins were dropped.
Last modified: July 12, 2024