Compte rendu de la conférence MySQL du 19 Novembre 2008
Date de publication : 09/12/2008 , Date de mise à jour : 28/01/2009
Par
Tristan Bartement (autres articles)
Cet article est une version rédigée de mes notes de la conférence MySQL du 19 Novembre 2008.
Introduction
1. Les sponsors et les stands
1.1. Le MUG
1.2. Infobright
1.3. Anaska
1.4. Continuent
1.5. Dolphin
1.6. JasperSoft
1.7. Sun
2. La situation de MySQL
3. La roadmap MySQL
3.1. MySQL 5.1
3.2. MySQL 6.0
3.3. MySQL 6.x
3.4. « Citadel »
4. Tenir la charge du Web 2.0 avec memcached et MySQL
4.1. Le monde du Web 2.0
4.2. MySQL pour le Web 2.0
4.3. Memcached
4.3.1. La nécessité d'un cache
4.3.2. Présentation
4.3.3. Conseils
5. Les moteurs MySQL
5.1. Falcon
5.2. Maria
5.3. Infobright
5.4. Kickfire
6. MySQL Proxy
7. Les performances MySQL sous microscope
7.1. Tracer les requêtes
7.2. L'indexation
7.3. Les requêtes
7.4. Le query cache
7.5. Le key cache de MyIsam
7.6. Le buffer pool d'InnoDb
8. Scale up, scale out, virtualisation. Que faire avec MySQL ?
9. Retour de client : Virgin Mobile
Sigles et définitions
Remerciements
Introduction
La conférence MySQL du 19 Novembre 2008 s'est déroulée à Paris, à l'hôtel Méridien Étoile près du Palais des Congrès. Elle a consisté en une douzaine de présentations. Cet article contient ce que j'en ai retenu : Il n'a pas vocation à être une retranscription exhaustive de la journée. Certaines présentations s'étant déroulées en parallèle, toutes ne sont pas mentionnées.
1. Les sponsors et les stands
1.1. Le MUG
Ce n'est pas une forme de tasse particulière mais le MySQL User Group. En plus de luter contre le manque, révoltant, de peluches de dauphin (nous avons tous nos priorités), le MUG promeut MySQL auprès des professionnels et organise divers évènements. Il avait en particulier organisé la veille, pour le tout venant, une rencontre avec Giuseppe Maxia et Nat Makarévitch.
1.2. Infobright
1.3. Anaska
Anaska dispense de nombreuses formations pour diverses technologies open source. Elles couvrent aussi bien PHP, le monde Java, PostgreSQL, MySQL évidemment, qu'OpenOffice, etc.
1.4. Continuent
Continuent fournit des solutions de haute disponibilité pour bases de données, et propose notamment de la réplication entre MySQL et Oracle dans un sens ou dans l'autre.
1.5. Dolphin
Dolphin propose des solutions d'interconnexion et mettait en particulier en avant Dolphin Express. Il s'agit d'une solution réseau haut débit et basse latence. Elle se compose d'une partie matérielle (équipement réseau), ainsi que d'une partie logicielle, qui se greffe dans MySQL lui-même, permettant de court-circuiter un maximum de couches, à commencer par la pile TCP/IP. Les gains sont particulièrement importants pour l'interconnexion de nœuds MySQL Cluster.
1.6. JasperSoft
JasperSoft développe toute une suite d'applications de
business intelligence open source. Elle couvre le reporting comme l'analyse, et un partenariat avec Talend apporte l'
ETL.
1.7. Sun
Sans surprise Sun disposait d'un stand, et proposait ses serveurs et son expertise dans les infrastructures Web.
2. La situation de MySQL
La journée s'est évidemment ouverte par un mot sur le rachat de MySQL AB par Sun au début de l'année. Avec le passage de Solaris puis de sa JVM en open source, la stratégie de Sun est maintenant tournée vers le modèle open source. Le rachat de MySQL AB est dans la continuité de cette évolution et permet à Sun de convaincre du sérieux de son engagement.
Cette opération apporte à Sun une base de données, un positionnement dans le monde du Web, et les clients qui vont avec. Sun, de son côté, apporte sa crédibilité, des ressources, et de l'expertise. Bien que le support de Solaris soit d'actualité, Linux et Windows seront évidemment toujours supportés.
Une étude Gartner place MySQL au troisième rang des
BDD, derrière Oracle et MS SQL Server. Ses clients sont essentiellement répartis sur 5 marchés :
- Le Web 2.0 ;
- L'embarqué ;
- Le SaaS ;
- Les télécom (en bonne partie via MySQL Cluster) ;
- L'entreprise 2.0 (les applications d'entreprise se basant sur les technologies du Web 2.0 comme Ajax).
MySQL est particulièrement bien implanté dans l'économie Web en rapide croissance, ne serait-ce que par Google qui en fait une utilisation industrielle. Son faible coût (qui commence à gratuit) et sa facilité de déploiement le rendent tout à fait adapté aux cloud computing et au principe du « Fail fast, scale fast » : Il donne toute latitude pour expérimenter à moindre frais ; Sur le nombre certains essais auront bien un succès fulgurant (sur le Web 2.0, la montée en charge qui accompagne un succès rapide peut être brutale).
C'est dans cette optique que la formule de MySQL Enterprise mise en avant est (évidemment) la version illimitée, à partir de 32k€ / an. Elle autorise un nombre illimité d'installations, sans limitation sur le nombre de processeurs ou autre, avec le support qui va avec, ce qui débarrasse d'une gestion contraignante des licences (quel serveur a droit à quel niveau de support etc.). Et, accessoirement, c'est moins cher qu'une licence Oracle Enterprise pour un CPU. Une version avec un support pour le serveur Glassfish est aussi disponible.
A cette occasion il a été conseillé aux utilisateurs qui paient pour la version entreprise d'utiliser MySQL Monitor (certains ne le déployant pas par manque de temps) auquel elle donne accès. Il facilite grandement le suivi d'un parc de serveurs, de la bonne marche de la réplication entre serveurs, etc. Qu'on souhaite faire l'économie de la version entreprise, c'est une chose, mais quand on y a accès pourquoi se priver ?
Plutôt que de migrer depuis un autre système, Sun recommande MySQL pour les nouvelles applications, ou éventuellement en cas de grosse reconception. Les scénarios visés sont particulièrement :
- Toute application Web ;
- Les applications OLTP petites et moyennes ;
- En Business Intelligenre / Datawarehousing ;
- Les applications distribuées.
3. La roadmap MySQL
3.1. MySQL 5.1
La version 5.1 était lors de la conférence proche de passer en GA (Generally Available), c'est depuis chose faite. Parmi ses apports notables :
- Les journaux (query log et slow query log) peuvent être activés à chaud. En outre ils peuvent aussi être stockés dans des tables ;
- Le partitionnement des tables ;
- La réplication par enregistrements (row-based, en transmettant juste les enregistrements ayant changé), par opposition à celle qui rejoue les requêtes de modification (statement based). Il y a aussi un mode hybride passant d'un système à l'autre, suivant que les requêtes sont coûteuses en exécution, ou au contraire touchent beaucoup d'enregistrements ;
- Index fulltext plus performants (jusqu'à +500 % annoncés) ;
- Le Job Scheduler pour planifier en interne des tâches sans dépendre de cron.
Il a été remarqué que les partitions pouvaient servir à autre chose qu'à améliorer les performances. Elles aident aussi à l'administration de gros volumes de données. Purger les données d'il y a quatre mois est incomparablement plus rapide si elles correspondent à une partition.
On devrait voir arriver des patchs (en 2009 ?) améliorant les performances dans les situations de forte concurrence (InnoDB sur systèmes avec de nombreux processeurs / cœurs).
3.2. MySQL 6.0
La prochaine version sera la 6.0 et inclura :
- Le moteur Falcon ;
- Le moteur Maria (non transactionnel) ;
- Des fonctions de sauvegarde et de restauration intégrées au serveur ;
- Une meilleure optimisation du plan d'exécution des requêtes, tout particulièrement pour les sous-requêtes ;
- Plus d'information disponible pour le diagnostic des problèmes de performances ;
- La réplication semi-synchrone (un commit ne rend la main que quand le binary log a été transmis à l'esclave, ce qui garantit de ne pas perdre la transaction en cas de perte du maître).
En ce qui concerne les fonctionnalités de sauvegarde, elles font suite aux récriminations des utilisateurs qui s'exclamaient, je cite l'orateur, « Your backup stinks! » (« Vos sauvegardes puent » pour les francophones). Cette nouvelle fonctionnalité devrait y remédier en permettant des sauvegardes à chaud non bloquantes pour les moteurs transactionnels
et MyIsam ! Cela est permis par l'utilisation d'un driver natif pour ce dernier moteur, ce qui permet des sauvegardes compactes et rapides. D'autres drivers natifs devraient suivre dans le futur. La restauration à un instant donné (
point-in-time recovery) devrait être possible. Pour revenir sur la vitesse du procédé, mysqldump est laissé loin (
vraiment loin) derrière, et ce quel que soit le moteur. Pour plus de détails voir ce
tutoriel [1].
Les alphas sont déjà disponibles.
3.3. MySQL 6.x
Diverses améliorations sont déjà planifiées pour la série 6.x :
- La gestion des clefs étrangères pour tous les moteurs ;
- La possibilité d'utiliser des Prepared Statement pour toute commande SQL ;
- Des progrès sur les plans d'exécution ;
- Amélioration des curseurs côté serveur (plus de performances, moins de mémoire) ;
- Amélioration de la sauvegarde à chaud ;
- Amélioration de la réplication (checksums) ;
- Meilleurs diagnostic de performance ;
- Changements pour les procédures stockées (Signal/Resignal, ...).
La réplication multi-sources est à l'étude pour la version 6.2.
3.4. « Citadel »
Tous les développements ultérieurs à la série des 6.x sont regroupés sous le nom de code « Citadel ». S'y trouvent pêle-mêle :
- Une gestion des privilèges par groupes / rôles ;
- Une authentification externe (au niveau de l'OS) ;
- Des ALTER TABLE non bloquants ;
- Le chiffrage transparent de tables ou de colonnes ;
- Plus d'optimisations des sous-requêtes.
4. Tenir la charge du Web 2.0 avec memcached et MySQL
4.1. Le monde du Web 2.0
Le Web est le cœur de métier de MySQL. C'est un domaine particulier où les applications sont perpétuellement en bêta et évoluent vite. De même le succès peut être fulgurant et, là où une application d'entreprise compte ses utilisateurs en milliers (et encore), on en vient vite à servir des millions d'internautes.
Ces applications ont besoin de fiabilité, de performances, et aussi de simplicité. Fiabilité, car les utilisateurs ne pardonnent pas l'indisponibilité. Un uptime respectable de 99,99 % signifie 5 à 10 minutes d'arrêt dans l'année. Mais si GMail, par exemple, s'interrompt 5 minutes, l'événement sera repris sur tous les sites de news. Cela pousse à utiliser des technologies comme la réplication, MySQL Cluster et DRDB.
Les performances sont tout aussi critiques, car, si le site s'écroule sous son succès et devient indisponible ou inutilisable (ou seulement pénible à utiliser), cela ne lui sera jamais pardonné. Parmi les éléments à considérer se trouvent le query cache, le choix des moteurs de MySQL, le partitionnement, MySQL Cluster, memcached, etc.
4.2. MySQL pour le Web 2.0
MySQL a l'avantage d'être simple d'utilisation. Il n'a (ou peut-être devrait on dire n'avait) pas trop de fonctionnalités superflues. Il est aisé à installer, à démarrer, et nécessite peu de configuration. S'y connecter est simple, que ce soit en Java, PHP, Perl, etc. La réplication MySQL est assez facile à mettre en place, et, comme le serveur est open source, on peut en déployer autant que nécessaire, ce qui est pratique quand on souhaite mettre en place du
sharding.
Notes diverses sur les capacités et emplois des moteurs :
- MyIsam se prête bien aux tables en lecture intensive. Il dispose d'index fulltext et spatiaux (fonctionnalités GIS, aussi disponibles pour d'autres moteurs à partir de la version 5.0.16) ;
- InnoDB et Falcon s'imposent dès que l'on a besoin d'un moteur ACID pour une application OLTP ;
- Memory est performant, mais non durable, et peut servir à la gestion des sessions ;
- Archive pour l'audit et les journaux ;
- NDB (MySQL Cluster) pour la haute disponibilité et/ou hautes performances (sessions, authentification).
La réplication MySQL consiste à transmettre toutes les requêtes de modification (depuis la 5.1 il est possible de transmettre directement les modifications apportées aux enregistrements) effectuées sur le serveur maître à ses serveurs esclaves qui vont les rejouer. Il est ensuite possible de répartir les requêtes d'interrogation sur les esclaves, en faisant attention tout de même au fait que la réplication n'est pas synchrone. Les esclaves peuvent donc avoir un peu de retard sur le maître. En particulier, comme les requêtes de modification sont rejouées de façon sérialisée, si l'une d'elles est longue à s'exécuter l'esclave prendra autant de retard. Pour une application très intensive en lecture il est possible de mettre en place plusieurs niveaux de réplication (les esclaves peuvent eux mêmes avoir des esclaves). Par contre la charge d'écriture est dupliquée sur tous les serveurs. S'ils sont identiques et que le maître dépense 50 % de ses ressources en écriture, les esclaves ne seront disponibles qu'à 50 % en lecture. Si on passe à 75 % d'écritures un esclave n'apporte plus que 25 % de ses ressources.
4.3. Memcached
4.3.1. La nécessité d'un cache
Pour alléger la charge de la
BDD il est indispensable de disposer de caches. Une application Web en a plusieurs couches, qui filtrent à chaque fois une partie de la charge. La première se trouve sur les machines clientes elles-mêmes puisque c'est le cache des navigateurs. Les serveurs Web peuvent aussi conserver des pages en cache pour ne pas les reconstruire à chaque demande d'un utilisateur. Peuvent ensuite se trouver des serveurs memcached pour décharger la
BDD, qui a elle même son propre système de cache lui permettant de ne pas avoir à interroger ses disques. En général, ces couches impliquent des ressources de plus en plus rares. Il est assez facile de multiplier les serveurs Web s'appuyant tous sur une même base de données. Répartir la charge sur plusieurs
BDD (ce qu'est basiquement le
sharding) l'est beaucoup moins. La
BDD tend à être unique.
Typiquement, on trouve sur la page d'accueil d'un site les 5 meilleurs ventes, fils de discussion les plus actifs, articles préférés... Multiplié par les milliers de visiteurs arrivant sur la page d'accueil, on obtient une charge qu'aucune
BDD ni aucun matériel ne peut assurer seul. D'où le besoin de stocker ce résultat dans un cache, comme memcached.
4.3.2. Présentation
Memcached a été originellement créé pour LiveJounal. Maintenant il est utilisé par tous les grands du Web : LiveJounal (forcément), Fotolog, Facebook, Amazon, Yahoo, Youtube, Wikipedia,
etc. Son fonctionnent est simple : C'est une table de hachage (potentiellement) répartie sur plusieurs machines. La taille maximale d'une instance dépend de si l'on est en 32 ou 64 bits. Dans tous les cas, plus on a de machines plus on a d'espace de stockage. Les clefs peuvent atteindre 250 bytes, et les valeurs 1Mo. Les entrées sont gérées en
LRU. Pour les grosses instances sa gestion des threads est efficace (il est multithread depuis la version 1.2).
Il faut noter que memcached n'est pas :
- Une base de données rapide. Les entrées qui ne tiennent pas en mémoire sont éliminées en LRU ;
- Sécurisé. Si on lui demande des données il les donne ;
- Fiable. Les données ne sont pas dupliquées sur plusieurs serveurs ni écrites sur le disque. En cas de panne...
- ACID (ni atomique, ni consistant, ni isolé, ni durable).
Son API est simple, limitée, et efficace : On dispose de « set », « get », « replace », « append » et « prepend » (pour compléter des valeurs), et de quelques fonctions un peu plus évoluées comme « cas » (Compare And Swap). Il existe une UDF (User Defined Function) MySQL pour y accéder, mais cela va plutôt à l'encontre du but recherché ; C'est normalement à l'application de l'appeler. Il peut tenir de très fortes charges : LiveJounal sert 20M de pages dynamiques par jour. Facebook a 805 instances memcached.
4.3.3. Conseils
Si des entrées doivent être durables, elles peuvent être dans memcached et MySQL. Les lectures en base sont usuellement en « pass through » : On cherche en cache, si on ne trouve pas on interroge la
BDD, puis on alimente le cache. On peut aussi mettre en cache des entités plus complexes que les résultats des requêtes, comme des objets ou des morceaux complets de pages Web.
Si des données sont critiques, on peut les placer dans plusieurs memcached différents. Si elles sont sensibles, on peut aussi chiffrer les valeurs. Dans tout les cas, c'est au client de le faire.
Les clefs sont à choisir avec attention. Elles ont souvent la forme « type + identifiant », par exemple « USER:210 ». Si elles doivent avoir une durée de vie limitée, elles peuvent inclure une date : « ITEM:210:20081121 » ne sera placée et retrouvée que le 21 novembre. Le lendemain, la clef utilisée sera « ITEM:210:20081122 », et l'ancienne finira par être purgée du serveur. On peut aussi y inclure un numéro de version.
Memcached a été comparé au query cache : Les requêtes sont sorties du query cache quand une des tables sur lesquelles elles portent est modifiée. C'est bien, car on voit ainsi des données à jour. Mais cela implique qu'une requête qui touche une table souvent modifiée ne va pas en bénéficier. De plus, le query cache est limité à une seule machine.
Memcached a été comparé à MySQL Cluster : Les deux se ressemblent beaucoup. Memcached reste bien plus simple, en bien comme en moins bien.
Dans le cas des applications intensives en écritures les choses deviennent plus délicates. On peut réaliser un partitionnement fonctionnel : On utilise une
BDD pour chaque domaine que l'on arrive à isoler, comme l'authentification, les journaux, et les données applicatives. On peut aussi séparer les données en données faiblement et fortement persistantes : La persistance du contenu du caddie d'un client d'un site de commerce est ainsi moins critique que celle d'une commande achevée et payée. On peut aussi recourir au
partitionnement horizontal, au
partitionnement vertical, et, si nécessaire, au
sharding.
Sun fournit du support pour memcached.
5. Les moteurs MySQL
Cette section ne couvre pas une des sessions de la conférence en particulier, mais rassemble ce qui a été dit dans la journée sur différents moteurs à venir ou d'éditeurs tiers.
5.1. Falcon
Il s'agit d'un nouveau moteur transactionnel
ACID. Bien qu'il ne soit pas officiellement un remplaçant d'InnoDB, il s'attaque à la même niche et il est difficile de ne pas y voir une réponse de MySQL au rachat d'InnoDB par Oracle.
Son arrivée est la fonctionnalité vedette de MySQL 6.0. Dans son état d'avancement actuel il ne rivalise pas encore en termes de performances avec InnoDB, mais étant récent il a été conçu avec l'objectif de tourner sur le matériel actuel (et d'éviter les goulots d'étranglement d'InnoDB, qu'il est supposé laisser à terme dans la poussière en ce qui concerne la montée en charge). Ses performances futures seront donc à suivre de près.
5.2. Maria
Ce moteur doit aussi arriver dans la version 6.0, mais est curieusement bien moins mis en avant (il a surtout été détaillé en fin de journée lors des questions-réponses). À terme (au delà de la 6.0), il disposera d'un mode non transactionnel et d'un mode transactionnel.
Pour la 6.0 il se définit comme un MyIsam résistant aux pannes : Mêmes fonctionnalités (y compris index fulltext), performances équivalentes, mais apte à résister aux coupures de courant, pannes matérielles, etc. En cas d'arrêt brutal, MyIsam a notamment le défaut, somme toute comme les systèmes de fichiers FAT et ext2, de nécessiter une vérification complète des données, ce qui peut être très long sur de grosses tables et retarder d'autant le retour en ligne du serveur.
5.3. Infobright
C'est un moteur pour datawarehouse. Un serveur MySQL standard est estimé viable (par Infobright) jusqu'à 500Go de données. Infobright cible la plage au delà de 250Go et est confiant sur ses performances jusqu'à environ 30To.
Ce moteur utilise un stockage en colonnes
[4]. Les données des colonnes sont regroupées en paquets puis fortement compressées, l'algorithme pouvant dépendre du type de données. Le taux atteint normalement aisément 1 pour 10 ; Un client présent a rapporté 1 pour 40. En plus du gain d'espace disque, ces deux techniques permettent de réduire considérablement les I/O disque. Le moteur maintient en outre des métadonnées sur ces paquets de données (min, max,
etc.), qui, même si elles peuvent atteindre 1 % du volume stocké, restent modérément encombrantes. Elles permettent de limiter encore la quantité d'accès disques à réaliser, voir de ne pas avoir à consulter les données elles mêmes. Elles constituent toute l'indexation dont le moteur a besoin. L'espace disque occupé est donc limité comparé à ce que des index usuels peuvent représenter.
Le principal atout de ce moteur vient de sa capacité à fournir de bonnes performances à partir de ses métadonnées sans avoir à changer le schéma, l'indexation, ou encore à reconstruire de nouveaux cubes pour pouvoir espérer voir ses nouvelles requêtes se terminer un jour. D'où un moindre besoin d'expertise technique, et une meilleure réactivité à des besoins changeants (nombreux en Business Intelligence).
Le moteur est open source, la version commerciale apportant divers plus (en plus évidement du support), comme une vitesse de chargement plus élevée ainsi qu'un support des « insert », « update », et « delete » SQL.
5.4. Kickfire
Kickfire se présente comme un moteur MySQL de
datawarehouse tirant parti d'un matériel spécifique. Pour réduire les I/O disque, il stocke ses données en colonnes
[4] sous une forme compressée. De plus, l'essentiel des traitements est réalisé par des circuits logiques dédiés, un peu comme une carte 3d le fait pour l'affichage massif de triangles texturés. Cette architecture, contrairement au CPU, place le flux de données au cœur du traitement afin d'éviter le Goulot d'étranglement de Von Neumann
[5].
L'intérêt est une efficacité grandement accrue : Matériel moins coûteux que n processeurs à m cœurs (dont MySQL ne saurait par ailleurs pas tirer parti), une consommation électrique bien moindre, et un encombrement plus proche d'une freebox ou d'un serveur en rack que d'une armoire normande.
Dans la catégorie 100GB du benchmark TPC-H, Kickfire a obtenu les meilleures performances, et le meilleur coût par requête. Ce produit vise à offrir des performances élevées pour un prix abordable sur des volumes inférieurs à 10TB.
6. MySQL Proxy
Pour qui n'en a pas encore entendu parler MySQL Proxy est un programme se présentant comme un serveur MySQL, toute application utilisant MySQL pouvant donc s'y connecter, mais déléguant le traitement des requêtes qu'il reçoit à un vrai serveur MySQL. Bref un proxy. Tout son intérêt est qu'il décode les requêtes dans un sens, les résultats renvoyés par le serveur dans l'autre, et les expose à un moteur de scripts Lua. Ces scripts peuvent donc aisément faire des statistiques sur les requêtes et leurs résultats, mais aussi les modifier à la volée. Lua a été choisi, car il est assez répandu (surtout dans le domaine du jeu vidéo), rapide, extensible, et surtout son moteur est léger (son runtime ne dépasse pas les 200 Ko). Les scripts peuvent être chargés à chaud.
Parmi les utilisations possibles se trouvent l'écriture de journaux, de statistiques, l'enrichissement de résultats, la réécriture de certaines requêtes pour ne pas avoir à adapter une application cliente que l'on ne maîtrise pas, la création de nouvelles commandes / alias / raccourcis qui seront traités par le proxy (c'est plus simple que de recompiler le serveur), de la répartition de charge, de la gestion de droits, la transmission des requêtes de modification à deux serveurs en parallèle pour basculer sur le deuxième sans perte de transaction en cas de panne, etc.
MySQL Proxy est maintenant stable et est considéré comme utilisable, bien qu'il soit encore en bêta, l'éditeur se réservant encore le droit de modifier ses fonctionnalités. Le surcoût d'un passage par un proxy transparent se contentant de relayer les requêtes est estimé à environ 400µs par requête.
MySQL Proxy étant maintenant utilisable, MySQL commence à fournir des outils se basant dessus. La disponibilité pour les clients achetant l'offre Enterprise de MySQL Query Analyzer a été annoncée lors de la conférence. L'analyseur de requêtes insère un proxy entre les clients et les serveurs afin de profiler les requêtes effectuées (temps d'exécution, taille du résultat...). En mettant la main à la pâte il est possible d'utiliser les journaux de MySQL et divers scripts Perl disponibles sur internet pour identifier les requêtes qui consomment le plus de temps. L'analyseur de requêtes permet de le faire à la demande sur toute une population de serveur via une interface simple d'utilisation. Le mauvais SQL étant la deuxième cause de problèmes de performances, trouver la requête qui tue est important.
Le Load Balancer est aussi basé sur MySQL Proxy. Il permet de répartir la charge sur un serveur maître et ses esclaves de façon transparente en tenant compte de la charge des esclaves. Autre outil, mais dont la disponibilité est plus lointaine, Connection Manager est une sorte de pool de connexions externe aux applications clientes. Il permet de limiter les ouvertures et fermetures de connexions, et aussi de mutualiser (en les multiplexant) des connexions pour un ou plusieurs serveurs Web, par exemple, afin d'alléger la charge de la
BDD.
MySQL Proxy peut aussi aider au
:
HSCALE est construit dessus, et
Spock Proxy en est un fork.
7. Les performances MySQL sous microscope
Cette session était très riche aussi tout son contenu ne sera pas intégralement repris ici. Cette section en donnera les grandes lignes et les points d'intérêts. De là une grande partie des détails peuvent être retrouvés dans la documentation et/ou via Google. Il est à noter que tous les exemples de statistiques montrés ici ont été réalisés sur un serveur avec pour ainsi dire pas de données et pas de charge. Les chiffres ne sont donc absolument pas représentatifs.
Face à des problèmes de performances, et pas uniquement avec une base de données, la démarche consiste à :
- Collecter l'information ;
- L'analyser ;
- Trouver la cause première du problème en écartant les symptômes ;
- La corriger ;
- Recommencer.
Les principaux points à considérer pour les performances de MySQL sont :
- L'indexation ;
- Les requêtes ;
- Le query cache ;
- Le key cache de MyIsam ;
- Le buffer pool d'InnoDb.
7.1. Tracer les requêtes
L'un des principaux outils est ici le slow query log. L'option « log-slow-queries » permet de l'activer, et « long-query-time » de fixer son seuil. Depuis la 5.1 il peut être activé à chaud, filtrer les requêtes d'administration (CREATE/DROP/ALTER), et être stocké dans une table plutôt que dans un fichier.
Pour l'exploiter et agréger les requêtes qui s'y trouvent, mysqldumpslow est fourni avec le serveur (comme mysqldump,
etc.). On peut aussi recourir à
mysqlsla, un script Perl permettant de manipuler les différents journaux de MySQL. Évidemment, tail et grep peuvent toujours servir.
Les clients de l'offre Enterprise disposent aussi de MySQL Query Analyzer.
7.2. L'indexation
Pour une
BDD l'indexation est le nerf de la guerre. Il est important de connaître les différents types d'index et leurs particularités (BTree, RTree, Hash, fulltext, clef primaire « cluster » d'InnoDb). Penser aussi aux index couvrants.
Les index sont réellement utiles s'ils tiennent en mémoire. Si on a un million d'enregistrements, un logarithme binaire suggère qu'une recherche nécessitera près de 20 étapes. En mémoire, c'est bien. Mais sur un disque, ce sont des accès aléatoires dont la quantité par seconde est limitée.
Pour conserver la mémoire des caches il faut veiller à utiliser les types de données les plus petit possibles. On peut n'indexer que le début d'une chaîne de caractères. Dans les cas des grandes chaînes on peut ajouter une colonne contenant un CRC32 (qui a l'avantage d'être très compact) ou un hash ; L'index, portant sur le CRC, est ainsi beaucoup plus petit et sert à dégrossir les recherches. Les requêtes devront par contre filtrer sur le CRC et la chaîne à cause des collisions.
En parlant des chaînes de caractères, les VARCHAR aussi doivent être le plus court possible. Leur longueur variable leur permet de n'occuper que la place nécessaire sur le disque et dans les index, mais les tables de type HEAP ont des enregistrements de taille fixe, ce qui convertit un VARCHAR en un CHAR équivalent, et rend l'UTF-8 particulièrement encombrant. Or, c'est le type des tables temporaires, qui peuvent être utilisées explicitement, mais sont aussi utilisées implicitement pour les tris et les regroupements. C'est une perte de mémoire, et, de plus, si une table temporaire devient trop grosse, elle passe sur le disque, ce qui change complètement ses performances.
Le partitionnement horizontal peut permettre de garder les index qui comptent en mémoire, chaque partition ayant des index séparés. Idem évidemment pour le
sharding.
Le partitionnement vertical est à essayer : Une table qui a un rôle central (par exemple des clients) dans de nombreuses requêtes via des jointures peut gagner à écarter tout ce qui n'est pas utilisé, soit dans ces jointures (par exemple une adresse), soit dans les principales clauses de filtrage.
7.3. Les requêtes
Rien ne peut sauver une mauvaise requête. Il est en particulier important de s'assurer que les requêtes utilisent bien les index. Pour cela il n'y a pas d'autre solution que d'utiliser EXPLAIN (et d'apprendre à interpréter son résultat). Avec une machine de test, il est facile de rajouter des index, et regarder l'évolution du plan d'exécution (attention à utiliser de vraies données, car le contenu des tables influe sur les choix de MySQL).
Il ne faut pas hésiter à tester différentes approches pour une requête. Par exemple, l'utilisation de jointures est généralement plus efficace que des sous-requêtes (comme un EXISTS), mais ce n'est pas toujours vrai.
Certaines requêtes sont assez réfractaires à l'indexation. Par exemple, la recherche d'un point par sa distance à un autre :
SELECT * FROM mespoints WHERE SQRT(POW(x-450,2)+POW(y-350,2))<=30
|
Puisque x et y ne sont pas utilisés directement la requête ne peut utiliser d'index. Pour limiter, les dégâts on peut tenter d'encadrer les valeurs recherchées :
SELECT * FROM mespoints
WHERE (x BETWEEN 420 AND 480)
AND (y BETWEEN 320 AND 380)
AND SQRT(POW(x-450,2)+POW(y-350,2))<=30
|
Il est alors possible d'utiliser un index sur x
ou y. L'idéal, ici, serait encore d'utiliser les fonctionnalités
GIS pour disposer d'un index spatial, justement fait pour ce genre de cas.
Accessoirement, pour tirer un enregistrement aléatoirement, la requête suivante ne fonctionne pas, du moins pas en termes de performances :
SELECT * FROM MaTable ORDER BY RAND() LIMIT 1
|
MySQL tirera une valeur aléatoire par enregistrement dans la table avant de tous les trier dans une table temporaire, pour ensuite n'en garder qu'un. Bref à éviter.
Pour utile qu'il soit, LIMIT est en général à manier avec précaution. Comme ci dessus, il ne garantit pas que le traitement de la requête ne manipulera pas de nombreux enregistrements, seulement qu'un seul sera retourné. Il faut en particulier se méfier de LIMIT pour le fenêtrage (ie « LIMIT 1000, 10 » avec un tri pour récupérer les résultats 1000 à 1010) : Quand on arrive à des décalages importants, ces requêtes impliquent beaucoup de traitements comparativement au nombre d'enregistrements retournés.
Si l'on dispose d'une clef primaire ordonnée (comme un AUTO INCREMENT), on peut compléter le LIMIT par une clause WHERE limitant la requête à une plage de clefs : Par exemple, une plage couvrant environ les 100 derniers enregistrements (à ajuster suivant les cas) dans laquelle on pense raisonnablement trouver les 10 valeurs dont on a besoin. Ainsi, on garantit que MySQL ne traitera qu'au plus 100 enregistrements. Si jamais on ne trouve pas assez de résultats, il sera encore temps de lancer une autre requête étendant la plage.
7.4. Le query cache
Il stocke les requêtes et leurs résultats. Il permet de réduire drastiquement le travail que doit exécuter le serveur. Une requête en est retirée quand une des tables sur lesquelles elle porte est modifiée. Cette opération est bloquante ! Il faut donc éviter d'utiliser un cache trop grand (ne pas lui donner 1Go), car l'invalidation d'une trop grande quantité de données peut geler le serveur pendant plusieurs secondes.
« query_cache_size » donne sa taille, et « query_cache_limit » limite la taille des résultats qui peuvent y être conservés. Un « SET GLOBAL » peut les modifier à chaud. Les autres variables de son paramétrage commencent aussi par « query_cache_ ».
Son état est à suivre via :
mysql> SHOW GLOBAL STATUS LIKE 'Qcache_%';
+
| Variable_name | Value |
+
| Qcache_free_blocks | 5 |
| Qcache_free_memory | 52395136 |
| Qcache_hits | 4969 |
| Qcache_inserts | 11128 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 10155 |
| Qcache_queries_in_cache | 11 |
| Qcache_total_blocks | 33 |
+
8 rows in set (0.01 sec)
|
En particulier le ratio entre « Qcache_hits » et « com_select » donne la fraction des requêtes qui bénéficient du cache.
7.5. Le key cache de MyIsam
MyIsam n'a pas de cache des données (pour cela il se repose entièrement sur l'OS), seulement un cache des index. Il est possible d'en définir plusieurs pour une configuration plus fine (et limiter les problèmes de concurrence). L'invalidation se fait en
LRU.
Dans le cas par défaut il n'y en a qu'un dont la taille est définie par « key_buffer_size ». Cette variable peut être changée à chaud. Les variables influençant ce cache commencent par « key_ » :
mysql> SET GLOBAL key_buffer_size=150*1024*1024;
Query OK, 0 rows affected (0.03 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'key_%';
+
| Variable_name | Value |
+
| key_buffer_size | 157286400 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
+
4 rows in set (0.00 sec)
|
Pour son suivi les indicateurs commencent aussi par « key_ » :
mysql> SHOW GLOBAL STATUS LIKE 'key_%';
+
| Variable_name | Value |
+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 16803 |
| Key_blocks_used | 15 |
| Key_read_requests | 68 |
| Key_reads | 24 |
| Key_write_requests | 0 |
| Key_writes | 0 |
+
7 rows in set (0.00 sec)
|
Le ratio « Key_reads / Key_read_requests » permet de savoir quelle fraction des accès aux index tombe sur des clefs déjà dans le cache.
7.6. Le buffer pool d'InnoDb
InnoDb est mieux loti en termes de cache. Il dispose uniquement de son buffer pool dans lequel il place ses index, ses données, ses données modifiées par les transactions courantes, etc. Sa taille est fixée par « innodb_buffer_pool_size ».
Pour le suivi on peut s'intéresser à toute la série des « Innodb_buffer_pool_ ». Le statut d'InnoDb donne aussi des informations dans l'une de ses sections, en particulier un hit rate immédiatement lisible :
mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_%';
+
| Variable_name | Value |
+
| Innodb_buffer_pool_pages_data | 6381 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 30435 |
| Innodb_buffer_pool_pages_free | 2 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 17 |
| Innodb_buffer_pool_pages_total | 6400 |
| Innodb_buffer_pool_read_ahead_rnd | 42 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 3929673 |
| Innodb_buffer_pool_reads | 3077 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 1519505 |
+
13 rows in set (0.00 sec)
mysql> SHOW INNODB STATUS;
[...]
BUFFER POOL AND MEMORY
Total memory allocated 17412982; in additional pool allocated 897792
Buffer pool size 512
Free buffers 456
Database pages 55
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 49, created 6, written 54
0.00 reads/s, 0.00 creates/s, 0.07 writes/s
Buffer pool hit rate 1000 / 1000
[...]
|
Minute publicitaire : MySQL Enterprise Monitor permet de suivre facilement les indicateurs des différents caches (entre autres) sur une population de serveurs. En particulier, il permet de générer des alertes quand, par exemple, le ratio de recherches dans un cache couronnées de succès tombe en dessous d'un certain seuil. Si le volume de données croissant fait baisser l'efficacité d'un cache, mieux vaut le savoir avant que les performances ne s'écroulent. Même sans utiliser Enterprise Monitor, une surveillance via d'autres outils ou des scripts maison est importante.
8. Scale up, scale out, virtualisation. Que faire avec MySQL ?
Sun vend du matériel, et plutôt dans la catégorie poids lourds. Si les processeurs avec plusieurs cœurs sont récents dans le monde des x86, certains UltraSPARC gèrent 8 threads par puce depuis déjà un moment, et l'UltraSPARC T1 est encore pire. Un serveur Sun à base d'UltraSPARC avec 8 processeurs en arrive donc rapidement à supporter 64 threads en parallèle. Du sur mesure pour SAP, Oracle...
Seulement, Sun a racheté un serveur de
BDD qui peut utiliser efficacement 4 et même 8 cœurs en parallèle, éventuellement 16. Au delà le gain est négligeable, à supposer que les performances ne se dégradent pas.
L'une des conséquences de cela est que Sun a tout intérêt à ce que MySQL tire mieux parti des machines avec beaucoup de cœurs, et a de l'expérience dans l'optimisation pour ce genre de matériel. Les serveurs x86 « standards » s'approchant les limites de montées en charge de MySQL, c'est une bonne chose. On peut s'attendre à des patchs améliorant sensiblement la scalabilité de la version 5.1 et d'autres améliorations futures.
Sun suggère aussi de faire tourner plusieurs instances de MySQL sur un même serveur, que ce soit pour plusieurs applications différentes n'utilisant que partiellement leurs serveurs (consolidation), ou pour des « shards » d'une même application (c'est à dire du
scale out sur une même machine). C'est une solution compétitive et économique, y compris en consommation électrique, par rapport au même nombre d'instances de MySQL tournant sur des serveurs physiques différents.
Quelques remarques intéressantes concernant la virtualisation ont été faites. Tout d'abord, avec les serveurs de
BDD, il faut être prudent, car la virtualisation ajoute une couche entre le serveur et le matériel : Il y a un risque que des données que le serveur pense avoir écrites aient été « écrites » par l'OS invité, mais pas encore par l'OS hôte, et donc de perdre les garanties
ACID d'un moteur transactionnel. En outre il existe différents types de virtualisation plus ou moins souples, mais aussi plus ou moins coûteux en performances. Notamment, si l'on n'a pas besoin de modifier le système hôte, Solaris dispose d'un système de « containers » (BSD a d'ailleurs un système semblable, bien qu'à première vue moins complet, avec ses « jails »), qui sont des systèmes virtuels isolés les uns des autres avec un surcoût modique. La virtualisation complète a l'avantage de la souplesse, ce qui la rend très utile en développement, mais son coût en performances devrait la tenir à l'écart de la production.
9. Retour de client : Virgin Mobile
Tout le suivi des clients de Virgin Mobile de fait sur une pile applicative 100 % open source (une première dans le milieu), du LAMP qui a donné pleine satisfaction.
Le développement a été rapide, 4 mois, et la plate forme a du faire face à une croissance très rapide, dépassant toutes les prévisions. En un an, le nombre de clients a triplé, passant de 300 000 à 1 000 000.
Pour y faire face l'infrastructure a commencé avec un serveur Web et deux serveurs de
BDD en réplication actif/passif. Elle dispose maintenant de deux pare-feux, deux load balancer, des serveurs Webs, et des bases de données regroupées par fonctionnalités (autrement dit utilisant un partitionnement fonctionnel). Chaque groupe de
BDD est constitué d'un maître et d'esclaves. Des réseaux dédiés sont présents pour la réplication et les sauvegardes. L'opérateur totalise 2,5To de données répartis en 40 bases (schémas) sur 12 serveurs.
Pour ce qui est des sauvegardes, mysqldump est utilisé jusqu'à 100Go. Pour les plus grosses bases elles sont réalisées à froid sur un esclave.
Sur une telle plate forme, la supervision est une nécessité, surtout lorsque, pour des raisons marketing, il peut y avoir plusieurs mises en production en une journée : Il est indispensable de suivre l'effet des nouveaux développements, de contrôler qu'ils n'introduisent pas de « requête de la mort ». Il faut s'assurer que la réplication se fait, et dans quel délai (MySQL Enterprise Monitor étant dans ce domaine très utile) ; Suivre le temps d'exécution des requêtes et analyser quotidiennement le slow query log.
Pour le datawarehousing, tout fonctionne sur MyIsam et Talend. L'opérateur étudie une éventuelle utilisation d'Infobright.
Sigles et définitions
BDD : Base De Données.
ETL : Extract Transform Load
[6].
GIS : Geographic Information System
[10].
LRU : Least Recently Used
[9].
OLTP : OnLine Transaction Processing
[8].
Partitionnement horizontal : Le partitionnement horizontal consiste à répartir les enregistrements d'une table sur plusieurs partitions suivant un critère (la clef de partitionnement). Par exemple, on peut utiliser une date pour avoir une partition par mois. À toutes fins utiles une partition est elle-même une table. Ce partitionnement est en général pris en charge par le serveur de façon transparente ; Il n'impacte donc pas les requêtes SQL.
Partitionnement vertical : Le partitionnement vertical consiste à répartir les colonnes d'une table sur plusieurs tables différentes. Il est intéressant, car il permet de séparer les colonnes les plus utilisées des autres afin qu'elles ne ralentissent pas les requêtes courantes, et ne polluent pas les caches. Ce partitionnement n'est généralement pas transparent.
Saas : Software as a Service
[7].
Scale Out : C'est étendre un système en y ajoutant des éléments, c'est-à-dire des serveurs
[12].
Scale Up : C'est étendre un système (pour suivre une augmentation de la charge) en empilant plus de ressources (plus de fréquence CPU, plus de CPU, plus de mémoire, plus de disques,
etc.)
[11].
Sharding : Le sharding est une forme de partitionnement horizontal où les données sont réparties sur plusieurs serveurs différents. La répartition est généralement faite au niveau applicatif, et nécessite donc d'être prise en compte dès la conception. Contrairement au partitionnement horizontal normal, les données réparties concernent plusieurs tables. Par exemple, on répartira les utilisateurs en plaçant sur un serveur donné toutes les données concernant un utilisateur, y compris ses commandes,
etc. Le sharding est très utilisé pour les applications Web, car il permet de multiplier à volonté les serveurs de BDD pour tenir la charge là où un seul serait dépassé. Pour plus de détails voir
Wikipedia [2] ou
cet article sur highscalability.com [3].
Remerciements
Je remercie
CinePhil pour la relecture de cet article.
Ce document est issu de http://www.developpez.com et reste la propriété exclusive de son auteur.
La copie, modification et/ou distribution par quelque moyen que ce soit est soumise à l'obtention préalable de l'autorisation de l'auteur.
