What are the advantages of running NoSQL (ex MongoDB) over MySQL, PostGRE SQL or MSSQL in Drupal? Are the advantages gained from simply using the storage or does some Drupal configuration need to change?
-
This is a question to which Károly Négyesi would give an "authoritative" answer. He surely knows the advantage of using MongoDB with Drupal.– apaderno ♦Mar 18, 2011 at 13:15
-
Read my mind.. very interested in other options if there is substantial benefits, and of course the pros come with cons.– KevinMar 18, 2011 at 13:21
Add a comment
|
3 Answers
MongoDB can be used to store most or all your entities into fast, document-oriented storage. This type of storage scales way better then the standard SQL based storage we have in Drupal core (which is based on a "one table per field" schema).
In the current state of Drupal 7, you would have:
- The base table of the entity stored on SQL (ie. the users table, the node table, etc.)
- All fields stored in SQL
- The properties of the entities from their base tables duplicated in MongoDB
This allows fast querying on the entities on MongoDB, and the ability to add complex indexes that no Opensource SQL database support (including indexes across tables). At the same time, you don't lose interoperability because the base table of the entity is still stored in SQL and can thus be joined by modules that are still SQL-only (like Flag).
This type of fast querying is available thanks to the EntityFieldQuery mechanism, a way to build queries on entities, their properties and their fields in an abstract manner. The default implementation in core translate those queries to SQL, but the MongoDB module has a full-featured implementation that can satisfy those queries from MongoDB directly.
Thanks to the EntityFieldQuery backend for Views, you can easily leverage this power, by using the tools you are used to. The only downside is that relationships are not supported (but in practice you rarely need them anyway - and this can be worked-around by pushing additional data into the entity object and add exposing them as additional properties of the entity).
In a nutshell, as soon as query performance is a problem on your project, which happens as soon as you have a significant dataset (let's say starting at a few tenth of thousands of entities on a given entity type), MongoDB is a net gain for very very few drawbacks. Highly recommended.
-
Damien Tournoud: If you experience performance problems with only a few tenth of thousands entries, then there probably is an underlaying problem (DBMS configuration, poorly written queries, it can be anything). If the underlaying SQL schema is good enough, you shouldn't have to worry before a million entries on a single table (but fields can grow fast if you consider revisions and multi-valued fields probably).– PierreMar 18, 2011 at 15:12
-
My point exactly: our schema is normalized, and as a consequence has very poor querying performance. To improve query performance, you need to denormalize the schema. The main advantage of using MongoDB in our case is that it is some sort of "automatic denormalization engine". Mar 18, 2011 at 15:46
-
And of course, MongoDB is great here because it is a document oriented database. Storing complex documents like entities in SQL storage is just plain stupid. It is our default implementation, but it doesn't mean you should use it :) Mar 18, 2011 at 15:52
-
@Pierre: Damien talked about a few tenth thousand entities, which can be a completely different thing than table rows. For example, you could have 10 or more fields on that entity, then you have 10 additional tables that needs to be queried with a separate query every time such an entity is loaded. And MongoDB can replace these additional tables, not the entity base table.– BerdirMar 18, 2011 at 16:14
-
2No module should expect the fields to be in MySQL. The only way to query fields on Drupal 7 is EntityFieldQuery. Open a bug in the module if it is querying the field tables directly. I don't know any of those modules at this time. Mar 18, 2011 at 16:24
MongoDB and similar are designed to store structured (hierarchical) data in a relatively flexible way.
For example in Drupal 7, when using field_sql_storage, every field gets it's very own tables. When you attach 10 fields to a content type, you end up with 10 tables in your database. When you load that node, field_sql_storage will execute a query per field and per node (or multiple nodes, when using node_load_multiple).
When you use mongodb_field_storage, you can store all fields of a node in a single document and get with a single query.
You can also store other things like watchdog, sessions, cache, blocks in MongoDB.
You still need MySQL however, MongoDB doesn't replace it (only for specific parts).
Another advantage is that it is easier with MongoDB to scale, you can add many servers to a cluster share the data between them.
-
Hi Berdir! Do you know if I drop MongoDB in an existing proyect to test for performance would I enable & disable the module without consequences? I want to try mongo, but what if it doesn't work or something? Is it safe to try? (I know you can not guarantee it but I wonder what happens in most cases) Apr 27, 2013 at 21:56
-
1Well, for a start, you can't just drop it in. Each field has configured which storage backend it uses you will have to change/re-create your fields, re-create your views (as they need to use the efq_views backend), maybe your own queries if you wrote direct queries against the field data tables. Might be easier to re-create the same structure in a new installation for an initial comparison.– BerdirApr 28, 2013 at 21:08
-
Thanks Berdir! I tried MongoDB a few days ago but there wasn't a noticeable gain in performance, but also I wasn't aware of the things/changes you are telling me right now. I thought "MongoDB should make a difference in larger sites". I'll try MongoDB again. Apr 30, 2013 at 14:12
The pros come with cons.
Drupal as a whole can't be switched to MongoDb, so you will have to support two databases and make sure they work well together.
Many modules will not be able to work with mongodb so you will loose interoperability.
Unless you have a pressing need (like part of your system is not coping with the number of request/or amount of data) I wouldn't switch. And even when you start approaching limits look at throwing hardware at the problem or tuning before switching.
I thought I had answered this before, there is a almost duplicate on SO
