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 following three conditions are met: 

• The time limit specified by disk_plan_expiration_minutes is reached (assuming the plan has not been read from disk during that time), and

• The plan is not in the in-memory plancache.

This process also applies to temporary (temp) tables. SingleStore recommends reusing the same name for temporary tables to the degree possible.

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.

SHOW VARIABLES LIKE '%plan_expiration%';

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

SET GLOBAL plan_expiration_minutes = 480;

Query OK, 0 rows affected (0.07 sec)

SET GLOBAL disk_plan_expiration_minutes = 10080;

Query OK, 0 rows affected (0.09 sec)

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.

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.

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.

Drop Plans from the 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.

Successful completion of a new ALTER TABLE will invalidate the plancache for all queries that operate on the affected table, requiring their recompilation. The invalidation of the plancaches happens asynchronously. That is, new queries will compile and use new query plans, while currently running queries will use the old query plans. Once no queries are using the old query plans, they are marked as ready to be purged by the garbage collector.

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

SHOW VARIABLES LIKE '%plan_expiration_minutes'; /* be sure to save these values */

SET GLOBAL plan_expiration_minutes=0;
SET GLOBAL disk_plan_expiration_minutes=0;
SELECT SLEEP(300);

SET GLOBAL plan_expiration_minutes=<oldvalue>;

SET GLOBAL disk_plan_expiration_minutes=<oldvalue>;

Related Topics

• Code Generation