Durability and Snapshots¶

Durability¶

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

Configuration¶

The 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. If 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, you can

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

Currently, durability is enabled or disabled for the entire MemSQL database server instance. Durability cannot be selectively enabled or disabled by database.

Disabling Durability¶

For the highest performance, albeit at the cost of disk-persistence, durability in MemSQL can be fully disabled. You should be very careful when choosing to disable durability: if the memsqld process is killed or if the host machine loses power then all data in all databases will be irrecoverably lost. However, MemSQL will persist metadata (databases and table definitions) even if durability is off.

To disable durability, set durability=off in memsql.cnf, or add the start up parameter --durability=off to the memsqld server process.

Durability settings cannot be changed at run time.

For example, notice how in this interaction we run MemSQL with durability disabled, create a table, populate it, then restart MemSQL, and the data is gone!

$ ./memsqld --durability=off &

120604 11:58:34 [Note] ./memsqld: ready for connections.
Version: '1b'  socket: '/tmp/memsql.sock'  port: 3306

$ mysql -u root -h 127.0.0.1 -P 3306 --prompt="memsql> "

memsql> CREATE DATABASE testdb;

Query OK, 0 rows affected (0.42 sec)

memsql> USE testdb;

Database changed

memsql> CREATE TABLE test(id INT PRIMARY KEY);

Query OK, 0 rows affected (4.61 sec)

memsql> INSERT INTO test VALUES (1), (2), (3);

Query OK, 3 rows affected (1.19 sec)
Records: 3  Duplicates: 0  Warnings: 0

memsql> SELECT * FROM test;

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.81 sec)

memsql> exit;

Bye

$ killall memsqld

120604 12:01:16 [Note] ./memsqld: Normal shutdown

120604 12:01:16 [Note] ./memsqld: Shutdown complete

$ ./memsqld --durability=off &

120604 11:58:34 [Note] ./memsqld: ready for connections.
Version: '1b-debug'  socket: '/tmp/memsql.sock'  port: 3306

$ mysql -u root -h 127.0.0.1 -P 3306 --prompt="memsql> "

memsql> USE testdb;

Database changed

memsql> SHOW TABLES;

+------------------+
| Tables_in_testdb |
+------------------+
| test             |
+------------------+
1 row in set (0.00 sec)

memsql> SELECT * FROM test;

Empty set (0.00 sec)

Recovery Errors¶

MemSQL uses checksums in both the snapshot and log files to confirm data consistency. If while recovering from one of these files, MemSQL discovers an inconsistency between the data and a checksum, the server fails to start up and reports the error in the tracelog. Note that if the server is shut down unexpectedly and is in the middle of a disk write, there may be garbage at the end of a log file. In this case, the garbage is ignored (with a warning in the tracelog).