Sign inTry Free

Managing Plancache Memory and Disk Usage

On this page

Compiled query plans are stored in a plancache for later use. When a plan expires, it remains in the on-disk plancache and is loaded back into memory the next time the query is run. 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. 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.

Set Plan Expiration Limits

Plancache expiration policy can be set through the plan_expiration_minutes, disk_plan_expiration_minutes, and enable_disk_plan_expiration engine variables. Expiration for the in-memory plancache is always enabled.

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.

Note

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

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)

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 cluster.

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.

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.

    sdb-admin stop-node --memsql-id <MemSQL_ID>

  2. Delete the contents of the plancache directory on each node. The default location is /var/lib/memsql/<ROLE>-<PORT>-<ID>/plancache/.

    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.

    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.

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/<node_ID>/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).

    sdb-admin list-nodes
    +------------+------------+-------------------------------------------+------+---------------+--------------+---------+----------------+--------------------+--------------+
| 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.

    sdb-admin describe-node --memsql-id <node_ID> 
    +--------------------+---------------------------------------------------------------------------------+
| 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.

    sdb-admin stop-node --memsql-id <node_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.

    sudo cp -R -p </path/to/source/directory> </path/to/destination/directory>

    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:

    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).

    sdb-admin update-config --memsql-id <node_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.

    sdb-admin describe-node --memsql-id <node_ID>
    +--------------------+------------------------------------------------------------------------------+
| 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.

    sdb-admin start-node --memsql-id <node_ID>

    When prompted, confirm the operation.

    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.

    +------------+------------+-------------------------------------------+------+---------------+--------------+---------+----------------+--------------------+--------------+
    | 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.

    sudo cp -R -p <path/to/source/directory> <path/to/backup/directory>
    sudo rm -r dirname

Last modified: November 13, 2024

Was this article helpful?

On this page

Was this article helpful?