Managing Plancache Memory and Disk Usage

Compiled query plan images, sometimes referred to as 'modules' or 'code modules', are stored in a plancache for later use. The plancache consists of both an in-memory plancache and an on-disk plancache, also known as the persistent plancache or PPC. Thus the plancache incrementally consumes both memory and disk storage space. This topic addresses managing that memory and storage space. Refer to Code Generation for more information on the plancache.

Compiled query plan images may be removed from the in-memory plancache through expiration or eviction. When a plan expires from the in-memory plan cache (called expiration), the plan is removed fully from the in-memory plancache so that when that plan is used again it must be fully reloaded from disk. When a plan is evicted (called eviction), the plan is removed from the in-memory plancache, but a lightweight entry is left behind, so that the query plan can be reloaded from disk in a much cheaper manner than for an expired plan. That is, an evicted plan is effectively still in the in-memory plancache, while an expired query plan is not.

Whether a plan expires or is evicted from the in-memory plancache, that plan remains in the on-disk plancache and is loaded back into memory the next time the query is run. Evicted plans will re-load more quickly than expired plans, but in neither case will the plan be re-compiled. Further, when a node restarts, the in-memory plancache starts off empty and plans are loaded back in from the on-disk plancache as queries are run.

Query plans are not recompiled after a plan is evicted or expires from the in-memory plancache or after a node restarts.

Query plans expire from the on-disk plancache as described in Set Plan Expiration Limits. When a plan expires from the on-disk plancache, it is completely removed from the plancache and the query must be recompiled when it is run again. In addition, query plans can be manually removed from the in-memory plancache as described in Drop a plan from the in-memory plancache.

Set Plan Expiration Limits

Plancache expiration policy can be set through the plan_expiration_minutes and disk_plan_expiration_minutes engine variables. Expiration for the in-memory and on-disk plancaches is always enabled.

Plans will expire from the on-disk plancache if the time limit specified by disk_plan_expiration_minutes is reached (assuming the plan has not been read from disk during that time). This restriction also applies to temporary (temp) tables. SingleStore recommends re-using the same name for temporary tables to the degree possible.

Note

As engine variables are scoped at the node level (and not across the workspace), if you update any of these plan expiration variables, you must update the values across all nodes in your workspace. See Engine Variables for more information.

The following example shows how to set these variables to change the in-memory expiration limit to eight hours and set the on-disk expiration to one week.

SQL

SHOW VARIABLES LIKE '%plan_expiration%';

+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| disk_plan_expiration_minutes | 20160 |
| enable_disk_plan_expiration  | OFF   |
| plan_expiration_minutes      | 720   |
+------------------------------+-------+
3 rows in set (0.08 sec)

SQL

SET GLOBAL plan_expiration_minutes = 480;

Query OK, 0 rows affected (0.07 sec)

SQL

SET GLOBAL disk_plan_expiration_minutes = 10080;

Query OK, 0 rows affected (0.09 sec)

SQL

SHOW VARIABLES LIKE '%plan_expiration%';

+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| disk_plan_expiration_minutes | 10080 |
| enable_disk_plan_expiration  | ON    |
| plan_expiration_minutes      | 480   |
+------------------------------+-------+
3 rows in set (0.07 sec)

Manage Eviction of Compiled Query Plans

Compiled query plan images (sometimes referred to as 'modules' or 'code modules') are evicted using LRU (least-recently used) when the in-memory plancache reaches a set memory limit. The compiled_images_eviction_memory_limit_percent and compiled_images_eviction_memory_limit_mb variables are used to specify the memory limit available for caching query plan images. Query plan images are evicted when the enable_compiled_images_eviction engine variable is set to ON, which is the default setting.

If the compiled_images_eviction_memory_limit_percent and compiled_images_eviction_memory_limit_mb variables are not set or are set to 0, compiled_images_eviction_memory_limit_percent will be set as a percentage of maximum_memory as shown in the following table.

`maximum_memory` (GB)	`compiled_images_eviction_memory_limit_percent` Value	In-memory Plancache Memory Limit
0-1	Prohibited (`maximum_memory` minimum is 1GB)
1-64	`10`	10% of `maximum_memory`
64+	`5`	5% of `maximum_memory`

If the compiled_images_eviction_memory_limit_mb or compiled_images_eviction_memory_limit_percent variables are set, the memory limit for the in-memory plancache is derived at runtime as shown in the following table.

The notation _mb denotes compiled_images_eviction_memory_limit_mb and _percent denotes compiled_images_eviction_memory_limit_percent.

_percent	_mb	In-memory Plancache Memory Limit
0.0	0	The value is derived in accordance with the default allocation rule specified in the table above.
0.0	> 0	If `_mb` exceeds the `maximum_memory` , a value derived from the above table is used. Otherwise, the value of `_mb` is used as is.
> 0.0	Any value	The value of `_percent` is used.

If the compiled_images_eviction_memory_limit_percent variable is set to a value other than 0.0, the value of compiled_images_eviction_memory_limit_mb is ignored.

Note

Changes to maximum_memory may result in changes in the module cache limit, in accordance with the rules specified above.

Drop a plan from the in-memory plancache

In addition to setting a retention policy for in-memory and on-disk plans, you may also choose to drop plans from the plancache to either compile a new query plan or reduce memory usage. For running nodes, the following three commands can drop a plan from the plancache: DROP … FROM PLANCACHE , ALTER TABLE, and ANALYZE.

DROP ALL FROM PLANCACHE purges all previously compiled plans, both in-memory and on-disk, and generates fresh query plans for any new queries. It purges the plans locally, and not across the workspace.

Successful completion of a new ALTER TABLE will invalidate the plancache for all queries that operate on the affected table, requiring their recompilation

ANALYZEinvalidates stale plans from both the in-memory and the on-disk plancache, including plans which are on-disk but not in-memory. This invalidation ensures that all stale plans are discarded and that they are recompiled the next time the query is run.

Note

The ANALYZE command invalidates plans by marking them as invalid so they won’t be used in the future, but it does not actually delete all the files in the on-disk plancache. The files will still be on disk, so it is not possible to reduce disk usage of the on-disk plancache with this command. To delete plans which are only on-disk (other than waiting for them to expire using disk_plan_expiration_minutes), manually delete the files from the plancache directory. To purge all plans, both in-memory and on-disk, use the DROP ALL FROM PLANCACHE command.

Managing Plancache Memory and Disk Usage

On this page

Set Plan Expiration Limits

Manage Eviction of Compiled Query Plans

Drop a plan from the in-memory plancache

Was this article helpful?

On this page

Was this article helpful?