Query Plan Pinning

SingleStore now supports query plan pinning. Plan pinning ensures the optimizer will always pick a specific plan for a given query. Plan pinning allows users to select the most effective query plans for every query in their workload.

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

  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.

    BOOTSTRAP PLAN PIN DATABASE;
    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.

    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.

    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.

    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:

    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. 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

Pins and the Plancache

The OPTIMIZER_NOTES field in the information_schema.PLANCACHE view now contains information about how plan pinning was used when creating the plancache entry. These new fields are:

  • pinned_plan - set to 1 if a pin was written to the configured storage based on the compiled result

  • used_pinned_plan - set to 1 if a lookup was made to the plan pinning service and an activated pin was found matching the given query

In addition, these plan features can be viewed (if any exist) in a plan_features JSON key. This is formatted as a list of JSON objects detailing the relevant feature. These objects are present under the plan_features key in the OPTIMIZER_NOTES field and have the following properties:

  • table_name: the table the feature applies to

  • feature_type: the type of feature

  • 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. In the plan features JSON key, the feature_type key would display the “Unique Key” value and the identifier key would contain the column(s) of the key. Generally, a plan feature is a property of a table in the plan that is necessary for the correctness or execution of the pinned plan. This means that a table with a unique key constraint may not necessarily have that key included as a feature for all related plans; it will only be included if those plans require it to be runnable.

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_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 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. Run the SHOW WARNINGS command to view them.

Continuous PIN Storage

The PIN ALL FROM PLANCACHE command will apply only to existing queries. To set up your cluster to pin all plans continuously (including new queries), set the pin_all_plans 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. The default value for pin_all_plans 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

Was this article helpful?