Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What are the advantages of Mnesia over major SQL database implementations and how it differs to them?

Can I use the database to hold really huge amounts of data without noticeable performance degradation ?

share|improve this question
4  
I think this question needs a little more focus. Can you list the criteria you would use to judge advantages or differences from the other database implementations? This really seems like a candidate for a wikipedia article/list, not really something that can be answered here. Additionally, considering as how Mnesia is really more akin to CouchDB, it's not fair to ask how it compares to "major" SQL implementations without naming the ones you want to compare to. Compared to SQLServer or Oracle it's not even close node-to-node for performance. –  jcolebrand Jan 7 '11 at 0:50

3 Answers 3

From the documentation:

Mnesia is a distributed Database Management System, appropriate for telecommunications applications and other Erlang applications which require continuous operation and soft real-time properties. It is one section of the Open Telecom Platform (OTP), which is a control system platform for building telecommunications applications.

In particular the very high level of fault tolerance which is required in many nonstop systems, combined with requirements on the DBMS to run in the same address space as the application, have led us to implement a brand new DBMS. called Mnesia. Mnesia is implemented in, and very tightly connected to, the programming language Erlang and it provides the functionality that is necessary for the implementation of fault tolerant telecommunications systems. Mnesia is a multiuser Distributed DBMS specially made for industrial telecommunications applications written in the symbolic programming language Erlang, which is also the intended target language. Mnesia tries to address all of the data management issues required for typical telecommunications systems and it has a number of features that are not normally found in traditional databases.

In telecommunications applications there are different needs from the features provided by traditional DBMSs. The applications now implemented in the Erlang language need a mixture of a broad range of features, which generally are not satisfied by traditional DBMSs. Mnesia is designed with requirements like the following in mind:

Fast real-time key/value lookup

Complicated non real-time queries mainly for operation and maintenance

Distributed data due to distributed applications

High fault tolerance

Dynamic re-configuration

Complex objects

What sets Mnesia apart from most other DBMSs is that it is designed with the typical data management problems of telecommunications applications in mind. Hence Mnesia combines many concepts found in traditional databases, such as transactions and queries with concepts found in data management systems for telecommunications applications, such as very fast real-time operations, configurable degree of fault tolerance (by means of replication) and the ability to reconfigure the system without stopping or suspending it. Mnesia is also interesting due to its tight coupling to the programming language Erlang, thus almost turning Erlang into a database programming language. This has many benefits, the foremost is that the impedance mismatch between data format used by the DBMS and data format used by the programming language, which is used to manipulate the data, completely disappears.

Mnesia versus MySQL, performance:

ejabberd consumes less computational resources when using some *SQL database than when using internal Mnesia. You are probably interested in that topic when you have many concurrent users (more than 1000, for example). With few concurrent users ejabberd's CPU consumption is negligible so admins of small servers don't care to setup an external SQL server and database.

CouchDB v. Mnesia, V. MySQL and other Mnesia topics:

One insight that immediately came to mind is that while it was blatantly obvious to me how to structure the data for MySQL, it is less so for Mnesia, and for CouchDB I am still not entirely sure of the best approach yet. For now, here are a couple of the more obvious points:

A ‘record’ has a ‘numplays’ field which obviously indicates how many times it has been played. This is fine in MySQL, but if I just incorporate this field into a document for CouchDB I will get a complete duplicate revision of the document in the database every time this one number changes, which seems awfully inefficient.

The three-table layout in MySQL of records, tags, and a link table between them (see the script if that isn’t clear) is (to me at least) obviously the right solution, but there are many possible ways of doing this in both Mnesia and CouchDB and I find I don’t intuitively have the answers.

In short, it's designed for very specific purpose and seems well engineered to suit the purpose. No one database can be abstractly compared to another. Only through the use of requirements can elements of commensurability be induced.

share|improve this answer

Sorry for being late to the party. :) Here's my answer, based on having used Mnesia since 1996 and various other database technologies since 1988.

