Bonjour,
 
J'utilise actuellement une base de données MySQL pour stocker mes données et étant donné que je commence à avoir des problèmes de lenteurs, je cherche une solution de remplacement. J'ai un unique serveur hébergeant à la fois MySQL et le serveur web (apache + PHP), mes problèmes venant de la partie MySQL. (Bon je ne suis pas du tout sure d'être dans la bonne catégorie..)
 
La solution actuelle
 
Serveur OVH avec Debian GNU/Linux 7.4
Processeur : Intel® Xeon® CPU E5-1620 v2 @ 3.70GHz 4 coeurs 8 threds
64Go de RAM
mysql  Ver 14.14 Distrib 5.5.35, for debian-linux-gnu (x86_64) using readline 6.2
Tables innodb uniquement.
 
Configuration MySQL :
 

 
Les besoins
 
Ma solution n'est pas destinée à avoir beaucoup d'utilisateurs, ni beaucoup de requêtes. (En gros, si on atteint 1000 utilisateurs "inscrits", c'est énorme déjà).
 
Ce que je dois faire, c'est récupérer un fichier de log de plus d'un millions de lignes par jour (on en est aujourd'hui à 1.5millions par jour, et ça devrait augmenter au fur et à mesure du temps), et les stocker dans ma base de données. Chaque ligne de log est traitée afin d'associer certaines valeurs à des ID ou autre. Chaque ligne de log ressemble à quelque chose comme ça :
date;site;utilisateur;pays;nombre;argent
Les données site, utilisateurs, pays, correspondent à des dimensions, il y a en a bien plus que ça (8 actuellement mais il est fort probable que ça évolue. De plus, des dimensions sont liées à ces dimensions. Par exemple, une thématique est liée à chaque site), et nombre et argent correspondent à des métriques (là encore, j'en ai plus que ça, mais il y a peu de chance que ça évolue beaucoup).
 
Le but est d'obtenir des données pour des périodes et des dimensions données. Les plus simples vont être par exemple, la somme de nombre et d'argent (SUM(nombre), SUM(argent)) pour le mois dernier, pour tel site (ou pour d'autres filtres). Mais il faut aussi pouvoir obtenir des données plus "compliquées" comme les 50 sites qui ont fait le plus d'argent sur une période, et pour chacun de ces sites, les 20 thématiques qui ont fait le plus de "nombre" et pour chaque thématique, tous les pays triés par nom. (Et évidemment, cela avec une modularité assez élevée... On peut éventuellement se retrouver avec 20 dimensions à vouloir afficher...)
Ces requêtes très compliquées peuvent prendre du temps sans que ce ne soit un problème, mais les plus simples (en gros, récupérer les données sur une période avec des filtres sur chaque dimensions) doivent pouvoir se faire rapidement (30 secondes, c'est déjà trop long).
 
Il y a aussi une notion de taux de change. Je récupère des données en dollar, et je dois les afficher en euro, selon le taux de change en vigueur actuellement. Cependant, à la fin du mois, un taux de change est choisi pour le mois précédent, et je dois donc appliquer ce taux à toutes les données du mois précédent. (Sachant qu'en fonction de certaines dimensions, je récupère des données directement en euro, donc il ne faut pas que je les mette à jour)
 
Ma solution actuelle
 
Actuellement, j'ai donc une table innodb rassemblant toutes les dimensions, du type :
Date, SiteId, UtilisateurId, PaysId, Nombre, ArgentDollar, ArgentEuro
Des tables Site, Utilisateur, avec leur dimensions associés (par exemple thématique pour un site, age, nationalité par un utilisateur) et les tables associées à ces dimensions.
Afin de diminuer les temps d'affichage de données simple, j'ai aussi créer des "sous tables" du type :
Date, SiteId, Nombre, ArgentDollar, ArgentEuro
Date, UtilisateurId, PaysId, Nombre, ArgentDollar, ArgentEuro
En gros, les métriques restent les mêmes, mais il y a un plus petit nombre de dimensions. Comme cela, j'utilise toujours la table la plus petite en fonction des filtres que les utilisateurs demandent (s'il n'y a qu'un filtre sur le Site, je vais pouvoir utiliser la table avec seulement le Site comme dimension, qui est bien plus petite que la table avec toutes les dimensions).
 
Ces sous tables sont générées "en cascade" (j'utilise toujours la plus petite table contenant toutes les dimensions nécessaires).
 
Pour les requêtes trop compliquées (voir l'exemple plus haut, qui peut avoir beaucoup de dimensions), elles sont faites en background et requêter via une API, donc leur temps d'execution n'est pas critique.
 
Pour le taux de change, je fais passer une update en fin de mois, pour regénérer la valeur ArgentEuro à partir de la valeur ArgentDollar (c'est d'ailleurs pour ça que je garde toujours la métriques argentDollar dans ma base, elle n'est pas utilisé à part pour ça..) sur la plus grosse table, puis je regénère les "sous tables" avec les bonnes valeurs en euro. Je ne peux pas faire passer l'update sur toutes les tables étant donné que pour certaines lignes, en fonction des dimensions, sont déjà en euro (la valeur argentDollar et ArgentEuro sont les mêmes) et ces dimensions doivent donc être disponibles pour que je puisse filtrer dessus.
 
J'ai ajouté des index sur mes tables de rapports sur les champs de type Date, SiteId, UtilisateurId, PaysId, etc.. (les dimensions en gros).
 
J'ai aussi ajouté du "pruning" pour certaines grosses dimensions. Par exemple pour les pays, je ne garde que les 25 pays qui ont fait le plus d'argent chaque jour, et je met tout le reste dans un pays "Autre", afin de diminuer la quantité de données..
 
Mes problèmes
 
Alors j'ai plusieurs problèmes. Le premier, c'est que même des rapports simple (2 dimensions, dont une petite), quand il y a une "grosse" dimension (=beaucoup de données pour cette dimensions. Genre utilisateur, on peut en avoir 20k par jour), ça peut prendre du temps à charger (5-10 secondes, parfois plus, ce qui est beaucoup pour une page web, pour des données assez simples). De plus, ces problèmes risquent de s'intensifier avec le remplissage des tables et le fait que j'ai des données de plus en plus grosse chaque jour).
 
Mon deuxième problème, c'est lorsque j'ai 2 requêtes qui s’exécutent à la fois, ça prend BEAUCOUP trop longtemps. Par exemple, 2 requêtes qui prennent une minute chacune, elles vont prendre 2 minutes l'une à la suite de l'autre, mais si elles s’exécutent en même temps, il y en aura pour 15 minutes. Cela va surtout poser problème en début de mois, quand je fais passer l'update du taux de change, et que je regénère toutes les sous tables (j'ai 22 tables de rapport de ce type en fait...) pour tout le mois. Ces regénérations de sous table prennent très longtemps (dans les 24h), et quand d'autres scripts se mettent en parallèle, ces scripts sont TRES ralentis (par exemple, j'en ai un qui met 1h habituellement et qui a mis 10h ce week-end...) et cela ralenti aussi la regénération des tables, qui ralenti les autres trucs etc... Donc ça provoque des "problèmes" en chaine et je ne vois pas comment gérer ça à part en désactivant mes traitements qui peuvent se faire en parallèle pendant 24h (et quand j'aurais encore plus de données et que ça prendra plus longtemps, il faudra que ce soit désactivé encore plus longtemps... Enfin ça ne me parait pas être une solution...) Je pense que ce problème peut venir de l'accès disque, mais je ne suis même pas sure...
 
Donc voilà, j'aimerais bien savoir vers quoi m'orienter pour améliorer tout ça sur le long terme. Je suis ouverte à toutes les solutions (que ce soit des changements de serveurs, de SGBD, de langages de développement, regarder du côté de la Big Data avec Hadoop, NoSQL et compagnie, etc... Enfin disons que tout est vraiment possible (dans la limite du raisonnable, on ne va pas se faire construire un data center !    )). Et je prend aussi les communautés/forums qui pourraient aussi m'aider sur ce sujet, je ne connais pas très bien les communautés spécialisées là-dedans.  
 
 
Merci !

le truc c'est que tu as le choix :
si ce n'est que de la lecture, tu peux multiplier les serveur de données. tu peux même mettre à jour des serveurs en les déconnectant de ton système, donc plus de soucis de mise à jour. Reste à équilibrer les connexions des utilisateurs sur les bases de données.
 
tu peux aussi agréger tes tables pour diminuer le volume des données à sommer, mais pour ça faut analyser tous les besoins des utilisateurs et ça risque d'être long.
 
tu peux éventuellement utiliser un outil plus spécialisé dans les volumes de données importants, mais là je n'y connais rien du tout.

Vu les volumes, et ce que tu veux en faire, peut être qu'une solution comme elastic search (+logstash pou rle chargement des logs  + kibana pour l'interrogation des données) ou mongodb fonctionnera mieux
 
(en fait, je suis a peu prêt sûr que ça fonctionnera mieux )

Y'a tout un tas de traitement qui me font penser à du data mining. Un outil comme Pentaho serait peut-être plus optimisé pour produire des rapports ou états sur ces fameuses dimensions.
 
Je vois que t'es chez OVH : j'imagine que t'es sur un serveur dédié ? Moi, j'en suis parti à cause de leur Mysql qui était trop poussif. En plus, j'étais limité en nb de requêtes sql / seconde. Il arrivait très souvent qu'en cours d'exécution de scripts faisant pas mal de requêtes, je me faisais déconnecter de la BD. Du coup, je te raconte pas comment les scripts partaient en sucette après ça
 
Avant de passer à du NoSQL, peut-être tester le partitionnement de tables. Mais il faudra effectivement bien choisir les critères de partitionnement.
 
Après, t'aurais peut-être intérêt à faire 2 BD : une qui gère les MAJ de contenus et une autre qui servirait à la génération des états/rapports. Cette 2ème BD serait un extract plus ou moins identique (peut-être faire des consolidations de données pour accélérer la génération des états/rapports). Cette Bd serait générer durant la nuit, par ex (ou à une fréquence hebdo ou mensuelle si c'est pas utile d'avoir un refresh chaque jour).
 
Tu peux regarder aussi du côté de MariaDB, le "remplaçant" de Mysql. A ce qu'il paraît, il serait plus rapide que Mysql à structure de table identique.

 
J'ai pensé en effet à faire de la réplication avec un (ou des) serveur réservé à la lecture, mais il y a bien trop de problèmes liés à l'écriture pour que ce soit une solution pour moi.  
Niveau agrégation, j'en fait déjà pas mal (enfin je suppose que ça correspond à ce que j'appelle le "pruning", et les "sous tables" aussi) mais j'aimerais tout de même pouvoir garder un maximum de possibilités et donc je ne peux pas faire beaucoup plus ici.  
 

 
Je suis en train de lire des infos sur ELK pour voir si ça peut m'aider. Pour mongodb, pourquoi ce choix en particulier ? Est-ce juste pour le côté NoSQL ou parce que MongoDB apporte des choses en plus que les autres solutions NoSQL n'apporteraient pas dans mon cas ?  
 

 
Je ne connais pas du tout Pentaho, je vais regarder, ainsi que MariaDB.  
Je suis bien sur un serveur dédié, mais je n'ai pas du tout l'impression d'avoir ce genre de limitations. D'ailleurs je crois que si c'était le cas, j'aurais déjà pété un câble    
Pour le partitionnement de tables, je suis moyennement chaude... C'est assez important d'avoir des données sur une année entière par exemple, et si je dois faire des "union" de tables pour récupérer les données, j'ai peur que ce soit largement pire ! (mais je n'ai peut-être pas bien compris le principe ! )
Pour les 2 BDD, je ne vois pas bien concrètement. Mais en fait, idéalement, j'aimerais avoir les données de plus en plus rapidement, je commence d'ailleurs à récupérer des données par heure toutes les heures (un process que je dois souvent désactiver car sinon plusieurs process BDD en même temps = problèmes de temps et mon truc qui doit tourner toutes les heures qui finit par mettre 3h à s'éxécuter ! )
 
Enfin en tout cas, merci, c'est ce genre de réponses qu'il me faut ! Je suis pour l'instant dans l'étape d'informations et d'évaluation de toutes les possibilités, et je cherche donc de nouvelles pistes à regarder !

Pour le partitionnement, tu n'y es pas du tout. Il ne s'agit pour toi, faire des "sous-tables" manuellement. C'est clair que ça compliquerait les requêtes. C'est MySql qui gère ça. Quand t'as une table partitionnée, logiquement, t'as toujours une seule table mais physiquement (sur le hdd, donc), t'en a plusieurs. Tu peux partitionner ta table en "lignes" ou en "colonnes". En lignes, tu peux limiter le nb d'enregistrements par table.
En colonne, tu peux indiquer une ou plusieurs colonnes (ie des champs) servant à constituer les partitions. Ca peut être un champ date par ex. Ainsi, tous les enregistrements d'un même mois, d'une même année (dépend de la granularité retenue) seront dans la même table. Donc, au moment de faire une requête portant sur un critère de date, le sgbd va gagner du temps car il saura déjà dans quelle(s) partition(s) aller chercher les enregistrements.
Donc suivant les critères (ie lignes/colonnes) choisis pour partitionner une grosse table en fonction des requêtes sql faites qu prennent beaucoup de temps, ça peut t'en faire gagner

 
Ah oui, j'avais en effet mal compris ! Je pensais à ce genre de partitionnement, mais manuellement ! Étant donné que les données sont le plus souvent affichées par mois, ça peut être une option d'essayer de partitionner par mois... Merci !

Je pesne que tu es complètement dans le use case de base  de ELK. J'utilise une techno similaire au taf, et j'ai un delai de reponse < 10 ms pour des facettes ( des regroupements/aggregat ) sur quelque smillions d'enregistrements, et surtout avec des requêtes bien plus simples à construire
Mongo est une des bases les plus simples et les plus répandues pour expérimenter le nosql "générique"