Add new comment

MongoDB vs SQL Server ?

Quelques épisodes de la vie NoSQL vue par YesSQL.

Ce sujet a été monté lors de la validation technique d'un projet. Le scénario des tests est :

  • estimer l'insertion intensive des données depuis les nombreuses unités (par exemple, les capteurs)
  • comparer la simplicité et la performance des requêtes « typiques »

Le test d'insertion, ou l'Expulsion du Paradis

J'ai limité l'insertion des données depuis les multiples capteurs par 10 millions lignes ce que est corresponde à 1 heure de travail environ.

La version MongoDB 2.0.2 pour Windows 64-bits est disponible pour télécharger sur le site web. L'installation passe sans poser des problèmes. SQL Server 2008 R2 Developer Edition a été installé déjà sur mon ordinateur. Si vous ne l'avez pas, même la version gratuit SQL Server Express est convenable en cadre de nos tests. L'ordinateur est relativement faible : CPU 2 cœur 2,4 GHz, 6 Go de mémoire vive et un seul disque dur de 250 Go pas trop rapide (7200 tours/min).

Les structures cibles sont identiques mais la forme de la collection MongosDB et celle de la table SQL Server sont bien différentes. Ci-joint les scripts de la création et d'insertion des données (MongoDB, SQL Server).

Une petite remarque concernant le script SQL. Le monde YesSQL est un monde des transactions. Ce n'est pas la peine d'insérer les lignes dans une table comme dans un fichier plat. Ce serait idiot et beaucoup moins rapide. Donc notez que dans le monde SQL il vous faut ouvrir une transaction, insérer quelques lignes puis valider cette transaction et reprendre. Dans cette exemple les paquets comptent de 10 lignes ce que accélère en 5 fois l'insertion « ligne par ligne ».

L'insertion en bloc (bulk insert ou bcp) est hors ce sujet puisqu'elle ne peut pas être appliquée dans tous les cas et puis il n'y a pas grande choses d'en tester. Juste notez, que l'insertion en bloc est disponible en MongoDb ainsi qu'en SQL Server.

Selon la documentation, MongoDB « does not use traditional locking or complex transactions with rollback, as it is designed to be lightweight and fast and predictable in its performance ». Je suis tout à fait d'accord, en absence des transactions l'insertion « ligne par ligne » est plus rapide en MongoDB. Une petite magie pour ceux qui ne sont pas au courent.

Après le test nous avons les mesures de la vitesse d'insertion et celles de la taille de la base des données. Les 10 millions de documents prennent 3,95 Go alors que les 10 millions de lignes n'occupe que 0,5 Go sans la compression.

Ci-dessous les graphiques de la vitesse d’insertion.

 

Malgré que le temps d'insertion d'un documente dans la collection est 3 fois plus long que celui d'insertion des 10 lignes dans la table, ce temps peut être suffisante par rapport des contraintes techniques. La dégradation temporaire (voir le graphique 2) ainsi que la consommation vorace de mémoire par MongoDB ont été plus intéressant pour moi : 4 Go contre 1 Go de SQL Server qui est limité de 2 Go.

Est-il possible limiter la consommation de mémoire vive en MongoDB ? La réponse actuelle est « non ». Il y a plus d'un ans que le demande a été créé mais il n'a pas de priorité pour les développeurs. En cas commun il est recommandé d'avoir une serveur virtuel dédie dont la mémoire est alloué par MongoDB. Certaines recommandations informels peuvent être trouvés dans Internet (exemple). Il ne reste qu’espérer de mieux.

C'est pourquoi il me fallait redémarrer le service « mongod » afin de libérer la mémoire et vider la cache (DBCC pour SQL Server).

Oh, la cache, vous aller voir comment ces SGBD en profitent.

Les requêtes, ou la Descente aux Enfers

C'est le temps d'attaquer les données insérées par les requêtes !

Ma vue d'ensemble : je suis au début des années 1990. Ce n'est même pas FoxPro v.2.0 qui avait SQL incorporé déjà. C'est Clipper 87 dont la version est sorti en été 1987. Je vois le même Clipper mais en JavaScript : ces fameux cycles imbriques, la sélection explicite d'un index, application des filtres, calculs des valeurs... Un peu de nostalgie...

MongoDB n'a pas des fonctions d'agrégation. Afin de calculer la somme ou la moyenne (arithmétique) vous avez les développer en incorporant dans la requête ou utiliser la méthode plus générique MapReduce. Si la requête d'agrégation renvoie plus de 10 milles documents le MapReduse est obligatoire. Il existe aussi la méthode collection.count(), mais elle ne peut être appliquée qu'en les collections hors du contexte de la requête.

Outre les fonctions SQL standards comme count, sum, avg etc, j'avoue que vous pouvez développer les fonctions d'agrégation en C# depuis SQL Server 2005. Mais je vous recommande bien réfléchir avant partir chercher les aventures.

