This topic does not apply to MemSQL Helios.
If you are managing your cluster with MemSQL Ops, go here.
Compiled query plans are stored in a plancache for later use. When a plan expires, it remains in the on-disk plancache and loaded back into memory the next time the query is run. And when a MemSQL node restarts, the in-memory plancache starts off empty and plans are loaded back in from the on-disk plancache as queries are run. This means query plans do not recompile after a plan expires from the in-memory plancache or from a node restarting.
Because both in-memory and on-disk plancache can incrementally consume memory and storage space, it’s important to define a plan expiration time limit as well as learn how to immediately reduce the memory footprint of your plancaches, if needed.
Setting plan expiration limits
Plancache expiration policy can be set through the plan_expiration_minutes, disk_plan_expiration_minutes, and enable_disk_plan_expiration variables.
The expiration for in-memory plancache is always enabled and the default value is 720 minutes.
Prior to MemSQL 6.0.21, plans never expired from the on-disk plancache; however in version 6.0.21 and later, plans will expire from the on-disk plancache if both the enable_disk_plan_expiration is 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). This restriction also applies to temporary (temp) tables. SingleStore recommends re-using the same name for temporary tables to the degree possible.
As engine variables are scoped at the node level (and not across the cluster), if you update any of these plan expiration variables, you must update the values across all nodes in your cluster. See the engine variables overview 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.
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)
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)
SET GLOBAL enable_disk_plan_expiration = true;
Query OK, 0 rows affected (0.08 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)
Dropping 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 two commands can drop a plan from the plancache: DROP … FROM PLANCACHE and ANALYZE.
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.
DROP ... FROM PLANCACHE drops plans from both the in-memory and on-disk plancache, but it only affects plans which are currently in-memory. Plans which are currently only on disk but not in memory are not affected. Therefore, even after running DROP ALL FROM PLANCACHE, some queries may still use previously compiled plans that were on-disk but not in-memory.
Both of these commands invalidate plans by marking them as invalid so they won’t be used in the future, but they do 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 these commands. The only way to delete plans which are only on-disk (other than waiting for them to expire using disk_plan_expiration_minutes) is by manually deleting the files from the plancache directory.
Deleting Plancache Files
The manual deletion of plancache files on disk is an offline operation and should be performed with caution. Please contact MemSQL 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:
-
Stop each node using the
sdb-admin stop-nodecommand. The following example shows how to shut down a single node; however, you can use the option--allto shut down the whole cluster.sdb-admin stop-node --memsql-id <MemSQL_ID> -
Delete the contents of the
plancachedirectory on each node. The default location is/var/lib/memsql/<ROLE>-<PORT>-<ID>/plancache/.sudo rm -rf /path/to/plancache/* -
Start the nodes back up using the
sdb-admin start-nodecommand. The following example shows how to start up a single node; however, you can use the option--allto start all nodes.sdb-admin start-node --memsql-id <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.
