MySQL et le moteur Blackhole

MySQL a l’avantage de pouvoir fonctionner avec différents moteur de stockage. Les plus connus sont sans aucun doute MyISAM, moteur historique très rapide en lecture mais dépourvu de transaction, et InnoDB qui est un moteur transactionnel capable d’absorber des charges plus importantes. Plusieurs autres moteurs sont également disponibles et répondent à des besoins particuliers. Memory permet par exemple d’utiliser des tables en mémoire. Enfin le moteur Blackhole permet comme son nom l’indique de ne rien stocker ! Alors me direz-vous à quoi cela peut-il bien servir un moteur de stockage qui ne stocke rien ??

Le moteur Blackhole et la réplication

Dans un système à forte charge il est fréquent de trouver plusieurs serveurs MySQL synchronisés entre eux via la réplication des données. Toutes les données ajoutées sur le serveur Maître sont sont enregistrées dans un journal qui est alors envoyé sur les différents serveurs Esclave pour y être joué afin que les bases soient parfaitement synchronisées. Etant donné que le journal contient les ordres sql, les serveurs MySQL peuvent fonctionner avec différents moteurs de stockage.

Utilisation en Proxy

Si les tables sur le Maître utilisent le moteur Blackhole, alors celui-ci ne fait qu’enregistrer les ordres SQL et les transmet aux différents serveurs Esclave. Bien entendu, seulement une ou une partie des tables peuvent être configurées de cette manière afin de répartir la charge entre différents serveurs dans le cas de grosses charges en écriture.

Utilisation en filtre de réplication

Un des inconvénients de la réplication MySQL actuelle, c’est qu’elle ne permet pas de de ne répliquer qu’une partie des données présentes sur le Maître. Ainsi, si un Maitre possède beaucoup de données, les Esclaves sont en principe obligés de supporter la même volumétrie même si ces données ne sont pas utilisées. Comme les tables sur un Esclave peuvent ne pas utiliser le même moteur de stockage que sur le Master, il est possible de déclarer les tables sur l’Esclave avec un moteur Blackhole. Ainsi, lorsque le processus de réplication jouera les ordres SQL reçus du Maître, celle-ci seront jouées, mais sans aucune conséquence ni sur les accès disque ni sur la charge CPU du serveur.

Exemple

Vous gérez plusieurs serveur MySQL en réplication avec une grosse base de données et beaucoup de requêtes… Malheureusement, vous devez exécuter des requêtes très consommatrices d’accès disque et qui ne sont pas optimisables, du genre :

SELECT id FROM users WHERE name like '%dupont%';

Cette requête fera inlassablement un « Full scan » de la table et ne peut pas utiliser d’index. Si la table contient beaucoup d’enregistrements et/ou un certain volume de données, il sera très difficile d’obtenir des temps de réponse corrects. En prime, les autres requêtes seront ralenties.

Une solution ? Ajoutez un serveur Esclave, mais déclarez toutes les autres tables en Blackhole et réservez ce serveur à l’exécution de ces mauvaises requêtes. La réplication n’utilisera que très peu d’accès disque qui seront quasiment dédiés à ces mauvaises requêtes.

Un solution « riche » ? Si votre serveur a suffisamment de mémoire, déclarez une table Memory qui ne contiendra que ce champ « name » ou les champs nécessaires à vos requetes, et positionnez un trigger sur la table d’origine afin que les modifications apportées par la réplication soient répliquées dans la table en mémoire.

Quelques chiffres

Chez Wikio, nous avons mis en place cette solution pour faire une test avec une table qui occupe 1Go sur disque. Les temps de réponse sont les suivants :

• Sur une esclave non dédié : entre 10 et 30 secondes suivant la charge occasionnée par les autres requêtes
• Sur un esclave ne répliquant que les tables nécessaires : 9 secondes
• En mettant les tables en mémoire : 3 secondes

Ben sûr, vous me direz, la solution est coûteuse… mais dans certains cas, il est préférable de monopoliser un serveur afin que les traitements aillent 3 fois plus vite !