Distributed Plancache

Overview

When the DPC is enabled, plans are synchronized automatically between nodes. That is, child aggregators and leaf nodes automatically download plans from the DPC into their local PPC. Once a plan is downloaded, the node can use that plan and avoid query optimization, code generation, and plan compilation in many cases.

Similar to how the PPC functions, a plan downloaded to the DPC is usable except when:

• A variable affecting the plan has changed in the node.

• A table in a query has changed significantly (for example, the number of rows in the table has changed by a factor of two times or more) since the plan was generated.

In addition to downloading plans from the DPC, nodes also upload plans to the DPC to make those plans available to other nodes. Similarly, plans can be deleted from the DPC to indicate to other nodes that those plans can be deleted.

DPC operations do not interfere with regular cluster operations. While the DPC may consume CPU, memory, and network resources, all operations happen in the background and do not interfere with regular query execution.

The DPC operates on a best-effort basis and does not guarantee that plan compilation will not occur on a new node.

Remarks

• DPC requires that the cluster have Unlimited Storage enabled. DPC utilizes unlimited storage to store plancache files.

• DPC only stores plans generated during MBC, LLVM, and INTERPRET_FIRST interpreter modes.

• Plan synchronization is not supported on the Master Aggregator.

Enable and Manage the Distributed Plancache

SELECT @@enable_distributed_plancache;

 
+--------------------------------+
| @@enable_distributed_plancache |
+--------------------------------+
|                              1 |
+--------------------------------+

Manage the Distributed Plancache

When the DPC is enabled, nodes automatically download, upload, and delete plans from the DPC.

Nodes download plans from the DPC to their local PPC when the cluster scales, is rebalanced, or a new node is added to the cluster. Nodes delete plans from the DPC when those plans are explicitly deleted from their local PPC with DROP … FROM PLANCACHE.

The download, upload, delete, and synchronization operations occur in the background and are managed by the DPC task queue.

Aggregator Synchronization

Child aggregators can be configured to automatically synchronize their local PPCs with the DPC. This process is called aggregator synchronization and is useful for clusters with multiple aggregators.

The following diagram shows two child aggregators with different in-memory and disk plans synchronizing through the Distributed Plancache after the configured aggregator synchronization interval.

When enable_periodic_distributed_plancache_agg_sync is set to ON, at the interval specified by distributed_plancache_agg_sync_s, each aggregator performs an aggregator synchronization operation and downloads the most recently used distributed_plancache_max_download_plans query plans from the DPC.

Asynchronous DPC Lookup

With asynchronous DPC lookup, query execution looks for plans in the DPC during query optimization and Code Generation. Asynchronous lookup reduces query optimization and compilation time for complex queries and reduces the load on the CPU.

Without the Distributed Plancache, each node compiles the query plan locally, even when the same query has already been compiled on another node

The following diagram shows query-plan execution without the Distributed Plancache: a query sent to different aggregators is compiled locally on each node instead of being reused across the cluster.

The following diagram shows asynchronous DPC lookup: the query checks memory and the persistent plancache, then a background thread checks the Distributed Plancache while optimization and code generation proceed.

This behavior is controlled by the session variable enable_dpc_lookup.

SQL

Copy

SET SESSION enable_dpc_lookup = ON;

When there is a PPC miss and the following are true, a thread is launched to lookup and download the plan from the DPC:

• enable_dpc_lookup is enabled.

• enable_synchronous_dpc_lookup is not enabled.

• The interpreter mode is set to interpret_first.

Assuming the plan exists in the DPC, there are three different scenarios:

A: Download from the DPC completes before query optimization and code generation complete. The plan from the DPC is loaded into the node's local PPC. The code generation and query optimization are cancelled, and query execution uses the LLVM plan downloaded from the DPC and loaded to the node’s local PPC. No local asynchronous compilation is launched.

