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 TABLEto 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.
SNAPSHOT [DATABASE] <db_name>
db_nameis the name of the database to snapshot. You must have
SNAPSHOT DATABASEcommand must be run on the master aggregator only.
memsql> snapshot database foo; Query OK, 0 rows affected (45.36 sec)