Aller au contenu


Photo

Quelle solution choisir ?

mysql

  • Veuillez vous connecter pour répondre
3 réponses à ce sujet

#1 Julie LORIN

Julie LORIN

    Member

  • Anciens
  • PipPip
  • 53 messages
  • Cursus:Alumni

Posté 04 août 2014 - 11:17

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 acutelle

 

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 :

key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K

table_cache=200
thread_cache_size=264
query_cache_size=256M
query_cache_limit=256M
join_buffer_size=256K
key_buffer_size=256M
bulk_insert_buffer_size=256M
read_buffer_size=10M
sort_buffer_size=64M
myisam_sort_buffer_size=256M

low_priority_updates=1

myisam-recover         = BACKUP

query_cache_limit       = 2M
query_cache_size        = 16M
query_cache_type        = 1

expire_logs_days        = 10
max_binlog_size         = 100M

innodb_lock_wait_timeout=100
innodb_buffer_pool_size=25G

innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=0
innodb_flush_method=O_DIRECT
innodb_file_per_table
innodb_buffer_pool_instances=5

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étrics (là encore, j'en ai plus que ça, mais il y a peu de chance que ça évolue beaucoup).

Le but est d'otenir 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 probleme, 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

 

Acutellement, 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, nationnalité 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'executent à 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'executent 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 poste dans le sous forum Big Data car j'ai l'impression que certaines problématiques y ressemblent (comment utiliser et faire quelques choses de ces millions de lignes ?), mais je n'ai pas clairement pas des téra de données non plus. (Et de ce que j'en ai lu, je ne suis pas sure que ce soit la bonne orientation...). Je suis ouverte à toutes les solutions (que ce soit des changements de serveurs, de SGBD, de langages de développement, etc... Enfin disons que tout est vraiment possible (dans la limite du raisonnable, on ne va pas se faire construire un data center ! :lol: )). Et je prend aussi les communautés/forums qui pourraient aussi m'aider sur ce sujet, je ne connais pas très bien tout ça..

 

Merci !



#2 Antoine ROY

Antoine ROY

    Member

  • Étudiant
  • PipPip
  • 16 messages
  • LocationParis

Posté 04 août 2014 - 04:18

Ne m'y connaissant pas assez coté software je ne peut pas trop aider, par contre pour le hardware, peux tu nous linker la config de ton serveur stp :) ?


-------------

ni-host.com


#3 Julie LORIN

Julie LORIN

    Member

  • Anciens
  • PipPip
  • 53 messages
  • Cursus:Alumni

Posté 04 août 2014 - 04:45

Bonjour,

 

Ca doit être celui-ci : http://www.ovh.com/f.../2014-SP-64.xml

(Avec 1x2To, l'offre a du légèrement évoluer à ce niveau-là !)



#4 Antoine ROY

Antoine ROY

    Member

  • Étudiant
  • PipPip
  • 16 messages
  • LocationParis

Posté 05 août 2014 - 09:37

Peut être qu'une upgrade coté disque pourrait aider, passer sur une solution ssd ou sur un disque plus rapide pour traiter l'écriture un peut plus rapidement. Coté RAID, perso je pense que du RAID10 est le plus opti pour du SQL, (raid5 trop lent). Sinon, parfois il vaut mieux avoir plusieurs petits serveurs en cluster qu'un seul gros pour tout gérer proprement :)


-------------

ni-host.com






Aussi étiqueté avec au moins un de ces mots-clés : mysql

1 utilisateur(s) li(sen)t ce sujet

0 membre(s), 1 invité(s), 0 utilisateur(s) anonyme(s)