B: Download from the DPC completes after query optimization and code generation complete, but before local asynchronous compilation is launched. Query execution uses the locally-generated MBC plan for the initial execution. Once the execution is complete, the download thread replaces the PPC files with the plan from the DPC, and drops the original MBC plan from the plancache. Subsequent execution uses the LLVM plan downloaded from the DPC. No local asynchronous compilation is launched.

C: Download from the DPC completes after local asynchronous compilation has launched. Local asynchronous compilation runs to completion and the node uses the locally compiled plan for future execution. The plan downloaded from the DPC is discarded and the newly compiled plan is asynchronously uploaded to the DPC.

Complex queries (for example, large queries with many joins) whose plans are in the DPC often result in Scenario A. When asynchronous compilation is backed up, small to medium queries often result in Scenario B. In Scenarios A and B, local compilation is avoided, reducing the load on the CPU.

The engine variable max_dpc_lookup_threads controls the maximum concurrent download threads for asynchronous DPC lookups.

Without the Distributed Plancache, each node compiles the query plan locally, even when the same query has already been compiled on another node.

Synchronous DPC Lookup

With synchronous DPC lookup, query execution looks to the DPC before starting query optimization, and Code Generation. When a plan exists in the DPC, this synchronous lookup process is typically faster than local compilation, optimization, and code generation for large, complex queries.

In contrast to asynchronous lookup, synchronous lookup does not launch a separate thread for the DPC lookup; instead synchronous lookup waits until after the DPC lookup has completed before launching local compilation, optimization, and code generation. SingleStore recommends using asynchronous lookup for most scenarios as synchronous lookup may add latency.

If it is known that a query will take a long time for optimization and code generation and it is expected that the query plan will be in the DPC, then synchronous lookup is the better option.

Syncronous DPC lookup is enabled when enable_synchronous_dpc_lookup is set to ON on aggregator nodes.

The following diagram shows synchronous DPC lookup path showing plan lookup through memory, PPC, and DPC before compilation.

Engine Variables

The following engine variables are used to manage the DPC:

Name	Description
distributed_plancache_worker_threads	Specifies the number of threads used to process tasks in the task queue for the DPC.
distributed_plancache_max_download_plans	Specifies the maximum number of plans downloaded in a synchronization task for the DPC.
distributed_plancache_agg_sync_s	Specifies the interval between aggregators' periodic synchronizations for the DPC.
enable_periodic_distributed_plancache_agg_sync	Specifies if aggregators periodically synchronize their local PPC with the DPC.
enable_dpc_lookup	A session variable that enables asynchronous DPC lookups.
enable_synchronous_dpc_lookup	A session variable that specifies that nodes look for a plan in the DPC when a plan is not found in the node's local PPC.
max_dpc_lookup_threads	Controls the maximum concurrent download threads for asynchronous DPC lookups.

Refer to List of Engine Variables for default values.

Resume and Recovery Behavior

Restoring Child-Aggregator Query Plans after Resume

When a cluster resumes or recovers, child aggregators can restore query plans from the Distributed Plancache (DPC) instead of recompiling them locally.

During recovery, DPC download tasks are queued after the global variables are synchronized. A subsequent task is queued when a reference database is created. If a matching plan is available in the DPC, the child aggregator downloads the plan to its local persistent plancache (PPC). If the plan is not available or cannot be downloaded, the child aggregator compiles the plan locally.

This behavior uses the existing enable_distributed_plancache setting. It does not introduce new settings or change the default.

When troubleshooting resume or recovery performance, use SHOW DISTRIBUTED_PLANCACHE STATUS and the DPC queue status variables to determine whether plan downloads completed or whether plans were recompiled locally.

Plan-file Organization and Database-name Handling

DPC plan-file organization in object storage and database-name handling use the normalized database name. When troubleshooting missing plan downloads or inspecting DPC plan-file paths, use the normalized database name when correlating the database with its stored plans and with leaf-node DPC requests.

These changes preserve DPC functionality and do not add new configuration settings.

