# 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](https://docs.singlestore.com/db/v9.1/query-data/advanced-query-topics/code-generation.md) for more information on the plancache. Compiled query plan images may be removed from the in-memory plancache through expiration or eviction. A plan expires from the in-memory plancache (called expiration) after the time interval specified by the `plan_expiration_minutes` engine variable has elapsed. When a plan expires from the in-memory plan cache, the plan is removed fully from the in-memory plancache so when that plan is used again it must be fully reloaded from disk. Plans are evicted from the in-memory plan cache (called eviction) when the plancache memory usage goes over the thresholds specified by `compiled_images_eviction_memory_limit_mb` and `compiled_images_eviction_memory_limit_percent`, as described below. Once the plancache goes over the threshold, the engine evicts the least recently used query plan images until the memory use of the plancache falls below the threshold. 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 reload more quickly than expired plans, but in neither case will the plan be recompiled. 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 expire from the on-disk plancache as described in [Set Plan Expiration Limits](https://docs.singlestore.com/#UUID-d28dbae3-2336-1ac1-e97d-83832be8662a.md). 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. Query plans can be manually removed from the in-memory plancache as described in [Drop Plans from the Plancache](https://docs.singlestore.com/#UUID-fc252a59-15ea-15a6-9fe0-352241d148b0.md). All plans can be removed from the in-memory and on-disk plancache as described in [Drop All Plans from the Plancache](https://docs.singlestore.com/#section-idm234875781961294.md). Finally, query plans can be manually deleted from the on-disk plancache as described in [Delete Plancache Files](https://docs.singlestore.com/#UUID-d43a953d-459c-3244-8dfd-939147a46a9a.md). ## 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 plancache is always enabled. Expiration for the on-disk plancache is managed with the `enable_disk_plan_expiration` engine variable, which defaults to ON. Plans will expire from the on-disk plancache if the following conditions are met:  * The `enable_disk_plan_expiration` engine variable is set to `ON`, and * 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. ```sql SHOW VARIABLES LIKE '%plan_expiration%'; ``` ```output +------------------------------+-------+ | Variable_name | Value | +------------------------------+-------+ | disk_plan_expiration_minutes | 20160 | | enable_disk_plan_expiration | ON | | plan_expiration_minutes | 720 | +------------------------------+-------+ 3 rows in set (0.08 sec) ``` ```sql SET GLOBAL plan_expiration_minutes = 480; ``` ```output Query OK, 0 rows affected (0.07 sec) ``` ```sql SET GLOBAL disk_plan_expiration_minutes = 10080; ``` ```output Query OK, 0 rows affected (0.09 sec) ``` ```sql SET GLOBAL enable_disk_plan_expiration = true; /* ensure this variable is set to true */ ``` ```output Query OK, 0 rows affected (0.08 sec) ``` ```sql SHOW VARIABLES LIKE '%plan_expiration%'; ``` ```output +------------------------------+-------+ | 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 > **📝 Note**: Plan eviction and the information in this section is applicable when `interpreter_mode` is set to `interpret_first` or `compile`. 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 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/drop-from-plancache.md) , [ALTER TABLE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/alter-table.md), and [ANALYZE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/operational-commands/analyze.md). `DROP ALL FROM PLANCACHE` can be used to purge previously compiled plans and generate fresh query plans. It can be run on a specific plan or more generally. Refer to [DROP … FROM PLANCACHE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/drop-from-plancache.md) for more information. > **📝 Note**: `DROP ALL FROM PLANCACHE` marks plans for deletion but does not delete them. As a result, there may be extra plans on disk because the engine generates new plans to replace the invalidated plans which remain on disk until they are garbage collected. You can tell if the disk plan garbage collector is behind by checking the nodes' "memsql.log" files for lines, including the phrase "Exiting stale disk plan garbage collection after 20 seconds of work". If the disk plan garbage collector is behind, it gives up after 20 seconds and waits until the next passage of `disk_plan_expiration_minutes` minutes to continue. 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/operational-commands/analyze.md) 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. ## Drop All Plans from the Plancache To remove all plans from the in-memory and on-disk plancaches, SingleStore recommends the practice of temporarily setting `plan_expiration_minutes` and `disk_plan_expiration_minutes` to `0`. Setting these engine variables to `0` will cause the plans in the plancaches to be marked as invalid and subsequently deleted by the garbage collector. To forcibly drop all plans in the in-memory and on-disk plancaches: Run `SHOW VARIABLES` and save the current values for the plan expiration minutes engine variables. ```sql SHOW VARIABLES LIKE '%plan_expiration_minutes'; /* be sure to save these values */ ``` Set the plan expiration minutes engine variables to `0` and invoke `SLEEP` to give the garbage collector time to delete the plans. A larger value for `SLEEP` may be needed depending on the size of the plancache and the speed of your disks. ```sql SET GLOBAL plan_expiration_minutes=0; SET GLOBAL disk_plan_expiration_minutes=0; SELECT SLEEP(300); ``` Reset the plan expiration minutes engine variables to their previous values. ```sql SET GLOBAL plan_expiration_minutes=; SET GLOBAL disk_plan_expiration_minutes=; ``` ## Delete Plancache Files > **⚠️ Warning**: The manual deletion of plancache files on disk is an offline operation and should be performed with caution. Please contact Support if assistance is needed. Use `enable_disk_plan_expiration` and `disk_plan_expiration_minutes` to set shorter expiration times if you frequently run out of disk space due to the size of your on-disk plancache. To delete the physical plancache files stored on disk, perform the following actions: 1. Stop each node using the `sdb-admin stop-node` command. The following example shows how to shut down a single node; however, you can use the option `--all` to shut down the whole cluster. ```shell sdb-admin stop-node --memsql-id ``` 2. Delete the contents of the `plancache` directory on each node. The default location is `/var/lib/memsql/--/plancache/`. ```shell sudo rm -rf /path/to/plancache/* ``` 3. Start the nodes back up using the `sdb-admin start-node` command. The following example shows how to start up a single node; however, you can use the option `--all` to start all nodes. ```shell sdb-admin start-node --memsql-id ``` The on-disk plancache should be cleared from the node as well as the in-memory plancache because of the node restart operation. ## Change the `plancache` Directory Each node in the cluster has a `plancache` directory which contains compiled plans used for code generation. The default location of the `plancache` directory is `/var/lib/memsql//plancache`. You may consider changing the default directory for several reasons, including, but not limited to, moving to a larger file system, changing hardware to increase storage capacity, and restructuring directories as part of the clean-up process. To change the `plancache` directory on one or more nodes, perform the following steps on each target node separately. 1. Obtain a list of your cluster nodes by running the following command on the host where Toolbox is installed. Make a note of the node ID of the target node(s) and the IP address of the target host(s). ```shell sdb-admin list-nodes ``` ```output +------------+------------+-------------------------------------------+------+---------------+--------------+---------+----------------+--------------------+--------------+ | MemSQL ID | Role | Host | Port | Process State | Connectable? | Version | Recovery State | Availability Group | Bind Address | +------------+------------+-------------------------------------------+------+---------------+--------------+---------+----------------+--------------------+--------------+ | C0EE1AEC3B | Master | ec2-54-234-59-65.compute-1.amazonaws.com | 3306 | Running | True | 7.6.6 | Online | | 0.0.0.0 | | 6AF9490486 | Aggregator | ec2-34-229-205-7.compute-1.amazonaws.com | 3306 | Running | True | 7.6.6 | Online | | 0.0.0.0 | | 2CDAC53371 | Leaf | ec2-54-160-159-22.compute-1.amazonaws.com | 3306 | Running | True | 7.6.6 | Online | 1 | 0.0.0.0 | | EF13284492 | Leaf | ec2-54-242-64-33.compute-1.amazonaws.com | 3306 | Running | True | 7.6.6 | Online | 1 | 0.0.0.0 | +------------+------------+-------------------------------------------+------+---------------+--------------+---------+----------------+--------------------+--------------+ ``` 2. Locate the node(s) on which `plancache` directory must be changed. Verify the location of the `plancache` directory on each node by running the following command. ```shell sdb-admin describe-node --memsql-id ``` ```output +--------------------+---------------------------------------------------------------------------------+ | memsqlId | EF13284492F0FDF0B35EC95B11523B9437B589B7 | | host | ec2-54-242-64-33.compute-1.amazonaws.com | | role | Leaf | | port | 3306 | | bindAddress | 0.0.0.0 | | processState | Running | | version | 7.6.6 | | memsqlConfig | /var/lib/memsql/aae1e686-42ab-432c-88bc-1086d894204a/memsql.cnf | | datadir | /var/lib/memsql/aae1e686-42ab-432c-88bc-1086d894204a/data | | plancachedir | /var/lib/memsql/aae1e686-42ab-432c-88bc-1086d894204a/plancache | | tracelogsdir | /var/lib/memsql/aae1e686-42ab-432c-88bc-1086d894204a/tracelogs | | auditlogsdir | /var/lib/memsql/aae1e686-42ab-432c-88bc-1086d894204a/auditlogs | | memsqld | /opt/memsql-server-7.6.6-63e226ba05/memsqld | | nodeID | 3 | | pid | 6967 | | dpid | 6974 | | autoRestartEnabled | true | | isConnectable | true | | recoveryState | Online | | availabilityGroup | 1 | | config | Omitted from table view. Use 'sdb-admin describe-node ... --property config' | | variables | Omitted from table view. Use 'sdb-admin describe-node ... --property variables' | +--------------------+---------------------------------------------------------------------------------+ ``` 3. Using the `sdb-admin stop-node` command, stop each target node. Optionally, you can use the `--all` option to shut down the whole cluster. ```shell sdb-admin stop-node --memsql-id ``` When prompted, choose to create a snapshot or skip it. ``` Continue to stop the node(s) without taking a database snapshot? Select 'Y' to stop the node(s) without taking a database snapshot. Select 'N' to take a snapshot of all databases and then stop the node(s). Taking a database snapshot will reduce the time it takes for the node(s) to recover after being restarted, but may increase the time it takes for the node(s) to stop. [Y/n]: Y Toolbox is about to perform the following actions on host ec2-54-242-64-33.compute-1.amazonaws.com: · Run 'memsqlctl stop-node --memsql-id EF13284492' Would you like to continue? [y/N]: y ✓ Stopped node on ec2-54-242-64-33.compute-1.amazonaws.com Operation completed successfully ``` 4. After the node is stopped, log in to the target node’s host and copy the `plancache` directory recursively to the new path using the following command. The `-p` option preserves the directory’s attributes including mode, ownership, and timestamp. ```shell sudo cp -R -p ``` In this example, the `plancache` directory needs to be moved from `/var/lib/memsql/aae1e686-42ab-432c-88bc-1086d894204a/plancache` to `/var/lib/plancache_data`. So the command will become: ```shell sudo cp -R -p /var/lib/memsql/aae1e686-42ab-432c-88bc-1086d894204a/plancache /var/lib/plancache ``` > **❗ Important**: - SingleStore recommends to make a note of the directory’s permissions, user, and group prior to copying the files. > - Make sure that the `plancache` directory’s permissions and ownership are intact. The default `memsql` user should be able to access the new `plancache` directory. 5. On the host where Toolbox is installed, run the following command to update the new `plancache` directory path for the required node(s). ```shell sdb-admin update-config --memsql-id --key plancachedir --value /new/path/to/directory ``` When prompted, confirm the update. ``` Toolbox is about to run 'memsqlctl update-config --key plancachedir --value /var/lib/plancache' on the following nodes: - On host ec2-54-242-64-33.compute-1.amazonaws.com: + EF13284492 Would you like to continue? [y/N]: y ✓ Updated configuration on ec2-54-242-64-33.compute-1.amazonaws.com Operation completed successfully ``` 6. Verify the changes by running the following command. ```shell sdb-admin describe-node --memsql-id ``` ```output +--------------------+------------------------------------------------------------------------------+ | memsqlId | EF13284492F0FDF0B35EC95B11523B9437B589B7 | | host | ec2-54-242-64-33.compute-1.amazonaws.com | | role | Leaf | | port | 3306 | | bindAddress | 0.0.0.0 | | processState | Stopped | | version | 7.6.6 | | memsqlConfig | /var/lib/memsql/aae1e686-42ab-432c-88bc-1086d894204a/memsql.cnf | | datadir | /var/lib/memsql/aae1e686-42ab-432c-88bc-1086d894204a/data | | plancachedir | /var/lib/plancache | | tracelogsdir | /var/lib/memsql/aae1e686-42ab-432c-88bc-1086d894204a/tracelogs | | auditlogsdir | /var/lib/memsql/aae1e686-42ab-432c-88bc-1086d894204a/auditlogs | | memsqld | /opt/memsql-server-7.6.6-63e226ba05/memsqld | | nodeID | -- | | pid | -- | | dpid | -- | | autoRestartEnabled | -- | | isConnectable | -- | | recoveryState | -- | | availabilityGroup | -- | | config | Omitted from table view. Use 'sdb-admin describe-node ... --property config' | | variables | -- | +--------------------+------------------------------------------------------------------------------+ ``` 7. Start the node(s) for which the `plancache` directory has been updated. Optionally, you can use the `--all` option to start the entire cluster. ```shell sdb-admin start-node --memsql-id ``` When prompted, confirm the operation. ```shell Toolbox is about to perform the following actions on host ec2-54-242-64-33.compute-1.amazonaws.com: · Run 'memsqlctl start-node --memsql-id EF13284492' Would you like to continue? [y/N]: y ✓ Started nodes on ec2-54-242-64-33.compute-1.amazonaws.com (1/1) ✓ Successfully started nodes on 1 host ✓ Successfully connected to node Operation completed successfully ``` 8. Verify the status of the updated nodes by running the `sdb-admin list-nodes` command. ```shell +------------+------------+-------------------------------------------+------+---------------+--------------+---------+----------------+--------------------+--------------+ | MemSQL ID | Role | Host | Port | Process State | Connectable? | Version | Recovery State | Availability Group | Bind Address | +------------+------------+-------------------------------------------+------+---------------+--------------+---------+----------------+--------------------+--------------+ | C0EE1AEC3B | Master | ec2-54-234-59-65.compute-1.amazonaws.com | 3306 | Running | True | 7.6.6 | Online | | 0.0.0.0 | | 6AF9490486 | Aggregator | ec2-34-229-205-7.compute-1.amazonaws.com | 3306 | Running | True | 7.6.6 | Online | | 0.0.0.0 | | 2CDAC53371 | Leaf | ec2-54-160-159-22.compute-1.amazonaws.com | 3306 | Running | True | 7.6.6 | Online | 1 | 0.0.0.0 | | EF13284492 | Leaf | ec2-54-242-64-33.compute-1.amazonaws.com | 3306 | Running | True | 7.6.6 | Online | 1 | 0.0.0.0 | +------------+------------+-------------------------------------------+------+---------------+--------------+---------+----------------+--------------------+--------------+ ``` 9. To safely remove your old `plancache` directory, create a backup of the directory and all its contents and then delete it. To perform these actions, run the following commands on the target host. ```shell sudo cp -R -p ``` ```shell sudo rm -r dirname ``` ## Related Topics * [Code Generation](https://docs.singlestore.com/db/v9.1/query-data/advanced-query-topics/code-generation.md) *** Modified at: March 25, 2026 Source: [/db/v9.1/user-and-cluster-administration/maintain-your-cluster/managing-memory/managing-plancache-memory-and-disk-usage/](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/maintain-your-cluster/managing-memory/managing-plancache-memory-and-disk-usage/) (An index of the documentation is available at /llms.txt)