If you haven’t already tried it out, mnesia is an Erlang application that implements a database, albeit a rather peculiar and particularly powerful one.
I have developed mnesia-based systems in industrial settings competing with solutions based on industry-standard databases installed in industry-standard cluster server environments, and in those solutions, the mnesia-based systems outdid them in terms of usability, reliability and ease of administration, not to mention that it was practically free with no huge license fees to pay the IT giants.
That said, don’t jump to conclusions. mnesia is not necessarily suitable for every application that needs a DBMS. Analyze your problem and evaluate the benefits and drawbacks before committing. Given the extreme ease of implementation, you can easily build a proof-of-concept solution and evaluate it against the more traditional solutions.
In this post we’ll explore what mnesia does and how to go about it. A cautionary note. DBMSs need to be installed and set up before you can use them in your applications. mnesia is no different. Except that it is provided as a library in the erlang distribution so you don’t need to install it, but you do need to set things up.
When you start using mnesia for the first time, you follow some tutorial or quick-start. The setting up is sort of skipped with some code provided so that you don’t need to “waste any time”. In the long run though, this leads to misunderstandings and lots of lost hours trying to figure out what you did wrong and so on. So, we will take some time going over the setting up in order to understand better what’s going on. It’s not difficult and most of all it is logical.
Records
mnesia stores information in tables which are made up of records, so let’s first take a look at those.
Assume that we want to write an application that keeps track of bike rides. For each ride, we want to record who took the ride, when it started, how long it took and what distance was covered. We could capture this information in a record ride
-record(ride, {rider, date, month, year, duration, distance, itinerary}).
We can guess what the different fields mean, but let’s make it clearer by specifying the type of each field.
-record(ride, {rider :: string(),
date :: #{day := integer(),
month := 1..12,
year := 2020..2030},
duration :: integer(), %% minutes
distance :: integer(), %% kms
itinerary :: {string(), %% starting place
string(), %% place en route
string()} %% ending place
}).
Now this doesn’t change anything from the compiler’s point of view, but it certainly clarifies what we expect as the values of those fields. And it can help if you run dialyzer, of course.
If you are familiar with relational databases, note that we are not limited to so-called scalar types. Here we have date
as a map and itinerary
as a tuple. Erlang actually allows the field to be a term. It can even be a function!
Let’s put the definition in a module called blog
and write a function blog:record_play/0
to create a ride
record and print some information about it.
record_play() ->
R = #ride{rider = "A",
date = #{day => 13, month => 6, year => 2022},
duration = 83,
distance = 29,
itinerary = {"p1", "p2", "p3"}},
io:format("record fields: ~p~n", [record_info(fields, ride)]),
io:format("record size: ~p~n", [record_info(size, ride)]),
io:format("R: ~p~n", [R]).
record_info/2
is a pseudo-function that the compiler will generate automatically while compiling. It doesn’t work in the shell and the record name has to be passed as an atom.
Let’s compile and run it
163> c(blog).
{ok,blog}
164> blog:record_play().
record fields: [rider,date,duration,distance,itinerary]
record size: 6
R: {ride,"A",#{day => 13,month => 6,year => 2022},83,29,{"p1","p2","p1"}}
ok
As you probably know, a record is simply a tuple whose first element is the name of the record. This is confirmed by the last two outputs.
If we have several ride
records and we put each each record in a row of a spreasheet with each field in a separate column, we get a table like
rider | date | duration | distance | itinerary | |
---|---|---|---|---|---|
ride | “A” | #{day => 13,month => 6,year => 2022} | 83 | 29 | {“p1”,”p2”,”p1”} |
ride | “B” | #{day => 13,month => 6,year => 2022} | 41 | 15 | {“p1”,”p3”,”p4”} |
ride | “A” | #{day => 15,month => 6,year => 2022} | 22 | 10 | {“p2”,”p3”,”p1”} |
ride | “A” | #{day => 18,month => 6,year => 2022} | 36 | 18 | {“p3”,”p2”,”p3”} |
ride | “B” | #{day => 1,month => 7,year => 2022} | 90 | 34 | {“p5”,”p6”,”p5”} |
ride | “B” | #{day => 21,month => 7,year => 2022} | 52 | 16 | {“p4”,”p3”,”p1”} |
And that’s more or less the illusion mnesia provides when we store the records in an mnesia table.
Information Storage
mnesia can keep the tables in RAM, on disc or both, replicating as necessary. Further, mnesia can run in a distributed environment with multiple erlang nodes and in such configurations, it can be set up to replicate the tables on any number of those nodes as well.
We need to tell mnesia the directory in which to store the table and other information for the Erlang node by means of the enviornment variable dir
of application mnesia. We can set the variable in the system config
file, set it explicitly before starting mnesia, or provide it in the erl
command. In the latter case, remember that although the directory is a string (enclosed in double quotes), it needs to be provided as an atom, so we enclose the double-quoted string in single quotes. It looks weird at first, but you get used to it.
If the dir
environment variable is not defined, mnesia assumes it is Mnesia.Node
where Node
is nonode@nohost
if distribution is not enabled or the value returned by erlang:node/0
.
Please note that the directory is specific for an Erlang node. We cannot use the same directory for different Erlang nodes.
Schema
Once we’ve setup the directory, we need to create a schema. The schema is a table that holds information about all the tables, including their definitions in terms of what kind of records are stored, the nodes the tables are replicated to, whether they are stored in RAM or disc or both, etc.
mnesia cannot run without a schema, so if you try to start mnesia without ever having created a schema, it will create one in the RAM.
Since mnesia uses the schema for its operations, you cannot create a schema while mnesia is running. When mnesia is not running, you can create the schema with mnesia:create_schema/1
which takes a list of nodes on which the schema needs to be created. The nodes must be running without mnesia running on those nodes. We’ll stick to configurations with just one running node for now.
We populate the schema with table definitions which we’ll cover shortly, but let’s now try our hands on the concepts so far as you might be getting restless with all this theory.
Make a temporary directory, e.g. /tmp/blog
where the tables will be stored and then start an Erlang shell.
Here follows a short session of commands whose function you likely can guess.
$ mkdir /tmp/blog
$ erl
Erlang/OTP 24 [erts-12.0.2] [source] [64-bit] [smp:2:2] [ds:2: 2:10] [async-threads:1] [jit]
Eshell V12.0.2 (abort with ^G)
1> application:get_env(mnesia, dir).
undefined
2> mnesia:system_info(directory).
"/home/mint/projects/blog/mnesia/Mnesia.nonode@nohost"
3> application:set_env(mnesia, dir, "/tmp/blog").
ok
4> mnesia:system_info(directory).
"/tmp/blog"
5> mnesia:system_info(use_dir).
false
6> mnesia:system_info(db_nodes).
[nonode@nohost]
7> mnesia:info().
===> System info in version "4.19.1", debug level = none <=== opt_disc. Directory "/tmp/blog" is NOT used. use fallback at restart = false running db nodes = [] stopped db nodes = [nonode@nohost] ok 8> mnesia:create_schema([node()]).
ok
9> mnesia:system_info(use_dir).
true
10> ls("/tmp/blog").
LATEST.LOG schema.DAT
ok
11> mnesia:info().
===> System info in version "4.19.1", debug level = none <=== opt_disc. Directory "/tmp/blog" is used. use fallback at restart = true running db nodes = [] stopped db nodes = [nonode@nohost]
ok
12> mnesia:start().
ok
13> mnesia:info().
---> Processes holding locks <--- ---> Processes waiting for locks <--- ---> Participant transactions <--- ---> Coordinator transactions <--- ---> Uncertain transactions <--- ---> Active tables <--- schema : with 1 records occupying 414 word s of mem ===> System info in version "4.19.1", debug level = none <===
opt_disc. Directory "/tmp/blog" is used.
use fallback at restart = false
running db nodes = [nonode@nohost]
stopped db nodes = []
master node tables = []
remote = []
ram_copies = []
disc_copies = [schema]
disc_only_copies = []
[{nonode@nohost,disc_copies}] = [schema]
2 transactions committed, 0 aborted, 0 restarted, 0 logged to disc
0 held locks, 0 in queue; 0 local transactions, 0 remote
0 transactions waits for other nodes: []
ok
mnesia:system_info/1
provides information on a number of aspects of the application, most of which can be queried even when it is not running.
directory
is obvious. use_dir
says if the said directory is being used by mnesia or not. As we can see from lines 5 and 9, this is true only when a schema has been created. And from line 10 we see that a file called schema.DAT
has been created. This is actually a dets
file and you can inspect it with dets:traverse/2
if you are really curious.
Use of mnesia:info/0
in the shell can provide a lot of information on the current state of the application, most of which should already make sense.
Tables
Let’s move on and see how to create the table to record the rides with mnesia:create_table/2
which wants the name of the table and some optional information as arguments.
Even though we can call the table whatever we want, it is convenient to name the table as the name of the records it will store.
mnesia:create_table(
ride,
[{attributes, record_info(fields, ride)},
{disc_copies, [node()]},
{record_name, ride},
{type, bag}])
Don’t try it out in the shell yet as it won’t work. Let’s first see what the options are saying and then we’ll see how to get it to work.
attributes
say what “columns” the table will have. The compiler-generated pseudo-function record_info/2
comes to the rescue.
disc_copies
specifies the nodes where replicas of the table on disc as well as in RAM will reside. If you don’t want RAM copies, use the option disc_only_copies
.
record_name
specifies the record structures which will be stored. The default is the name of the table itself.
Finally there is type
which can be set
, ordered_set
or bag
. To understand what that means, we need to mention that the first attribute of the record (or the second element of the corresponding tuple if you prefer) is called the key of the record. A key is used to select a record from among all the records in the table. When the type is set
, there is at most one record in the table with a given key. When it is bag
, there can be any number of records with the same key, but they must differ in at least one other attibute. Here, we decide to use rider
as the key (listing it as the first attibute wasn’t accidental) and bag
as the type as we could have many records for the same rider
.
Since record_info
is generated by the compiler at compile time only, we’ll use mnesia:create_table/2
in a module and not from the shell (unless you decide to provide the list of fields explicitly, but it is not recommended). If the operation succeeds, it returns {atomic, ok}
. Although we will deal with transactions later, this says that the operation was atomic, that is it succeded on all nodes. If it had failed on any or more of the nodes, we would be sure the table did not get created on any of the nodes.
mnesia:info/0
or mnesia:table_info/2
can give us information about the created table.
Reading and writing records
We finally get to the point where we can actually use the table we created.
We’ll later want to add records to the table with mnesia:write/1
, the variant of mnesia:write/3
that uses some default values, and view the ones already in the table with mnesia:read/1
, the varaint of mnesia:read/3
, but we start with mnesia:dirty_write/1
and mnesia:dirty_read/2
.
We write a function in our blog
module to add a ride
add_ride_v1(Rider, Date, Duration, Distance, Itinerary) ->
mnesia:dirty_write(#ride{rider = Rider,
date = Date,
duration = Duration,
distance = Distance,
itinerary = Itinerary}).
and then add a record using it
12> blog:add_ride_v1("A", #{day=>13,month=>6,year=>2022},83,29,{"p1","p2","p3"}).
ok
13> mnesia:table_info(ride,size).
1
We also write a function in our blog
module to view a record
view_rides_v1(Rider) ->
mnesia:dirty_read(accounting, Rider).
and then use it to view the record we added
14> blog:view_rides_v1("A").
[{ride,"A",
#{day => 13,month => 6,year => 2022},
83,29,
{"p1","p2","p3"}}]
There is another extremely useful way of viewing the records in the table which comes in very handy when you are beginning to use mnesia. It is the observer
tool. Go to the Table Viewer
tab, and then in the View
menu, select Mnesia Tables
. You will see a list of tables (minus the schema). If you double click the row showing the ride
table, another window opens displaying all the records in the table. If you double click on any row, that record opens in a edit window where you can even modify those values, but be careful when doing that.
Note that the read operation returns a list to accomodate for the fact that we could have had multiple records and even none. Try adding the records in the table we saw earlier and view the records for rider “A”. Try reading the records for riders “B” and “C”.
We are now ready for transactions.
Transactions
Let’s assume we want to count all adding and viewing operations for every rider. We keep the counts in another table called accounting
with accounting records defined as
-record(accounting, {rider, read_count, write_count}).
and create the corresponding table with
mnesia:create_table(
accounting,
[{attributes, record_info(fields, accounting)},
{disc_copies, [node()]},
{type, set}])
Now every time we add a ride
record, we also increment the corresponding write count. We modify our add_ride
function to do that
add_ride_v2(Rider, Date, Duration, Distance, Itinerary) ->
mnesia:dirty_write(#ride{rider = Rider,
date = Date,
duration = Duration,
distance = Distance,
itinerary = Itinerary}),
%% mnesia:stop(),
case mnesia:dirty_read(accounting, Rider) of
[] -> %% no accounting for Rider yet
mnesia:dirty_write(#accounting{rider = Rider,
write_count = 1});
[#accounting{write_count = Writes} = Rec] ->
mnesia:dirty_write(Rec#accounting{write_count = Writes + 1})
end.
Similarly, we modify our view_record
function to increment the corresponding read count
view_rides_v2(Rider) ->
case mnesia:dirty_read(accounting, Rider) of
[] -> %% no accounting for Rider yet
mnesia:dirty_write(#accounting{rider = Rider,
read_count = 1});
[#accounting{read_count = Reads} = Rec] ->
%% timer:sleep(1000),
mnesia:dirty_write(Rec#accounting{read_count = Reads + 1})
end,
%% mnesia:stop(),
mnesia:dirty_read(ride, Rider).
Ignore the commented out mnesia:stop
and timer:sleep
calls for now.
If we compile the module again and try the new versions, we’ll see that things work as expected.
29> blog:add_ride_v2("D", #{day=>21,month=>7,year=>2022},52,16,{"p4","p3","p1"}).
ok
30> blog:view_rides_v2("D").
[{ride,"D",
#{day => 21,month => 7,year => 2022},
52,16,
{"p4","p3","p1"}}]
31> mnesia:dirty_read(accounting, "D").
[{accounting,"D",1,1}]
However, there’s a problem with this.
The functions are not atomic. When adding a record, what if something goes wrong after the ride record has been written? We will have added a ride record without accounting for it. In the same way, if something goes wrong after the ride record has been read in the record viewing function, we would account for the viewing of a record that has actually not been viewed.
To verify this, let’s simulate this in the viewing function by uncommenting mnesia:stop()
.
If we try viewing the record now, mnesia will stop. Starting it again and verifying the counts in the accounting table will show that indeed the count has been incremented but we hadn’t actually viewed the record.
40> blog:view_rides_v2("D").
=INFO REPORT====
application: mnesia
exited: stopped
type: temporary
** exception exit: {aborted,{no_exists,[ride,"D"]}}
in function mnesia:abort/1 (mnesia.erl, line 361)
41> mnesia:start().
ok
42> mnesia:dirty_read(accounting, "D").
[{accounting,"D",2,1}]
This is where transactions come in. A transaction is a set of operations which either succeeds or is aborted. When it succeeds all operations are guaranteed to have succeded. When it aborts, it is as if none of the operations succeded. When a function is executed within an mnesia transaction, mnesia provides that guarantee on all mnesia operations.
The non-dirty variants of read and write can only be used in a transaction context. Here’s how we’ll rewrite our viewing function to work in a transaction context
view_rides_v3(Rider) ->
Inspect =
fun() ->
case mnesia:read(accounting, Rider) of
[] -> %% no accounting yet
mnesia:write(#accounting{rider = Rider,
read_count = 1});
[#accounting{read_count = Reads} = Rec] ->
%% timer:sleep(1000),
mnesia:write(Rec#accounting{read_count = Reads + 1})
end,
%% mnesia:stop(),
mnesia:read(ride, Rider)
end,
mnesia:transaction(Inspect).
We can easily verify that this works just like the previous version. Now, as before, uncomment the mnesia:stop
instruction, compile and run it again. mnesia will stop, but this time, when we start mnesia again, the read count will not have been incremented.
58> blog:view_rides_v3("D").
{atomic,[{ride,"D",
#{day => 21,month => 7,year => 2022},
52,16,
{"p4","p3","p1"}}]}
59> mnesia:dirty_read(accounting, "D").
[{accounting,"D",3,1}]
60> c(blog).
{ok,blog}
61> blog:view_rides_v3("D").
** exception exit: shutdown
62> =INFO REPORT==== 12-Aug-2022::17:15:41.989913 ===
application: mnesia
exited: stopped
type: temporary
62> mnesia:start().
ok
63> mnesia:dirty_read(accounting, "D").
[{accounting,"D",3,1}]
Locks
The question of multiple operations all succeeding together is just one aspect of a transaction. There is another serious problem without a transaction that has to do with concurrency.
Immagine two processes trying to view a rider’s records at the same time. Both processes could begin with seeing the same read_count
, so they’ll both increment it by one. With the result that the read count will have been incremented by one even though there were two read operations.
We can simulate the simultaneous reading of the read count by waiting a little bit before incrementing the count. So let’s uncomment timer:sleep(1000)
instead of mnesia:stop()
in the viewing functions, recompile and spawn a couple of processes both trying to view the record. e.g. as follows
86> [spawn(fun() -> io:format("~p~n", [blog:view_rides_v2("D")]) end)
|| _ <- [1,2]].
[<0.3998.0>,<0.3999.0>]
[{ride,"D",#{day => 21,month => 7,year => 2022},52,16,{"p4","p3","p1"}}]
[{ride,"D",#{day => 21,month => 7,year => 2022},52,16,{"p4","p3","p1"}}]
87> mnesia:dirty_read(accounting,"D").
[{accounting,"D",4,1}]
As expected, the read count has incremented by one.
Now try the version that uses a transaction context
88> [spawn(fun() -> io:format("~p~n", [blog:view_rides_v3("D")]) end)
|| _ <- [1,2]].
[<0.4016.0>,<0.4017.0>]
{atomic,[{ride,"D",
#{day => 21,month => 7,year => 2022},
52,16,
{"p4","p3","p1"}}]}
{atomic,[{ride,"D",
#{day => 21,month => 7,year => 2022},
52,16,
{"p4","p3","p1"}}]}
89> mnesia:dirty_read(accounting,"D").
[{accounting,"D",6,1}]
Surprised? This version counts the read operations correctly. The result indicates the function execution was atomic, but how exactly did that happen?
The magic works using so-called locks. In a transaction context, a process can read a record only if it has acquired a read lock on the key of that record (or on the entire table), and a process can write a record in a table only if it has acquired a write lock on its key (or on the entire table).
Incidentally, this is a classic problem of shared resources among multiple processes. With process local variables, Erlang solves the problem by making them invariant.
Only one process at any given time can acquire a lock of a kind. If a process tries to acquire a lock already acquired by some other process, it will get blocked until the lock is released. So once a process acquires a read lock on a key, it can be sure that no other process in a transaction context will be able to read records with that key until it releases the lock. And when the process acquires a write lock on a key, it can be sure that no other process in a transaction context will be able to write a record with that key until it releases the lock on the key.
Not only does mnesia acquire and release the requested locks before executing its read and write functions in a transaction context, it also takes care of the fact the tables may be replicated to other nodes. In fact a read lock is acquired on one node, possibly the local node if a replica exists on the local node, and a write lock is acquired on all active nodes where the table is replicated.
However, blocking a process on the acquisition of a lock can create problems of so-called deadlocks. If one process is holding a lock on key X and needs a lock on key Y, while another process is holding a lock on key Y and needs a lock on key X, they will both block indefinitely.
mnesia solves this problem by aborting the ongoing transaction and re-try it if it can’t acquire a lock because some other process is holding it. When a transaction is aborted, mensia rolls back any changes it might have made to records in the aborted transaction. Although this makes mnesia deadlock free, it means that a transaction may be tried many times before it succeeds. And since mnesia can only rollback changes made by itself, all other actions, particularly ones with side effects, will not be rolled back. For example, if you send a message to some process during the transaction, you might end up sending that message very many times. So, be careful and try not to put statement with side effects inside transactions.
Queries
Now let’s try some more complex queries. In particular, these are queries where the key of the records is not known or is irrelevant. This would generally require an exhaustive search of the entire table, unless some other means are available to reduce the search space.
We’ll examine three ways of searching through the entire table using mnesia:foldl
, mnesia:select
and query list comprehensions. In the next section we look at a way to reduce the search space by means of indexes.
We’ll try to get a list of the duration
of all rides, 10 km or more, that had p2 in their itinerary
.
We start with using mnesia’s fold
operations which have semantics similar to the fold
opertions on a list
. Here’s how we might do it in blog:q1/2
q1(MinDistance, Place) ->
Find =
fun() ->
mnesia:foldl( %% or foldr
fun(#ride{distance = Distance,
itinerary = {P1, P2, P3},
duration = Duration}, Acc)
when Distance >= MinDistance,
P1 =:= Place;
P2 =:= Place;
P3 =:= Place -> [Duration | Acc];
(_, Acc) ->
Acc
end, [], ride)
end,
mnesia:transaction(Find).
If we compile again and try the function we get the desired answers
111> c(blog).
{ok,blog}
112> blog:q1(10, "p2").
{atomic,"$S"}
113> blog:q1(20, "p2").
{atomic,"S"}
Those strings are actually lists of integers. If I want to quickly see what integers they are, I force Erlang to print the string as a list of integers by adding a zero to the list
114> "$S" ++ [0].
[36,83,0]
Another way is to use mnesia’s select
function which employs Erlang Match Specification
. We do this in function blog:q2/2
q2(MinDistance, Place) ->
Find =
fun() ->
MatchHead = #ride{distance = '$1',
itinerary = {'$2', '$3', '$4'},
duration = '$5',
_ = '_'},
Guards = [{'>=', '$1', MinDistance},
{'orelse',
{'=:=', '$2', Place},
{'orelse',
{'=:=', '$3', Place}, {'=:=', '$4', Place}}}],
Results = ['$5'],
mnesia:select(ride, [{MatchHead, Guards, Results}])
end,
mnesia:transaction(Find).
Compiling and running q2
, gives us the same answers
132> c(blog).
{ok,blog}
133> blog:q2(10, "p2").
{atomic,"S$"}
Now this is arguably quite cryptic if you are not familiar with Erlang Match Specifications. Very briefly, a match specification is a list of match functions
.
Each match function
is a tuple of a match head
, guards
and results
.
The match head
is a sort of record template, where attributes in the records can be bound to variables, the variables being expressed as atoms with the form $N
. Attributes that we are not interested in can be bound to the “don’t care” variable '_'
. In our example, we have bound distance
to the variable '$1'
and duration
to '$5'
.
Guards
is a list of matching conditions expressed in tuples with the first element the matching condition function. In our example, we have two matching conditions. The first is that distance
is greater than or equal to MinDistance
. The second says Place
is one of the three elements in itinerary
.
Lastly results
says what we want to extract from the records that match. In our example we simply fetch the duration
.
Querying using match specifications tends to be more efficient than going through the entire table with a fold
.
The last method we’ll examine is by means of Query List Comprehensions implemented in the Erlang distribution’s module qlc
. It is a generic set of functions to work with abstractions called QLC Tables. table/1
and table/2
functions in mnesia
, ets
and dets
modules, provide a way to obtain a so-called query handles on mnesia
, ets
and dets
tables respectively.
Queries list comprehensions are like list comprehensions, but instead of applying to and producing lists, they apply to and produce query handles. To obtain the result of a query, qlc:e/1
is applied to the query handle.
We can get all records of a table by evaluating the query handle corresponding to an mnesia table
142> QH = mnesia:table(ride).
{qlc_handle,{qlc_table,#Fun<mnesia.23.126602418>,true,
#Fun<mnesia.24.126602418>,#Fun<mnesia.25.126602418>,
#Fun<mnesia.26.126602418>,#Fun<mnesia.29.126602418>,
#Fun<mnesia.28.126602418>,#Fun<mnesia.27.126602418>,'=:=',
undefined,no_match_spec}}
143> mnesia:transaction(fun() -> qlc:e(QH) end).
{atomic,[{ride,"A",
#{day => 13,month => 6,year => 2022},
83,29,
{"p1","p2","p3"}},
{ride,"A",
#{day => 15,month => 6,year => 2022},
22,10,
{"p1","p3","p4"}},
{ride,"A",
#{day => 18,month => 6,year => 2022},
36,18,
{"p2","p3","p1"}},
…
We can transform a query handle by applying qlc:q/1
to a query list comprehension. We’ll do that in blog:q3/2
, the implementation of our previous query on rides longer than a certain distance and with a given place in the itineray. In order to use qlc
, we must include qlc.hrl
-include_lib("stdlib/include/qlc.hrl").
q3(MinDistance, Place) ->
Find =
fun() ->
Table = mnesia:table(ride),
Query = qlc:q([R#ride.duration
|| R = #ride{distance = Distance,
itinerary = Itinerary,
duration = Duration}
<- Table , Distance >= MinDistance,
lists:member(Place, tuple_to_list(Itinerary))]),
qlc:e(Query)
end,
mnesia:transaction(Find).
Once again, if we compile and run the function, we’ll get the same results
165> c(blog).
{ok,blog}
166> blog:q3(10, "p2").
{atomic,"S$"}
Indexes
When queries could have been more efficient if only some other field of the record was the key, we can make use of indexes, or secondary keys. They are particularly useful when matching fields of the record with specific values, rather than constraints.
We add an index, or secondary key to a table with mnesia:add_table_index/2
and then reading a record employing the secondary key with mnesia:index_read/3
179> mnesia:add_table_index(ride, distance).
{atomic,ok}
180> mnesia:transaction(fun() -> mnesia:index_read(ride, 36, distance) end).
{atomic,[]}
181> mnesia:transaction(fun() -> mnesia:index_read(ride, 16, distance) end).
{atomic,[{ride,"B",
#{day => 21,month => 7,year => 2022},
52,16,
{"p4","p3","p1"}}]}
We could have made the same query even without the index on distance using mnesia:match_object/1
, which we haven’t talked about yet
183> rr("blog.erl").
[accounting,ride]
184> mnesia:transaction(fun() -> mnesia:match_object(#ride{distance = 16, _ = '_'}) end).
{atomic,[#ride{rider = "B",
date = #{day => 21,month => 7,year => 2022},
duration = 52,distance = 16,
itinerary = {"p4","p3","p1"}}]}
An aside. To try this out directly from the shell, I imported the record definitions with the shell’s rr/1
function, with the added benefit that the output got printed as a record instead of a tuple.
Even though it is not obvious from it, mnesia tries makes use of any indexes that exist on the table by automatically using mnesia:index_match_object/2
. In fact, mnesia does that also with query list comprehensions.
Fault Tolerance
Let’s now have a look at how table replication to different nodes helps make a fault-tolerant database. This makes mnesia really stand apart, as I mentioned in the introduction.
Let’s begin by creating three directories for three different nodes, n1, n2 and n3, and start the nodes ensuring that mnesia’s environment variable dir
is bound to the path of one of the three directories.
$ mkdir /tmp/db1 /tmp/db2 /tmp/db3
$ erl -sname n3 -mnesia dir '"/tmp/db3"'
Eshell V12.0.2 (abort with ^G)
(n3@arif-mint)1> nodes().
[]
(n3@arif-mint)2> [net_adm:ping('n1@arif-mint'), net_adm:ping('n2@arif-mint')].
[pong,pong]
(n3@arif-mint)3> Nodes = [node() | nodes()].
['n3@arif-mint','n1@arif-mint','n2@arif-mint']
(n3@arif-mint)4> [rpc:call(N, mnesia, system_info, [use_dir]) || N <- Nodes].
[false,false,false]
Now we create the schema
(n3@arif-mint)5> mnesia:create_schema(Nodes).
ok
(n3@arif-mint)6> [rpc:call(N, mnesia, system_info, [use_dir]) || N <- Nodes].
[true,true,true]
Next we start mnesia on all the nodes
(n3@arif-mint)7> [rpc:call(N, mnesia, start, []) || N <- [node() | nodes()]].
[ok,ok,ok]
Now we can create the tables. To allow us to work in the shell, we import the record definitions from our module blog and print an empty record to get the attributes.
(n3@arif-mint)8> rr(blog).
[accounting,ride]
(n3@arif-mint)9> #ride{}.
#ride{rider = undefined,date = undefined,
duration = undefined,distance = undefined,
itinerary = undefined}
(n3@arif-mint)10> mnesia:create_table(ride, [{attributes, [rider, date, duration, distance, itinerary]}, {disc_copies, Nodes}, {type, bag}]).
{atomic,ok}
(n3@arif-mint)11> #accounting{}.
#accounting{rider = undefined,read_count = 0,
write_count = 0}
(n3@arif-mint)12> mnesia:create_table(accounting, [{attributes, [rider, read_count, write_count]}, {disc_copies, Nodes}, {type, set}]).
{atomic,ok}
(n3@arif-mint)13> [rpc:call(N, mnesia, system_info, [tables]) || N <- Nodes].
[[accounting,ride,schema],
[accounting,ride,schema],
[accounting,ride,schema]]
Let’s now write a ride record and verify we can read it on all of the nodes
(n3@arif-mint)18> mnesia:transaction(fun() -> mnesia:write(#ride{rider = "R", distance = 10, duration = 32}) end).
{atomic,ok}
(n3@arif-mint)19> [rpc:call(N, mnesia, dirty_read, [ride, "R"]) || N <- Nodes].
[[#ride{rider = "R",date = undefined,duration = 32,
distance = 10,itinerary = undefined}],
[#ride{rider = "R",date = undefined,duration = 32,
distance = 10,itinerary = undefined}],
[#ride{rider = "R",date = undefined,duration = 32,
distance = 10,itinerary = undefined}]]
Now we restart node n1, start mnesia and verify that we can read the record we wrote from node n3
$ erl -sname n1 -mnesia dir '"/tmp/db1"'
Eshell V12.0.2 (abort with ^G)
(n1@arif-mint)1> mnesia:start().
ok
(n1@arif-mint)2> nodes().
['n2@arif-mint']
(n1@arif-mint)3> mnesia:dirty_read(ride, "K").
[{ride,"K",undefined,53,20,undefined}]
Well, I suppose that should be sufficient to convince us about the resilience of mnesia when we have to do with failing nodes.
However, don’t get deceived into believing mnesia is some kind of magical solve-all tool. Certain failure scenarios can leave the nodes in an inconsistent state. When that happens mnesia can detect it and stop with a specific message. mnesia provides tools to deal with such situations, the simplest being declaring one of the nodes as the master node and have it replicated to the remaining nodes. But we are into advanced territory, not in the realm of a simple getting started. What’s important is to remember that real problems do exist, but there are tools to help with solving them.