11 Mnesia Database Questions

Mnesia is the somewhat odd name for the real-time, distributed database which comes with Erlang.

11.1 What is Mnesia good at?

Locking and Transactions

If you need to keep a database that will be used by multiple processes and/or nodes, using Mnesia means you don't have to write your own access controls.

Distribution

Tables can be replicated at many nodes, both for efficiency (database lookup is a local activity) and robustness (redundancy means if one node goes down, other nodes still have copies of the data.)

Non-fully-normalised data

Unlike most database systems, records can contain data of arbitrary size and structure.

Monitoring

Monitoring - processes can subscribe to events which are sent when various operations on the data take place (update, delete, etc) The RDBMS package allows even more fine-grained control.

11.2 What is Mnesia not so good at?

Mnesia is primarily intended to be a memory-resident database. Some of its design tradeoffs reflect this.

Really large tables must be stored in a fragmented manner.

11.3 Is Mnesia good for storing blobs?

It depends. Erlang has no problem storing Erlang binary data types of arbitrary size, however due to the in-memory-database design emphasis of mnesia, storing lots of binary data will eventually hit one of a number of limitations. These are driven by:

Storage type - Both ram_copies and disc_copies tables rely on storing a full copy of the whole table and data in main memory. This will limit total blob storage to the size of available memory.

On the other hand disc_only_copies tables do not suffer from this limitation but they are slow (from disk) and the data is stored in dets tables which if not closed properly (e.g. after system crash) can take a long time to repair (this was improved in recent versions but is still not quick).
Replication - if the table has a replica then updating an entry and rebuilding after a restart will copy the data over the network between the two machines. Depending on available bandwidth and the uptime requirements this may or may not be acceptable.

As always, measurement of the different mechanisms for your specific application is recommended.

A more colourful discussion of the these topics can be found in this post to the mailing list.

11.4 Is there an SQL interface for mnesia?

A partial one was built as a masters project, it's of limited use and not widely used.

QLC is the query engine for Mnesia and ETS. It is a much better fit to Erlang than SQL.

11.5 How much data can be stored in Mnesia?

It depends on the storage type of your tables.

For ram_copies and disc_copies, the entire table is kept in memory, so data size is limited by available RAM.

Note that for disc_copies tables, the entire table needs to be read from disk to memory on node startup, which can take a long time for large tables.
disc_only_copies tables are limited to 2 GB each. If your table is fragmented, each fragment counts as a separate table, and the combined size can thus exceed 2 GB.

The reason for this limit is that disc_only_copies tables are backed by Dets tables, and the Dets file format uses signed 32-bit integers for file offsets.

An earlier version of this FAQ entry claimed that all tables are limited by the Dets limit. This was the case until R7B-4 (released on 30th September 2001), when disc_copies tables were moved from Dets to disk_log.