Augmenter la vitesse de vos requêtes SQL en changeant le moteur de stockage

Il y a quelque jours et comme à mon habitude je me suis retrouvé face à un problème.

Une fonction complexe de mon application prenait un temps considérable. Pour le contexte c’est une fonction qui exécute un très grand nombre de requête à la base de donnée. Des SELECT à tout va sur plusieurs tables ne contenant pas plus d’une centaine de lignes chacune, mais surtout des SELECT, DELETE, INSERT et UPDATE des plusieurs milliers de lignes sur une seule et même table. Pour vous dire la fonction mettais plus de 50 secondes à se terminer sur la prod.

Après plusieurs essaies d’optimisation comme la modification de la conf mysql sur la prod, ou un gros refact sur ma fonction en utilisant au maximum le cache système de mon application pour diminuer le nombre de requête SQL. Rien n’y fait.

On me donne comme piste de me renseigner les moteurs de stockage de ma base de donnée MySQL. Pour l’instant l’intégralité de ma base est en InnoDB.

Après lecture de ce fabuleux article qui compare ces deux moteurs. J’ai écrit une migration pour changer le moteur de stockage de ma table  sur laquelle l’application effectue grand nombre de requête. J’ai donc passé le moteur de cette table de InnoDB à MyISAM, car oui, une même base de donnée peut avoir plusieurs moteurs de stockage en fonction de nos besoins.

La migration en question.

class ConvertVotreTableIntoMyisam extends Migration { /** * Run the migrations. * * @return void */ public function up() { DB::statement('ALTER TABLE ' . 'votreTable' . ' ENGINE = MyISAM'); } /** * Reverse the migrations. * * @return void */ public function down() { DB::statement('ALTER TABLE ' . 'votreTable' . ' ENGINE = InnoDB'); } }

Attention, comme indiqué dans l’article précédemment cité, les table avec l’engine MyISAM ne doivent lié à aucune autre table part des foreign_key. Pensez à effectué une migration avant pour les supprimer si vous le pouvez.

90% ! C’est le gain de perf que j’ai obtenu en production sur ma fonction en passant ma table de InnoDB en MyISAM!

Notes à savoir: L’engine par default des tables de votre base de donnée dépends de votre version de MySQL, avant la 5.5.5, c’est MyISAM, après c’est InnoDB.

Dans Laravel vous pouvez changer ça dans config/database.php, pour mysql il est de base à ‘engine’ => null, donc vos tables seront soit en MyISAM ou en InnoDB en fonction de votre version MySQL. Vous pouvez fixer le moteur par défaut ‘engine’ => ‘InnoDB’, ou ‘engine’ => ‘MyISAM’,

Installer et configurer MYSQL sous docker pour en finir avec Vagrant (sur MacOS)

Pré-requis :

  • Docker : https://hub.docker.com
  • Kitematic (GUI pour Docker) : https://github.com/docker/kitematic/releases

Une fois installé, lancez l’application Docker et connectez vous avec votre compte.

Ensuite, via Kitematic, téléchargez l’image « officielle » mysql qui vous intéresse (en général la ‘:latest’)

Kitematic va lui même créer un container Docker en se basant sur l’image précédemment téléchargée.

Startez le container via Kitematic et allez dans ‘Settings’. Ajouter une ligne aux Environment Variables ‘MYSQL_ROOT_PASSWORD’, et enregistrez.

Vos variables devraient ressembler à ça :

Si vous le désirez, vous pouvez changer et configurer les ports de sorties dans ‘Hostname/Ports’.

Redémarrer le container, et vous avez un service mysql qui tourne 🙂

Tips: Si vous n’arrivez pas à connecter vos projets (Ruby On Rails pour notre cas) avec ce nouvel environnement à cause de cette erreur :

« Authentication plugin ‘caching_sha2_password’ cannot be loaded »

C’est qu’il y a un problème d’encryptage du mot de passe ‘root’ (certainement dû à une divergence de mysql-server/client).

Pour se faire, deux solutions :

  • Via Kitematic, cliquez sur ‘Exec’
  • Via un terminal, lancez les commandes suivantes
docker ps (pour connaitre votre container_id) docker exec -it CONTAINER_ID bash

Puis, dans la console ouverte, mettez à jour le mot de passe ‘root’ via :

mysql --user=root --password ALTER USER 'root' IDENTIFIED WITH mysql_native_password BY '1234';

Gestion de projet avec Gitlab

Ces derniers jours nous avons utilisé Gitlab pour gérer nos projets.

Cette nouvelle approche permet au codeur d’être à la fois plus rigoureux dans la façon d’écrire son code mais aussi de monter en compétence car chaque ligne de code que vous écrivez sera relu et analysé par un autre membre de votre équipe.

Au niveau de l’équipe, cette gestion de projet permet de travailler ensemble et de façon asynchrone sur une même étape importante du projet (Milestone/Iteration).

