How do I delete all plans in the plancache?
The Garbage Collector thread waits 2 seconds between runs on each node - While waiting, it checks if "disk_plan_expiration_minutes" minutes have passed since the last run of disk plan cleanup and, if so, it checks for plans older than "disk_plan_expiration_minutes" to mark for deletion (or plans otherwise marked for deletion by dropping or by DDL).
If you need to forcibly drop all plans in the plancache, both on-disk and in-memory, then run:
- "SHOW VARIABLES LIKE '%plan_expiration_minutes';" (save these values to set them back afterward)
- "SET GLOBAL plan_expiration_minutes=0;"
- "SET GLOBAL disk_plan_expiration_minutes=0;"
- "SELECT SLEEP(300);" (to give the GC thread time to clean up plans - you may need longer depending on the size of the plancache and the speed of your disks)
- "SET GLOBAL plan_expiration_minutes=<oldvalue>;"
- "SET GLOBAL disk_plan_expiration_minutes=<oldvalue>;
Note: "DROP ALL FROM PLANCACHE;" marks plans for deletion but does not delete them. This can result in more plans on disk because SingleStore will generate new plans despite old plans existing (because they exist but are invalid). You can tell if the disk plan GC is behind by checking nodes' "memsql.log" files for lines, including the phrase "Exiting stale disk plan garbage collection after 20 seconds of work". If the disk plan GC is behind (it gives up after 20 seconds and waits until the next passage of "disk_plan_expiration_minutes" minutes to continue. Support recommends reducing "disk_plan_expiration_minutes" instead of running "DROP ALL FROM PLANCACHE;".