Observe Distributed Plancache Statistics

Connect to a node and use the SHOW DISTRIBUTED_PLANCACHE STATUS command to observe statistics about the DPC on a specific node, as follows.

SHOW DISTRIBUTED_PLANCACHE STATUS;

+-------------------------------------------------------+-------+
| Stat                                                  | Value |
+-------------------------------------------------------+-------+
| Successful Downloads Since Startup                    | 128   |
| Skipped Downloads Since Startup                       | 110   |
| Failed Downloads Since Startup                        | 0     |
| Plans Uploaded Since Startup                          | 2     |
| Plans Deleted Since Startup                           | 0     |
| DB Synchronization Since Startup                      | 1     |
| Successful Downloads From Periodic Sync Since Startup | 12    |
| Skipped Downloads From Periodic Sync Since Startup    | 107   |
| Failed Downloads From Periodic Sync Since Startup     | 0     |
| Periodic Query Plan Syncs Since Startup               | 1     |
| Distributed Plancache Plans Used Since Startup         | 90    |
| Currently Queued Populate Download Tasks              | 0     |
| Currently Queued Download Tasks                       | 0     |
| Currently Queued Upload Tasks                         | 0     |
| Currently Queued Delete Tasks                         | 0     |
| Avg Plan Download Latency (ms)                        | 13    |
| Avg Plan Upload Latency (ms)                          | 38    |
| Avg Plan Delete Latency (ms)                          | 0     |
| Avg Duration For DB Plan Synchronization (ms)         | 1710  |
+-------------------------------------------------------+-------+

Note: Failed Downloads indicate that the node tried to download a plan file from the DPC, but failed to do so typically because the plan did not exist in the DPC. Skipped Downloads indicate that the node tried to download the plan from the DPC, but the plan already existed in the local PPC.

Use the following commands to view the number of tasks in the DPC task queue.

SHOW STATUS LIKE 'Queued_DPC_Uploads';
SHOW STATUS LIKE 'Queued_DPC_Downloads';
SHOW STATUS LIKE 'Queued_DPC_PopulateDownloads';
SHOW STATUS LIKE 'Queued_DPC_Deletes';

If the values of these metrics increase significantly and the number of compilations rises due to slow plan synchronization, SingleStore recommends considering an increase in the DPC worker thread pool size (distributed_plancache_worker_threads).

Use DPC with a New Cluster

Use the following steps to use the DPC for a specific database in a new cluster. Complete steps 1, 2, and 3 before creating the new cluster.

1. Enable the distributed plancache. This command enables the distributed plancache for the entire cluster group.

   SQL

   Copy

   SET GLOBAL enable_distributed_plancache = ON;

2. The plans that need to be downloaded to the new cluster must exist in the DPC, that is, those plans must be compiled locally in any of the nodes after enable_distributed_plancache was turned on.

   1. This can be achieved by either removing and recompiling that plan, or letting the cluster run for a while so the nodes naturally recompile the plan over time.

3. Set distributed_plancache_max_download_plans to the number of plans that is expected for the database (default 1000).

   SQL

   Copy

   SET distributed_plancache_max_download_plans = <number_of_expected_plans>;

4. Create a new cluster, attach the database to the new cluster.

5. The plans automatically sync after step 4 has been completed.

6. If more plans need to be synchronized explicitly or if the value set in step 3 was too small, run _SYNC_PLANS <database name> on the aggregator node to download the most recently used query plans for that database again (plans that already exist in the DPC are skipped).

Plancache Manager

The Plancache Manager (PCM) is an external service that stores metadata about plans stored in a cluster's plancache. During fast scale and suspend and resume operations, the cluster retrieves plan metadata from the PCM. Plans marked as stored in the DPC are retrieved from the DPC. These plans do not need to be re-compiled, allowing the cluster to be automatically warmed up with most recently used ("hot") plans after fast scale and suspend and resume operations.

A cluster with DPC enabled automatically uses the PCM.