Comment trier les résultat d'une requête d'agrégation ? Vous n'en pouvez en aucun façon. Il vous faut faire le tri sur le client. Le dialogue suivante je laisse sans commentaires.
Q: How can I sort on the resultset of the group by operation?
A: Group currently just returns an object, so you should be able to sort easily client side

Comme le résultat, le code claire et simple des requêtes en langage déclarative SQL se transforme au « spaghetti » des fonctions impératives JavaScripts. Les bons gars ont dessiné la schéma de transformation d'une requête MySQL vers MongoDB. Vous aller voir « l'évolution » dans les exemples utilisées par ces tests.

La requête Q1

Récupérer la date maximale et minimale.

SQL Server MongoDB
SELECT MIN(measureDate), MAX(measureDate)
FROM dbo.measuresData
var minDate = new Date(1900,1,1,0,0,0,0);
var maxDate = new Date(2100,1,1,0,0,0,0);
db.measuresData.group(
{
  key: { },
  reduce: function(obj, prev) 
  {
    if (obj.measureDate.getTime() < prev.minMsec) 
      prev.minMsec = obj.measureDate.getTime(); 
    else if (obj.measureDate.getTime() > prev.maxMsec) 
      prev.maxMsec = obj.measureDate.getTime(); 
  },
  initial: { minMsec: maxDate.getTime(), maxMsec: minDate.getTime() }, 
  finalize: function(out)
  { 
    out.minMeasureDate = new Date(out.minMsec);
    out.maxMeasureDate = new Date(out.maxMsec);
  }
})
.forEach(printjson);

MongoDB impose convertir les dates en format numérique (le nombre de millisecondes depuis 01/01/1970) sinon la comparaison des dates à l’intérieur de la fonction produit une erreur « TypeError: this["get" + UTC + "FullYear"] is not a function nofile_b:2 ».

Mon expérience « Clipper » est valable depuis 15 ans. Je construit l'index sur la date « measureDate » puis je fait le tri de la collection et je prend le première valeur. Ensuit je répète cela en ordonnée les dates en mode descendant.

db.measuresData.ensureIndex({measureDate: 1});
 
db.measuresData.find({}, {measureDate: 1}).sort({measureDate: 1}).limit(1);
 
db.measuresData.find({}, {measureDate: 1}).sort({measureDate: -1}).limit(1);

La requête Q2

Calculer la somme des valeurs entières et flottantes en les regroupant par l'état et la groupe des capteurs.

SQL Server MongoDB (sans trier les résultats)
SELECT SUM(intVal), SUM(floatVal), stateId, groupId
FROM dbo.measuresData
GROUP BY stateId, groupId
ORDER BY stateId, groupId
db.measuresData.group(
{
  key: { stateId: true, groupId: true },
  reduce: function(obj, prev) 
  {
    prev.sumIntVal += obj.intVal; 
    prev.sumFloatVal += obj.floatVal; 
  },
  initial: { sumIntVal: 0, sumFloatVal: 0.0 }
})
.forEach(printjson);

La requête Q3

Calculer le nombre total des capteurs et le nombre des capteurs uniques en les regroupant par l'état et la groupe des capteurs.

SQL Server MongoDB (sans trier les résultats)
SELECT COUNT(deviceId), COUNT(DISTINCT deviceId), stateId, groupId
FROM dbo.measuresData
GROUP BY stateId, groupId
ORDER BY stateId, groupId
db.measuresData.group(
{
  key: { stateId: true, groupId: true },
  reduce: function(obj, prev) 
  { 
    prev.count++;
  },
  initial: { count: 0 },
  finalize: function(out)
  {
    out.distCount = db.measuresData
      .distinct("deviceId", {stateId: out.stateId, groupId: out.groupId})
      .length;
  }
})
.forEach(printjson);

Le journal d’exécution MongoDB affiche 35-40 secondes pour chaque appel de « finalize() » dont le nombre est 200 environ. J'annule la requête et je construit un index sur les éléments « stateId » et « groupId ». Autrement dit, sans l'intervention DBA la requête simple ne fonctionne pas au point de vue d'utilisateurs grâce à son temps magnifique de réponse.

db.measuresData.ensureIndex({stateId: 1, groupId: 1})

L'index est construit lors de 140 secondes (son homologue en SQL Server prend 20 secondes), la taille de la bas de données grandit au 4,2 Go.

On relance la même requête. C'est gagné ! Le temps d'appel d'une fonction est réduit au 300 milliseconde mais augmente aléatoirement jusqu’au 1,2 seconde. Le temps total dépasse 15 minutes...

La requête Q4

