Mysql comme data warehouse pour le décisionnel
Je travaille actuellement sur le jeu de données netflix (je voulais d'abord étudier Kettle pour comparer avec Talend, ce sera pour plus tard).
Le jeu de données fait 700Mo en format texte (non compressé, mais sans redondances) ou 100 millions de lignes, dans la DB il prend 4Go (je suis uniquement en InnoDB et Mysql 5.0). Je n'ai qu'1 Go de RAM donc ça ne tiens pas en mémoire, ni la table, ni même l'index de la clé primaire. Je liste donc les avantages et limites de MySql que j'ai pu voir dans ce cas et des tips (histoire que moi non plus je ne les oublie pas).
Positif :
* Le système de cache des requêtes. En gros, si vous faites un select qui prend plusieurs minutes à se faire, une exécution de la même requête plus tard sera instantanée. Ca me semble évident pour un data warehouse où il y a peu d'ajout/update.
Dans le fichier de conf :
query_cache_limit = 32M # taille max d'une requête cachée
query_cache_size = 64M # buffer pour le cache
et des requêtes liées :
mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 2 |
| Qcache_free_memory | 66746000 |
| Qcache_hits | 507 |
| Qcache_inserts | 122 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1868 |
| Qcache_queries_in_cache | 31 |
| Qcache_total_blocks | 94 |
+-------------------------+----------+
* J'aime beaucoup l'aspect de modularisation des moteurs, un jour il y aura peut-être un moteur multi-dimensionnel natif ce qui serait excellent pour de l'OLAP. Mais en pratique je ne fait que du InnoDB qui semble être plus performant sur les benchs que j'ai vu.
Limites :
* Si un index ne tiens pas entièrement en mémoire, vous pouvez oublier. La création de l'index demande des accès aléatoires, vous allez donc saturer très vite la machine en I/O. On commence la création avec 20k insertion/sec puis ça tombe vers 100. La solutions est le partitionnage horizontal. Avec la version 5.1 c'est très facile. Avec la version 5.0 il y a les tables MERGE, mais ce n'est que pour les tables MyISAM. J'ai donc fait un partitionnement à la main. La clé primaire étant (userId, movieId), j'ai fait 40 tables avec pour chaque n-uplet avec i = userId % 40, le n-uplet est stocké dans la table i. J'ai fait de même avec movieId, ça double donc le stockage, mais permet une recherche très rapide selon userId ou movieId. Possible de faire une vue au dessus de tout cela pour simplifier la lecture.
* En JDBC le résultat d'un SELECT est transmit en entier, donc si vous faites un SELECT * from fact, vous ramenez 4Go, ce qui avec 1Go de RAM ne fonctionne pas. Mysql ne regarde pas l'option setFetchSize. Il est possible de faire en sorte que Mysql retourne les résultats petit à petit, mais c'est un hack complexe et qui ne semble pas mature (je n'ai pas essayé). Là aussi faire mes 40 tables a simplifié les choses, il suffit de faire les SELECT séquentiellement sur les 40 tables chacune ne faisant que 100Mo environ. Comme avant, ça complexifie un peut les choses, mais encapsulé dans un DAO ça passe bien.
* Par défaut, les tables innodb sont stocké dans un seul fichier (ib_data), ce fichier ne peut être réduit, même si vous supprimez toutes les tables. Donc à une erreur près ce fichier peut devenir immense. Il est possible de faire un fichier par table (donc plus simple à gérer, vous supprimer la table il disparaît). Ma config innodb (sans doute pas optimal) :
innodb_flush_log_at_trx_commit = 0 # attention perfs vs securite
innodb_buffer_pool_size = 512M # le plus important
innodb_additional_mem_pool_size = 32M
innodb_file_per_table
innodb_log_file_size=512M
innodb_log_buffer_size=128M # un log trop petit fera plusieurs I/O pour le log par secondes et réduit trop les perfs.
Autres tips :
* show engine innodb status; pour avoir des infos sur ce qui se passe chez innodb, assez exhaustif.
Conclusion :
Mysql a encore des limites assez gênantes comme data warehouse pour le décisionnel, néanmoins, une fois qu'on connaît les workaround, il est plutôt efficace. On ne peut juste pas faire les choses sans se demander comment MySQL va les traiter. C'est un peu dommage, mais il n'y a pas de magie dans MySQL. Je note que je n'ai pas encore utilisé les autres DBMS de façon aussi poussée.
Répondre avec citation
Partager