Enable the Distributed Plancache

The enable_distributed_plancache engine variable controls the DPC.

Set the enable_distributed_plancache global engine variable to ON to enable the DPC.

SET GLOBAL enable_distributed_plancache = ON;

Use the following command to verify the DPC is enabled.

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.

SET GLOBAL enable_periodic_distributed_plancache_agg_sync = ON;

SET SESSION enable_synchronous_dpc_lookup = ON;

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

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

Clear the DPC Queue

CLEAR DISTRIBUTED_PLANCACHE QUEUE;

Synchronize the DPC Manually with _SYNC_PLANS

To synchronize a node's local PPC with the DPC for a specific database, run the following command.

_SYNC_PLANS <dbName>;

This command will download up to distributed_plancache_max_download_plans from the DPC to the node's local PPC for the specified database.

The _SYNC_PLANS command first downloads one plan per table in the database. For databases with large numbers of tables, SingleStore recommends increasing the value of distributed_plancache_max_download_plans to a value greater than the number of tables in the database before running the command.

When this command is run, a synchronization task will be queued regardless of whether enable_distributed_plancache is set to ON or OFF. If the request is enqueued while enable_distributed_plancache is set to OFF, the task will run when enable_distributed_plancache is set to ON.

Example 1: Improve Performance after Scaling and Cold-Starts

The DPC can improve performance for scaling and cold starts, which is useful when scaling or adding aggregator nodes occurs regularly as a part of normal operations.

Run the following command to enable the DPC.

SET GLOBAL enable_distributed_plancache = ON;

With enable_distributed_plancache set to ON, when a database is scaled or aggregator nodes are added, plans for all databases relevant to the new node(s) are automatically synchronized.

This synchronization process downloads distributed_plancache_max_download_plans. For databases with a large number of tables, SingleStore recommends setting distributed_plancache_max_download_plans to a value greater than the number of tables in the database before running this command.

Run the following queries to track the status of synchronization.

SHOW DISTRIBUTED_PLANCACHE STATUS;
SHOW STATUS LIKE 'Queued_DPC_PopulateDownloads';
SHOW STATUS LIKE 'Queued_DPC_Downloads';

When the values of both Queued_DPC_PopulateDownloads and Queue_DPC_Downloads are zero, all download tasks from the DPC have finished.

Example 2: Many Child Aggregators

The DPC can improve first-time query performance when there are many child aggregators and the query shapes are heterogeneous. To avoid aggregator nodes repeatedly compiling the same plan, run the following on all aggregator nodes:

SET GLOBAL enable_periodic_distributed_plancache_agg_sync = ON;
SET GLOBAL distributed_plancache_agg_sync_s = <sync_seconds>;

The value <sync_seconds> must be at least 30 seconds. For clusters with many aggregators, SingleStore recommends setting this value between 60 seconds and 10 minutes.

These settings ensure that aggregator nodes periodically download plans from the DPC that they do not have in their local PPC.

Another option for improving performance in clusters with many child aggregators is to turn on the synchronous DPC lookup using the following command.

SET SESSION enable_synchronous_dpc_lookup = TRUE;

When the enable_synchronous_dpc_lookup engine variable is set to TRUE and a node detects that a query plan is not in its local PPC, the node will look for a plan in the DPC before compiling the query plan.

Consider a query being run on many aggregators. The first aggregator to receive the query looks (and misses) in its local PPC and the DPC; the node then compiles the plan and uploads it to the DPC. When the query is run on other nodes, those nodes miss in their local PPC, but find the plan in the DPC and use that plan to avoid compilation.

The enable_synchronous_dpc_lookup engine variable is a session variable and enables this lookup only for the current session.

Example 3: Database with a Large Number of Query Plans

The DPC can improve performance when a specific database has a disproportionately large number of query plans compared to the other databases.

To download plans from the DPC to the PPC for a specific node, run the following commands on that node.

SET GLOBAL distributed_plancache_max_download_plans = <max_plans_to_download>;
_SYNC_PLANS <dbname>;

To download all query plans for the database, set the value <max_plans_to_download> to the number of tables in the database plus the number of plans in the database.