Takes a snapshot of the given database and truncates the transaction log.

During normal operation, MemSQL logs writes and DDL commands to transaction log files. Periodically, a compact snapshot of the entire database is written out to disk and the logs are truncated. This command allows you to force a snapshot operation on the given database across all partitions in the cluster.

SNAPSHOT DATABASE is primarily useful to compact the logs to enable faster recovery if the MemSQL service is restarted. There are two situations in which we would typically recommend performing a manual SNAPSHOT on a database:

• After changing the schema of a large table using ALTER TABLE to avoid the cost of replaying the ALTER operation if a leaf were ever to restart. In practice, this situation comes up most often during prototyping and development of your application when schemas are frequently changed.

• Prior to performing maintenance operations (such as MemSQL upgrades) that require you to take leaves offline. Especially if your workload is highly transactional this will save recovery time on the leaves as they will have significantly fewer transactions to replay after loading the most recent snapshot into memory.

Syntax

SNAPSHOT [DATABASE] <db_name>

Remarks

• db_name is the name of the database to snapshot.
• A user must have either the SUPER or BACKUP privilege to run this command.
• SNAPSHOT DATABASE forces a manual snapshot, and can only be run on the master aggregator.

Example

SNAPSHOT DATABASE foo;
Query OK, 0 rows affected (45.36 sec)

Was this Article Helpful?

Thanks For Your Feedback!