SNAPSHOT DATABASE

Syntax

SNAPSHOT [DATABASE] <database_name>;

Remarks

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

• This command causes implicit commits. Refer to COMMIT for more information.

• Refer to the Permission Matrix for the required permission.

This command forces data to the object store if you are using bottomless. This is useful before a heavy maintenance operation, like a cluster resize, to ensure that all the data is on S3 so that you can attach back to that point or later.

In the Enterprise edition, using the CREATE MILESTONE command with a meaningful milestone name makes it easy to attach the database at the point marked by the milestone.

During normal operation, SingleStore Helios logs writes and DDL commands to transaction log files. When a snapshot is taken, a compact snapshot with an incremented index of the entire database is automatically written out to the disk. The transaction log is also truncated, meaning that future transactions are added to a new log with the same incremented index until the next snapshot is taken. The snapshot holds all the data, with the index, up to the point when it was taken. This occurs at the following times:

1. When the transaction log reached the threshold set by snapshot_trigger_size

2. After large ingests to columnstore tables, during which new columnstore segments are written or merged during ingest, and old (immutable) segments need to be cleared out. The snapshot effectively accomplishes this, since they are stored as long as the transaction log references them.

The SNAPSHOT DATABASE command allows you to force a snapshot operation on a given database across all the partitions in a workspace. SNAPSHOT DATABASE is primarily useful to compact the logs to enable faster recovery if the SingleStore Helios service is restarted. There are two situations in which we would typically recommend performing a manual SNAPSHOT on a database:

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

2. Prior to performing maintenance operations (such as SingleStore Helios upgrades) that require you to take leaves offline, especially if your workload is highly transactional.

Consider a database where DDL queries being run frequently cancel each other out, such as CREATE TABLE and DROP TABLE for the same table. These commands would always be written to the transaction log as two commands. Comparatively, a snapshot of the same set of transactions would merge out these operations. Effectively, this means reduced recovery work in cases where a snapshot is taken immediately prior to a restart or any maintenance. This is because SingleStore Helios only needs to recover the snapshot, rather than replaying the transaction log and recovering the previous snapshot.

Note: For workloads with ETL transactions, some users choose to take a snapshot at the end of each transaction in order to keep the workspace ready for a restart. This is not always necessary, however. In many cases, the amount of work being done during ETL is enough to make the transaction log surpass the snapshot_trigger_size (sometimes more than once), or enough data is ingested to trigger a snapshot.

Although taking frequent snapshots is beneficial in many cases, users who utilize DR workspace replication should exercise caution when configuring snapshot_trigger_size or triggering manual snapshots with SNAPSHOT DATABASE. For example, consider a case where a secondary DR workspace falls behind replaying transactions from snapshot A, while the primary workspace is logging transactions to what will be snapshot B. Then, the primary workspace ingests enough data to trigger snapshot B, and moves on to logging what will be snapshot C. The secondary workspace no longer has the capability to catch up on old write operations from snapshot A, and will reprovision instead. This causes the secondary workspace to drop its data, and start replaying the newest snapshot B from the primary workspace. While the secondary workspace reprovisions, it cannot be queried, which could impact any analytical workload running on the secondary workspace.

Example

SNAPSHOT DATABASE database_name;

Query OK, 0 rows affected (45.36 sec)