You are viewing an older version of this section. View current production version.
SNAPSHOT DATABASE
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. You must haveSUPER
privilege.- The
SNAPSHOT DATABASE
command must be run on the master aggregator only.
Example
memsql> snapshot database foo;
Query OK, 0 rows affected (45.36 sec)