L’énorme avantage de cette approche est de pouvoir créer, attribuer et gérer des taches qui sont intimement liées au code du projet. C’est à dire de créer une tâche, l’attribuer à un codeur et la lier de façon quasi automatique à une branche de travail. Le codeur pourra ainsi travailler sur cette tâche sans gêner ou ralentir ses collègues, même si ils travaillent sur des parties de code extrêmement proches.

 

LES ISSUES

Sur le même modèle qu’un KANBAN, les issues sont les tâches a faire.

Elles peuvent être attribuées à une ou plusieurs personnes.

Différents Labels (statuts) peuvent leur être assignés, Open, To Do, Doing, Review et Closed

Dans chaque issue, les différents membres de l’équipe peuvent partager et discuter sur la mise en oeuvre de la fonctionnalité demandé.

Chaque issue peut être lié ou non à une milestone (étape importante du projet). La milestone est terminé lorsque toutes les issues lié à cette dernière on été close.

Elles permettent également de créer en un seul clic, une Merge Request et la branche de travail associée.

LE BOARD

Les Board est semblable à un trello. On peut déplacer les différentes issues de labels en labels en fonction de leur avancement.

LES MERGE REQUESTS

Lorsqu’une issue a été prise ou attribuée à une personne, qu’elle est passé en Doing et que la merge request a été ouverte. Le codeur n’a plus qu’a aller travailler sur la branche associée de cette dernière.

Chaque merge request permet de :

  • Comparer les changements entre deux branches lors des codes review
  • Examiner et discuter des modifications proposées en ligne
  • Aperçu en direct des modifications
  • Empêcher de merger grace au status WIP
  • Résoudre automatiquement les problèmes de fusion.
  • Résoudre les conflits de fusion à partir de l’interface utilisateur
  • Squash les commits pour un historique plus propre

 

LE PROCESS A SUIVRE

Maintenant que les différentes couches de cette nouvelle approche de gestion de projet on été vue. Voici le process à suivre pour l’intégrer efficacement dans votre quotidien.

1 – Prendre une issue:

Si la tâche ne vous a pas encore été attribuée, allez dans le Board voir les issues qui sont dans le label To Do.

Sélectionnez l’issue désiré, lisez la en détail et attribuez la vous.

2 – Commencer à travailler:

Une fois l’issue attribuée à votre nom et que vous voulez commencer à travailler dessus.

Dans le Board glissez la dans Doing.

Ouvrez l’issue,  et cliquer sur Create Merge Request

Une merge request avec le satut WIP est crée automatiquement, la branche de travail associé à cette dernière a également été crée.

3 – Dans votre terminal:

Passez sur la branche Develop.

Faites un git pull. La nouvelle branche que vous venez de créer a été rapatrié.

Faites un git checkout votre-nouvelle-branche

4 – CODER

Faites des git commit/ push de temps en temps, le code sera pousser sur la branche de travail.

Faites également un git commit dès que vous voulez changez de branche

5 – Passer en Review:

Une fois que les demandes de l’issue ont été complétées.

git commit, git push votre code.

Allez voir dans GitLab votre Merge Request.

Il se peut que vous ayez des Merge Conflit. Vous pouvez tenter de les résoudre automatiquement sur GitLab.

Sinon dans votre terminal.

git commit votre travail

Puis faites un git checkout develop

un git pull

retournez sur votre branche de travail avec un git checkout votre-nouvelle-branche

git rebase develop, des merge conflits sont apparus!

Résolvez les.

git rebase –continue

git commit, git push -f

Retournez voir dans GitLab, votre problème de Merge Conflit a disparu.

Cliquez sur Resolve Wip Status, pour montrer que le travail est terminer pour cette merge request.

Passez également le status de la MR en Review

Allez également dans le Board et glisser l’issue concerné dans le label Review.

6 – Attendre les retours de la Review

Si vous avez des retours fait par votre reviewer reprenez à l’étape 4

(Pensez à close les discussions pour chaque commentaire fait lorsque vous avez résolu le problème.)

Si aucun retour sur votre Merge Request n’a été fait, votre reviewer fera le Merge.

7 – Reprendre à l’étape 1 jusqu’à avoir fini la Milestone

 

Vous êtes Reviewer:

Allez voir dans le Board les issues qui sont dans le label Review.

Sélectionnez en une.

Ouvrez la Merge Request associée. Vérifiez qu’il n’y a plus le statut WIP et qu’il n’y a pas de Merge Conflict. Sinon renvoyez au codeur.

Lisez dans Changes le nouveau code. Cliquez sur une ligne pour y laisser un commentaire si vous avez une question sur le fonctionnement ou une proposition de changement. Veuillez à que votre commentaire soit toujours bienveillant envers le codeur. Enseignez les bonnes pratiques.

Le codeur sera notifié de vos commentaires et saura qu’il doit retourner modifier son code.

Une fois que tout les changements on été effectués et que la Merge Request est parfaite à vos yeux. Vous pouvez merger.

Avant de Merge, pensez à sélectionner Remove Source Branch pour supprimer automatiquement la branche lié à la merge request et Squash Commits pour ne pousser qu’un seul commit dans la branche develop. (Ce commit portera le nom de la merge request)