Forum général.général Gérer *beaucoup* (vraiment !) de bases MySQL

Posté par .
Tags : aucun
3
28
juin
2010
Bonsoir.

Je dois réfléchir à la problématique suivante : le LMS Dokeos (forké en Chamilo) est un outil intéressant, mais son mode de fonctionnement pose question : il dispose d'un mode mono-base et d'un mode multi-bases.

Dans le premier, chaque fois qu'un "cours" est créé, environ 80 tables supplémentaires le sont avec. Dans le second, une nouvelle base est créé pour le "cours", avec toujours à peu près 80 tables dedans, naturellement. D'expérience, en mono-base avec un "cours" par triplet prof-matière-classes, le serveur MySQL s'effondre quasiment avec un seul établissement (de bonne taille, toutefois). Aucun soucis en multi-bases (et j'ai lu de-ci de-là que MySQL n'aimait pas trop qu'on lui "fasse manger" plein de tables...)

Tout va bien quand on a quelques instances de ce logiciel, mais quid de la mis à l'échelle ? Imaginons que tous les établissements scolaires d'une académie s'en servent, ça commence à faire ! Avec seulement un "cours" par prof de l'académie, on se retrouve avec plusieurs dizaines de milliers de bases. Peut-on raisonnablement envisager de gérer cette situation, sans devoir multiplier les serveurs à outrance ? Je ne parle pas là de l'aspect HA...