Calculer la somme des valeurs entières et flottantes en les regroupant par l'état et la groupe des capteurs et en réduisant le nombre des lignes traitée par la plage de dates. Je prend l'intervalle de 1 minute au milieu entre les dates extrêmes (voir Q1) ce que donne 500 milles de lignes environ au lieu des 10 millions.

SQL Server MongoDB (sans trier les résultats)
SELECT SUM(intVal), SUM(floatVal), stateId, groupId
FROM dbo.measuresData
WHERE measureDate BETWEEN '20120208 22:54' AND '20120208 22:55'
GROUP BY stateId, groupId
ORDER BY stateId, groupId
var date1 = new Date(ISODate("2012-02-08T15:20:00.000Z"));
var date2 = new Date(ISODate("2012-02-08T15:21:00.000Z"));
 
db.measuresData.group(
{
  key: { stateId: true, groupId: true },
  cond: {measureDate: { $gte: date1, $lt: date2 } },
  reduce: function(obj, prev)
  {
    prev.sumIntVal += obj.intVal; 
    prev.sumFloatVal += obj.floatVal; 
  },
  initial: { sumIntVal: 0, sumFloatVal: 0.0 }
})
.forEach(printjson)

La requête Q5

Calculer le nombre total des capteurs et le nombre des capteurs uniques en les regroupant par l'état et la groupe des capteurs et en réduisant le nombre des lignes traitée par la plage de dates. Je prend la même intervalle (voir Q4).

SQL Server MongoDB (sans trier les résultats)
SELECT COUNT(deviceId), COUNT(DISTINCT deviceId), stateId, groupId
FROM dbo.measuresData
WHERE measureDate BETWEEN '20120208 22:54' AND '20120208 22:55'
GROUP BY stateId, groupId
ORDER BY stateId, groupId
var date1 = new Date(ISODate("2012-02-08T15:20:00.000Z"));
var date2 = new Date(ISODate("2012-02-08T15:21:00.000Z"));
 
db.measuresData.group(
{
  key: { stateId: true, groupId: true },
  cond: {measureDate: { $gte: date1, $lt: date2 } },
  reduce: function(obj, prev) 
  { 
    prev.count++;
  },
  initial: { count: 0 },
  finalize: function(out)
  {
    out.distCount = db.measuresData
      .distinct("deviceId", {stateId: out.stateId, groupId: out.groupId})
      .length;
  }
})
.forEach(printjson);

Chronométrage des requêtes

Même après l'optimisation le temps des requêtes MongoDB reste insatisfaisant. De la part de SQL Serveur tout l'optimisation est la création d'un index cluster sur la date (il faut transformer la clé primaire en non-cluster préalablement). Les requêtes Q2 et Q3 imposent l'analyse de tout la table et donc l'optimisation SQL n'a pas été fait.

Chaque requête a été lancé 3 fois. Les tirs 2 et 3 montrent bien l’efficacité de la cache SQL Server.

  SQL Server   MongoDB
Tirs / temps, sec 1 2 3   1 2 3
Q1 7 1 1   190 185 189
Q2 8 3 3   >7200
Q3 26 20 20   345 341 349
Q4 8 1 1   22 22 22
Q5 15 10 12   141 141 141
Après l'optimisation
Q1 0 0 0   0 0 0
Q2 8 3 3   322 800 619
Q3 Les mêmes       Les mêmes    
Q4 1 1 1   12 12 13
Q5 7 7 7   85 84 85

Conclusions, ou Avancer dans le Passé

Outre la complexité d'écriture des requêtes simples, le temps de réponse inacceptable, le stockage physique exhaustive, l'utilisation incontrôlé de mémoire vive et beaucoup d'autres choses dont je n'ai pas pu revoir dans ce test court, MongoDB ne sait pas utiliser la cache... Tous répertorié au-dessus ne me donne pas le droit dire « le SGBD industriel ».

Il faut avoir des bonnes raisons afin de choisir NoSQL. Par exemple, les données faiblement structurées. Ou au contraire, les structures très compliquées dont la présentation en forme relationnelle est gênant en interrogation et chère en exploitation et évolution même à travers de la couche de vues. Les NoSQL plus connu depuis longtemps sont les cubes OLAP et XML.

Il est plus simple dire ce que ne sont pas les raisons de choisir NoSQL :

  • vos incompétences en SGBD et notamment en SQL/SGBDR ainsi que l’espoir d’en éviter
  • l’émeute contre « Big-3 » (Oracle, IBM, Microsoft). Puisqu'il y a des PostgreSQL, Interbase/Firebird et MySQL sous condition ainsi que dizaines éditeurs commerciales « non grande marque »
  • le remplacement des argumentes techniques par ceux de marketing comme « la nouvelle vague », « la tendance contemporaines », « nouvelles technologies », « la tendance progressive » et les autres mots de désinformation