Mnesia and MySQL are indeed different beasts, and which one is the best depends very much on how you intend to use it.

If your application is written in Erlang, Mnesia allows you to store the data in the same memory space as your application, which means you can fetch a single data object as quickly as a few microseconds. This is not possible in MySQL, since your application and the database will be separated in memory. The reason why Mnesia can do this and still be robust, is that Erlang implements memory 'protection' at the language level.

Overall, SQL databases tend to favor throughput over latency, and when it comes to latency, Mnesia+Erlang are generally outstanding. You need to decide which one is most important to you. As it says in the docs (above), Mnesia's target applications were telecom switching applications, where response time requirements for e.g. a call setup were around 20 ms. This essentially meant that you could read from the database only if the data was in shared memory, but would avoid writing to persistent storage on a per-call-setup basis. OTOH, these applications have practically no need for ad-hoc query support, and do not use very large data sets. Some work has been done to extend the suitability of Mnesia for other domains, but it is not a priority for the Erlang/OTP development team. Mnesia is what it is, and is likely to stay that way.

In the link above where Mnesia and MySQL are compared for speed, one needs to remember that it's in eJabberd, which runs against a single server if it's MySQL and runs a fully replicated database if it's Mnesia - and large eJabberd clusters can have as much as 10 or more erlang nodes (and thus, 10 or more Mnesia replicas). From a redundancy standpoint, this is fairly ridiculous and costly, and Mnesia by no means forces you to do so. It obviously gives wicked-fast reads on each node, but writes will be very expensive. Several comparisons I've read have ended up comparing distributed Mnesia with a single-node MySQL; if redundancy is not needed for MySQL, it shouldn't be required for Mnesia either. Mnesia is quite flexible in letting you choose replication patterns, and data location is transparent to the application.

Mnesia is also not limited to 2 GB per table (although a particular storage option is). The largest Mnesia database I know of has ca 600 GB of data in (64-bit) RAM+disk - although I do not recommend this. Anything up to 10-20 GB should be perfectly fine with modern hardware though, but skip disc_only_copies entirely and use disc_copies - buy more RAM if you have to. I'd think twice before using the sharding support (mnesia_frag) - it works, but is rarely worth the trouble.

Perhaps the biggest difference between Mnesia and MySQL is SQL itself: Mnesia doesn't really have comparable functionality; QLC offers some support for ad-hoc queries, but it is not in the same league as SQL, and neither is the level of query optimization. In tooling and provisioning, MySQL is also superior, and if you need analytics, there's no question which one you should choose (i.e. NOT Mnesia).

The best way to view Mnesia is as an extension to the Erlang language. It puts data right at your fingertips, and is excellent for small data sets where the data structure and access patterns are well known. For this purpose, using MySQL is about as uncomfortable as is using Mnesia for the things where MySQL works best.

Most applications fall somewhere in between, and this is where it becomes a judgement call. You may well end up using both...

share|improve this answer
    
Thanks for the answer. It is the best explanation I have read on mnesia. –  Akshat Jiwan Sharma Jan 21 at 16:59
    
Thanks for sharing your experience with us, its far more valuable than reading any blog. –  Rahul Gautam May 21 at 10:41
    
Great answer,but i am even more confused now. –  DarkHorse yesterday

No, I wouldn't say that Mnesia is good for large amount of data. You can choose to use Ets or Dets as backend. If you choose Ets, your database will only be in-memory and very fast but the data isn't persistent. And if you want your data persistent (saved on disk) you need to use Dets, that has a 2GB limit, so your database can't hold more than 2GB of data.

You can use a custom backend e.g. innostore that is used in the Riak NoSQL database.

The advantages with Mnesia is that it is a distributed database so it is very easy to do fault tolerant systems if you have more than one computer. And it is very easy to use in Erlang since it's an in-language database and acts "like a function". And it is also super-fast if you only need an in-memory database e.g. like a cache.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.