Using Durability and Recovery
Although MemSQL’s primary storage is main memory, the server maintains a copy of the data on disk as well.
By default, MemSQL runs with full durability enabled. Transactions are committed to disk as a log and later compressed into full-database snapshots.
The snapshot and log files are stored in the
configured in memsql.cnf . When MemSQL is restarted,
it will recover itself into the state it was in before the shutdown, by
reading the snapshot and log files in
datadir. The snapshot and log
files are stored in the
datadir directory configured in
memsql.cnf. When MemSQL is restarted, it will recover
itself into the state it was in before the shutdown, by reading the
snapshot and log files in
transaction-buffer setting allows you to configure the maximum
size of the in-memory, per-database buffer of database transactions. If
this buffer fills up, write queries will block until some of it is
committed to disk to make space for new transactions. This buffer also
limits the size of any individual log record. In practice, the size of
this log limits the size of an individual row (based on the serialized
size of the row).
transaction-buffer is set to 0, the database runs with
disk-synchronous durability: every write transaction is committed to
disk before the query is acknowledged.
You can audit the performance of the log by monitoring the
Transaction_buffer_wait_time variable in
show status. If you notice
that this value is growing over time, then the hard disk is unable to
keep up with the pace of write queries committed in-memory. To fix this,
- Restart the server with a larger
transaction-buffer. This will increase the size of the in-memory transaction buffer, effectively allowing more room for transactions to sit and wait while the hard drive catches up. The trade off is that the upper bound of data the server can lose in the event of an unexpected system failure is now greater, because more transactions can live in memory and not yet on disk.
- Reconfigure the server to use a faster disk. MemSQL exclusively relies on sequential (not random) disk writes, so using an SSD will dramatically improve durability write performance.
When the log reaches
snapshot-trigger-size, the database kicks off a
new snapshot, which is a full backup of the database.
The log files and last
snapshots-to-keep snapshots are kept by MemSQL
in the data directory. Old files are automatically deleted after they
have rotated out of the
snapshots-to-keep window. MemSQL waits for the
current snapshot to be completely written to disk before deleting older
snapshots. This means enough disk space for
snapshots-to-keep + 1
snapshots is needed to run MemSQL (+1 for the snapshot currently being
written). By default
snapshots-to-keep is set to 2.
Currently, durability is enabled or disabled for the entire MemSQL database server instance. Durability cannot be selectively enabled or disabled by database.
It is not possible to disable durability on a MemSQL cluster. MemSQL relies on durabilitly to replicate data across the cluster.
Snapshot and Log File Formats
MemSQL’s snapshots and logs use an internal binary format. Because
MemSQL reconstructs indexes while recovering or replicating data, these
files only store row data, not indexes. Database snapshots are generally
smaller than their corresponding in-memory footprint because they do not
store indexes. Both snapshots and log files contain checksums to confirm
data integrity. The checksums are computed with the
If you see the following warning message
Warning: SSE4.2 is not supported. Resorting to software CRC32C. MemSQL recovery and log writing performance will be negatively impacted.
your system does not support the CRC32 instruction (part of Intel’s SSE4.2 instruction set). This is common on older processors and some virtualized environments. MemSQL can use a software implementation of CRC32; however, this will slow down reading and writing log files in MemSQL. We recommend that production deployments of MemSQL run on environments that support this instruction.
When MemSQL starts, it asynchronously loads data into memory from
snapshots and logs. A database that is in this loading state is referred
recovering. Connections to a database that is recovering are not
Run SHOW DATABASES with the
EXTENDED option to see which
databases are recovering and to get an estimate for how long recovery
will take. For more information, see database_states.
MemSQL uses checksums in both the snapshot and log files to confirm data
integrity. Snapshot and log recovery behave differently if an
inconsistency between the data and a checksum is detected. If while
recovering from a log file, MemSQL discovers an inconsistency between
the data and a checksum, the database enters the
offline state (see
database_states). No connections to the database are allowed when it’s
offline state. To bring the database into the
run REPAIR DATABASE . This command first creates a copy of the log
and then repairs the log corruption by truncating the log and loading as
much data from the log as possible into memory. REPAIR DATABASE
will not try to reconstruct the corrupted data. MemSQL log checksums can
only detect, not repair, corruptions. Any data after the corruption in
the log won’t be recovered as MemSQL can’t guarantee the consistency of
this data. If a corrupted log record manipulates the same data as a
later log record, replaying the log past the corruption can result in
wrong results. If MemSQL discovers an inconsistency between the data and
a checksum while replaying the snapshot the database enters the
unrecoverable state. There is no way to repair a database in the
unrecoverable state. The database must be recovered from a backup