D'un autre côté, je ne peux m'empêcher de me dire que les autres LMS (comme Moodle, Sakaï...) qui fonctionnent en mono-base avec un nombre fixe de tables, vont en contrepartie avaler des quantités astronomiques de données ! OK, une SGBD, c'est un peu fait pour ça ;), mais comment se comportent-ils avec des centaines de millions d'entrées dans une seule table ? Si quelqu'un a eu vent d'études sur la complexité des opérations liées aux recherches / insertion dans un SGBD, je suis preneur (idem pour le parfait manuel des bonnes pratiques dans l'écriture des requêtes). Et peut-être que c'est aussi là que d'autres SGBD que MySQL deviennent intéressants...

Merci de m'avoir lu ! Et en espérant que quelqu'un ait, sinon des réponses, du moins des pistes, car je suis bien embêté...
  • # fonctionnalité native versus outillage à touver ou créer

    Posté par (page perso) . Évalué à 3.

    Sur la gestion de multiples bases, je n'ai pas de compétences particulière.
    Par contre, la recherche dans des tables volumineuses est la fonction de base des SGBD. Que ce soit par des arbres n-aire ou des clefs de hachage, c'est très rapide.
    D'expérience, il est toujours plus facile de gérer quelques objets éventuellement complexe qu'une multitude d'objets même simple.

    La solution multi base va impérativement nécessiter un outillage pour réaliser les opérations de maintenance (sauvegarde, réorganisation...) sur l'ensemble des bases.
    Ça peut être un simple outillage à base de script ou quelque chose de plus chiadé mais ce sera nécessaire.
    Dokéos n'inclut pas le minimum pour automatiser les actions sur les multiples bases ?
  • # Logiciel à améliorer

    Posté par (page perso) . Évalué à 4.

    Une solution simple et qui fonctionne, c'est de rajouter un attribut «id_cours» ou quelque chose dans le genre à chacune des 80 tables, et de le rajouter dans la liste des attributs de la clef primaire.

    Ça veut aussi dire qu'il faut modifier les clefs étrangères… C'est du boulot, mais c'est étrange que le logiciel utilise autant de tables. Le travaille d'un SGBD, c'est de gérer des données, même quand il y en a des millions.

    Le fait de rajouter l'attribut dans la clef primaire va faire organiser la base de données en fonction de l'attribut par le SGBD. Il va aussi construire des index.

    Si cela ne suffit pas, même si ça parait étrange (c'est juste des cours, il y en a pas des dizaines de millions), tu peut regarder du cotés du partitionnement : http://fr.wikipedia.org/wiki/Partition_(base_de_données)

    Les données vont êtres organisées sur les disques en fonction de critères que tu définis, pour que ça aille encore plus vite (par exemple, chaque établissement a son disque dur).

    Envoyé depuis mon lapin.

  • # tables fixes

    Posté par . Évalué à 3.

    Si jamais tu veux pouvoir faire des requêtes sur toutes les données, (pour des stats, transferts etc) il vaut beaucoup mieux (pour toi autant que pour le sgbd) ne pas s'éparpiller sur plusieurs tables et encore moins sur plusieurs bases.
    L'intérêt de dispatcher sur plusieurs bases c'est inversement quand les données ne doivent surtout pas se rejoindre (sécurité, versions différentes etc.).

    Au niveau volume des données ça dépend surtout du logiciel, si les requêtes sont optimisées etc.
  • # Merci !

    Posté par . Évalué à 1.

    Merci pour vos réponses. Je ne serai pas en mesure de faire quelque modification que ce soit au code du logiciel. Je souhaite juste pouvoir estimer les implications matérielles de la gestion d'environ 20 000 bases de données MySQL. Faut quoi comme babasse, beaucoup de RAM c'est sûr, mais beaucoup c'est vague. J'ai actuellement une machine qui sert plus de 3 000 bases, plus de 200 000 tables, avec plus de 10 instances de MySQL (et autant d'instances Apache2 avec mod_php). C'est un Xeon bi-cœur à 2 GHz avec 4 Go de RAM. La machine est raisonnablement chargée, sans plus...

    En ce qui concerne la problématique des performances, je me dis (mais j'ai peut-être tort) que la rapidité (pour les requêtes de sélection mais aussi d'insertion) dépend certainement un peu du nombre d'enregistrements stockés, non ? En gros, la complexité des opérations est souvent en O(const), O(n), O(n*log(n)). Dans le cas d'une requête du genre "SELECT `bidule` FROM `chose` WHERE `machin`="truc", on est en O de quoi ? Et quand il y a des jointures, des relations n:m ? Si vous avez des infos...

    Enfin, je vois au moins un intérêt au fait d'avoir une base par cours : une restauration partielle n'impacte qu'un nombre "minimum" de personnes et prend moins de temps que s'il faut tout régurgiter... Quant aux sauvegardes, on idem, elles impactent peu de personnes simultanément.

    nat
    • [^] # Re: Merci !

      Posté par . Évalué à 5.

      ans le cas d'une requête du genre "SELECT `bidule` FROM `chose` WHERE `machin`="truc", on est en O de quoi ? Et quand il y a des jointures, des relations n:m ? Si vous avez des infos...

      Pour le select simple:
      - O(n) s'il n'y a pas d'index sur le champ 'machin'
      - O(log(n)) si 'machin' est indexé (index de type B-TREE, les seuls utilisés par MySQL sur les tables de type MyISAM)
      -O(const) si 'machin' a un index de type HASH

      Pour les jointures entre deux tables:
      - O(n) * O(m) sans index
      - O(log(n) * O(log(m)) avec les index qui vont bien
      En pratique, faire des jointures sans index relève de l'opération suicide dès que les tables ont plus de quelques enregistrements.

      PS: Je ne connais pas Dokeos mais je ne peux pas m'empêcher de penser qu'une application qui a besoin de 200,000 tables et qui nécessite d'en ajouter pour intégrer de nouveaux cours a sérieusement «foiré» la définition de son modèle de données.
      • [^] # Re: Merci !

        Posté par . Évalué à 1.

        >> -O(const) si 'machin' a un index de type HASH

        Question de noob, mais bon, hein... Comment c'est possible ? Un index est chargé intégralement en RAM ?
        • [^] # Re: Merci !

          Posté par . Évalué à 2.

          Le hash est une fonction qui, à partir de la valeur de la clé, donne directement l'adresse de l'enregistrement dans la base de donnée, d'où le O(const) (je simplifie un peu, il faut gérer les collisions: plusieurs valeurs peuvent avoir la même hash).
          Il n'y a donc rien à charger en RAM.

          Wikipedia le décrit beaucoup mieux que je ne pourrais le faire : https://secure.wikimedia.org/wikipedia/en/wiki/Hash_table
          • [^] # Re: Merci !

            Posté par . Évalué à 1.

            Je savais ce qu'est un hash (je m'en sers régulièrement pour faire mon bois :-° !)

            Merci pour le lien, j'en déduis donc qu'effectivement, ce type d'indexation est très performant, et voit ses performances se dégrader très peu avec l'augmentation du volume de données stockées.
      • [^] # Re: Merci !

        Posté par (page perso) . Évalué à 1.

        Le modèle de donnée est effectivement foireux.

        Les concepteurs on pris le parti de partitionner les données en créant des tables ou des bases au choix.
        L'inconvénient est clair pour tout traitement de consolidation, mais aussi dans l'impossible passage à l'échelle tant pour des raisons de ressources que pour des raisons d'administration.

        Techniquement, il est plus coûteux d'effectuer dans la même seconde 10 (ou 100 ou 1000) recherches sur 10 (100, 1000) tables différentes que les mêmes recherches sur la même table. En effet dans un cas, on accèdera à des indexs différents (et des tables différentes) donc pas d'utilisation des différents niveaux de cache (de celui du processeur à celui du disque en passant par ceux du système) alors que des recherches sur les mêmes tables via les mêmes indexs utiliseront ces caches.

        je ne me souviens pas si Mysql sait partitionner les tables ; c'est peut être la raison qui a amené à ce choix.
        Mais c'est clairement une mauvaise solution. Si encore le niveau de "partitionnement" retenu avait été l'établissement ou à défaut le prof. mais la ! (pourquoi pas par élève ? :))


        Au final s'il ne peut être question de corriger l'application, la meilleure solution est probablement celle de la multiplication des bases, car elle permet de plus facilement gérer des sauvegardes, restauration, administration en général pour chaque cours.
        Mais il faut prévoir l'outillage d'automatisation pour ne voire qu"une base en administration courante, sinon c'est l'enfer.

        Ensuite, attention sur toutes les solutions de scalabilité à base de multiplication d'instance Mysql (ou autre) ; c'est vite lourd à gérer.

        Sinon, pour le dimensionnement, je pense que la meilleur solution c'est de monter un prototype et de tester les deux solutions aux limites d'exploitation prévue.

        Au passage y a t'il un utilitaire pour basculer d'un modèle à l'autre en cas de changement de choix en cours de route ? Parce que sinon, il y a de gros risque d'avoir à se l'écrire après s'être fourvoyé dans la mauvaise direction.
        • [^] # Re: Merci !

          Posté par (page perso) . Évalué à 2.

          juste une question idiote, si le modèle est foireux, pourquoi le prendre, ca posera un soucis à un moment donné, en le sachant pourquoi continuer dans cette voie ?
          • [^] # Re: Merci !

            Posté par . Évalué à 1.

            Pour une raison simple : l'application est la plus adaptée à nos besoins !
            • [^] # Re: Merci !

              Posté par . Évalué à 1.

              Pour l'utilisateur peut-être, mais pour la mise en place et la maintenance on dirait pas...
              • [^] # Re: Merci !

                Posté par . Évalué à 1.

                Certes. Mais c'est bien pour cela qu'il y a des services informatiques compétents, non ;) ?

                Il est des domaines où les vrais informaticiens (je n'en fait pas partie) sont décideurs, et d'autres où ils ne peuvent ni ne doivent l'être. Je suis exactement dans cette situation...

                Donc les commentaires du type "mauvais bidule, changer de bidule" ne m'aident pas vraiment, car je cherche justement à faire marcher le bidule, avec tous les défauts qu'il a ! Merci, néanmoins, de participer à la discussion.
                • [^] # Re: Merci !

                  Posté par . Évalué à 1.

                  Ok, je n'avais pas compris si tu voulais un avis sur le bidule ou simplement un conseil pour faire avec.
                  Donc, vu la situation, tu as peut-être effectivement intérêt à choisir l'option multi-base, ça sera effectivement plus souple. S'il y a des problèmes de charges ce sera assez facile d'en déplacer une partie sur une autre machine par ex, de savoir laquelle charge etc. Ca ne sera pas forcément le plus performant mais ça sera le plus simple. Les hébergeurs ont l'habitude de gérer des quantités importantes de bases, ça doit donc se faire.
                  • [^] # Re: Merci !

                    Posté par . Évalué à 1.

                    OK, ça rejoint les conclusions auxquelles j'étais parvenu. Merci. Une idée de comment font les hébergeurs, justement ? J'ai lu des choses sur le sharding, mais je ne suis pas encore sûr d'avoir tout bien compris...
                    • [^] # Re: Merci !

                      Posté par . Évalué à 1.

                      Le sharding, en quelque sorte c'est ce que fait dokeos ! répartir les données.
                      Je ne sais pas comment font les hébergeurs mais j'imagine qu'ils ajoutent tout simplement des machines.
        • [^] # Re: Merci !

          Posté par . Évalué à 1.

          > Techniquement, il est plus coûteux d'effectuer dans la même seconde 10
          > (ou 100 ou 1000) recherches sur 10 (100, 1000) tables différentes que les
          > mêmes recherches sur la même table. En effet dans un cas, on accèdera à
          > des indexs différents (et des tables différentes) donc pas d'utilisation des
          > différents niveaux de cache (de celui du processeur à celui du disque en
          > passant par ceux du système) alors que des recherches sur les mêmes
          > tables via les mêmes indexs utiliseront ces caches.

          Et c'est reparti avec une quesion de noob : si la grosse table a la même taille que la somme des petites, l'index de la grosse aura une taille proche de la somme des tailles des index des petites, non ? Et une fois chargés en cache, ça devrait revenir à peu près au même, non ?

          > Au final s'il ne peut être question de corriger l'application, la meilleure solution
          > est probablement celle de la multiplication des bases, car elle permet de plus
          > facilement gérer des sauvegardes, restauration, administration en général pour
          > chaque cours.

          Ça me semble être un avantage, en effet, tout en minimisant en outre le temps pour réaliser les sauvegardes. Pas du point de vue de l'admin, bien sûr, mais du point de vue usager (durée de blocage).

          > Sinon, pour le dimensionnement, je pense que la meilleur solution c'est de monter
          > un prototype et de tester les deux solutions aux limites d'exploitation prévue.

          Encore un domaine que je ne connais pas du tout. Y'aurait-il un moyen simple de faire jouer en parallèle des milliers d'instances de Selenium [http://seleniumhq.org/] (ou tout autre truc dans le genre) ?

          Encore merci de vous pencher sur ma misère...
        • [^] # Re: Merci !

          Posté par . Évalué à 1.

          > Techniquement, il est plus coûteux d'effectuer dans la même seconde 10
          > (ou 100 ou 1000) recherches sur 10 (100, 1000) tables différentes que les
          > mêmes recherches sur la même table. En effet dans un cas, on accèdera à
          > des indexs différents (et des tables différentes) donc pas d'utilisation des
          > différents niveaux de cache (de celui du processeur à celui du disque en
          > passant par ceux du système) alors que des recherches sur les mêmes
          > tables via les mêmes indexs utiliseront ces caches.

          Et c'est reparti avec une quesion de noob : si la grosse table a la même taille que la somme des petites, l'index de la grosse aura une taille proche de la somme des tailles des index des petites, non ? Et une fois chargés en cache, ça devrait revenir à peu près au même, non ?

          > Au final s'il ne peut être question de corriger l'application, la meilleure solution
          > est probablement celle de la multiplication des bases, car elle permet de plus
          > facilement gérer des sauvegardes, restauration, administration en général pour
          > chaque cours.

          Ça me semble être un avantage, en effet, tout en minimisant en outre le temps pour réaliser les sauvegardes. Pas du point de vue de l'admin, bien sûr, mais du point de vue usager (durée de blocage).

          > Sinon, pour le dimensionnement, je pense que la meilleur solution c'est de monter
          > un prototype et de tester les deux solutions aux limites d'exploitation prévue.

          Encore un domaine que je ne connais pas du tout. Y'aurait-il un moyen simple de faire jouer en parallèle des milliers d'instances de Selenium [http://seleniumhq.org/] (ou tout autre truc dans le genre) ?

          Encore merci de vous pencher sur ma misère...
          • [^] # Re: Merci !

            Posté par . Évalué à 2.

            Désolé, mon FF a planté, et j'ai fait restaurer les onglets, du coup double-post...
          • [^] # Re: Merci !

            Posté par . Évalué à 1.

            Imagine toi une maison avec chacun sa chambre ou tous dans le même dortoir... Plus tu disperse les données plus la somme prendra de place et plus le sgbd va mouliner pour trouver ses petits.

            Pour les sauvegardes il y a des solutions pour ne pas bloquer l'usager.

            Pour les tests ce qu'il faudrait d'abord c'est isoler les points faibles. Le problème vient rarement des requêtes simples, même en très grande quantité, mais plutôt de passages critiques qui font tomber tout le monde (par ex des stats).
            • [^] # Re: Merci !

              Posté par . Évalué à 1.

              Sauvegardes : tu penses à des techniques de snapshot ZFS ou LVM (après avoir locké ce qu'il faut) ? Je connais la théorie, mais je ne l'ai jamais mise en pratique. Je me suis toujours demandé ce qu'il se passe lorsqu'une requête d'insertion / modification est en cours d'exécution, qu'elle affecte plusieurs tables, et que le lock survient au beau milieu de ladite requête. Est-ce que le lock attendra avant d'être effectif que la totalité des modifications soient effectuées, ou est-ce qu'il prend le pas sur tout ce qui se passe ? Dans le premier cas, je me demande s'il peut arriver qu'on ne puisse pas locker les tables car la base est sans cesse sollicitée, dans le second, je me demande ce qu'on récupère lors d'une restauration (il manquera certainement des données !)

              Pour les tests de charge, je manque totalement de culture et je me dis que ce doit être un vrai métier. Y'a-t-il des moyens relativement simples qui permettent de charger une appli, par exemple en faisant répéter un "scénario" ? J'indiquais plus haut que j'avais entendu parler de Sélénium (un ami s'en sert pour faire des qualif), mais il me semble qu'on ne peut pas lui faire jouer simultanéménet 10 000 fois le même scénario...
              • [^] # Re: Merci !

                Posté par . Évalué à 1.

                Pour les sauvegardes il y a plusieurs solutions. Je ne connais pas MySQL, mais normalement on ne sauvegarde pas au niveau du système de fichier mais au niveau du sgbd, c'est lui qui fait un snapshot cohérent. Encore une fois, ça dépend aussi de l'application, si elle gère bien les transactions. Si les bases sont grosses et qu'un snapshot trop fréquent est trop lourd on fait des réplications en continue.

                Les tests de charge (genre ab) sur 10000 accès simultanés c'est intéressant pour les sites à fort traffic, mais dans le cas d'une application je doute de l'utilité. Mais bref, personnellement si je veux faire des tests d'appli web, je le script en python...
                • [^] # Re: Merci !

                  Posté par . Évalué à 1.

                  Pour les sauvegardes, si on dispose de serveurs redondants, ou d'un serveur pour les écritures et d'autres pour les lectures, avec synchronisation de l'ensemble, je sais qu'on peut se servir d'un esclaver pour faire les sauvegardes...

                  Quant aux tests, oui, je me demandais si je n'allais pas utiliser Python pour automatiser du Twill, par exemple, mais je n'imagine pas du tout ce dans quoi je m'embarquerais ! Par exemple, je n'ai jamais touché aux threads... Et puis je vois un autre problème : j'ai récemment "joué" avec des outils tels que slowloris ou hping (pour la bonne cause, hein !), et je n'ai pas réussi à dépasser 1000 connexions simultanées. Y'a-t-il une limite quelque part ? Un paramètre à ajuster dans /proc ?

                  Merci encore.

Suivre le flux des commentaires

Note : les commentaires appartiennent à ceux qui les ont postés. Nous n'en sommes pas responsables.