Modifier la structure des tables mysql sans bloquer l'utilisation avec pt-online de Percona

Exécuter des alter table dans une base mysql ou mariadb est très courant durant la vie d'un projet. Pourtant cette opération simple devient de plus en plus compliquée à planifier au fur et à mesure que la volumétrie des données augmente. Il suffit de quelques centaines de milliers de ligne pour que cela pose problème. De par sa conception, une modification de structure sur une table InnoDB bloque les écritures (insert, update, delete) durant toute l'opération. Quand cela prend moins d'un minute c'est embêtant mais tolérable, quand c'est plusieurs dizaines de minutes voir des heures c'est impossible à faire.

Heureusement il existe des outils pour palier à ce problème, pt-online de Percona est l'un d'entre eux. Il permet de jouer des alter table sans bloquer les écritures sur la table. Le principe est assez simple, pt-online crée une copie de la table, y duplique les données, ajoute un trigger pour écouter toutes les modifications de donnée pendant qu'il joue l'alter table sur la copie. Une fois l'alter table finis il rejoue les modifications enregistrées dans le trigger puis renomme la table modifiée pour remplacer celle d'origine.

pt-online fait parie de la suite Percona Toolkit que l'on peut télécharger ici: https://www.percona.com/downloads/percona-toolkit/LATEST/

Pour l'exemple je vais utiliser une base de mot de passe fournie par le site Have I Been Pwned, le fichier "update 1" me donnera une table d'environ 1Go ce qui est suffisant.
Le schema de la table est assez simple:

CREATE TABLE `passwd_dictionary` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `hashed_passwd` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13696816 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Pour charger les données depuis le fichier décompressé dans /tmp:

MariaDB [hibp]>LOAD DATA INFILE '/tmp/password.txt' INTO TABLE passwd_dictionary (hashed_passwd);

Commençons par récolter quelques métriques, combien de temps me faut-il pour trouver un hash dans la table et pour insérer un nouveau mot de passe.

MariaDB [hibp]> SELECT SQL_NO_CACHE * FROM passwd_dictionary WHERE hashed_passwd = '175FFE731F27432C706F872566DFD33EF5519DD9';
Empty set (2.98 sec)
 
MariaDB [hibp]> INSERT INTO passwd_dictionary (hashed_passwd) VALUES ('password');
Query OK, 1 row affected (0.32 sec)

Pour le test je vais modifier la taille du champs hash_passwd  de 255 à 50. Pendant que la requête s’exécute je vais relancer (dans un autre terminal) les 2 requêtes ci dessus pour voir l'impact de l'alter table.

MariaDB [hibp]> ALTER TABLE  passwd_dictionary CHANGE hashed_passwd 
`hashed_passwd` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL ;
Query OK, 13675934 rows affected (46.52 sec)           
Records: 13675934  Duplicates: 0  Warnings: 0
 
MariaDB [hibp]> SELECT SQL_NO_CACHE * FROM passwd_dictionary WHERE hashed_passwd = '175FFE731F27432C706F872566DFD33EF5519DD9';
Empty set (3.85 sec)
 
MariaDB [hibp]> INSERT INTO passwd_dictionary (hashed_passwd) VALUES ('password2');
Query OK, 1 row affected (8.11 sec)
 

pt-online ne s'utilise pas depuis le client mysql/mariadb mais depuis la ligne de commande linux. L'utilitaire comprend beaucoup d'option, je ne vais pas toutes les expliquer, la documentation est très explicite.

root@ulaptop:~# pt-online-schema-change \
> --alter "change hashed_passwd hashed_passwd varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL" \
> D=hibp,t=passwd_dictionary --alter-foreign-keys-method auto \
> --max-load Threads_running=50 --critical-load Threads_running=100 \
> --no-drop-old-table --host=localhost --user=root --execute
 
No slaves found.  See --recursion-method if host ulaptop has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
No foreign keys reference `hibp`.`passwd_dictionary`; ignoring --alter-foreign-keys-method.
Altering `hibp`.`passwd_dictionary`...
Creating new table...
Created new table hibp._passwd_dictionary_new OK.
Altering new table...
Altered `hibp`.`_passwd_dictionary_new` OK.
2018-01-07T21:30:10 Creating triggers...
2018-01-07T21:30:10 Created triggers OK.
2018-01-07T21:30:10 Copying approximately 13275314 rows...
 Copying `hibp`.`passwd_dictionary`:  71% 00:12 remain
2018-01-07T21:30:54 Copied rows OK.
2018-01-07T21:30:54 Analyzing new table...
2018-01-07T21:30:54 Swapping tables...
2018-01-07T21:30:54 Swapped original and new tables OK.
Not dropping old table because --no-drop-old-table was specified.
2018-01-07T21:30:54 Dropping triggers...
2018-01-07T21:30:54 Dropped triggers OK.
Successfully altered `hibp`.`passwd_dictionary`.

La commande à une sortie très verbeuse, on peut y lire tout ce qui se passe. Elle a pris à peu prés 50 secondes ce qui la rend un peu plus lente que l'alter table.
On peut voir que les paramètres sont nombreux, à commencé par --alter qui ne contient que le changement et pas toute la requête.
Attention aux option D (database) et t (table) qui sont séparé par une virgule sans espace.
Les options --max-load et --critical-load permettent de respectivement mettre en pause ou abandonner la commande. A modifier en fonction des performances de votre serveur.
Dans certains cas il peut être utile de ne pas supprimer tout de suite l'ancienne table, c'est le rôle de --no-drop-old-table, attention à l'espace disque.
Et pour finir --execute permet d'éxecuter la commande, je vous conseille de toujours remplacer cette option par --dry-run la première fois pour tester votre commande.

Est ce que la promesse de l'outil est tenue à savoir peut on faire des écritures sans être bloqué pendant l'opération? La réponse est oui, j'ai joué plusieurs insert, ils ont tous été instantané.

pt-online est un outil incontournable quand les bases de données grossissent, mais il est tellement simple d'utilisation qu'au final pourquoi attendre? Il existe d'autres outils du même genre créé par facebook, github, à vous de choisir le votre.