Quelques mots sur Alter Table, ou comment ne pas le faire

Ce n'est probablement pas un article, mais une courte note sur certaines fonctionnalités du travail avec de grandes tables dans MySQL.

La raison de l'écriture était l'ajout apparemment quotidien d'une nouvelle colonne à la table. Mais tout n'a pas été aussi simple que prévu.

Alors, un soir, pour ne pas déranger nos chers clients, il a fallu ajouter une colonne à la table.

Pour plus de clarté, les caractéristiques de la table et de la base:

• taille de la table 110Gb
• nombre de rangées: 7,5 millions
• moteur de stockage: InnoDB
• il y a deux serveurs sql connectés selon le schéma maître-esclave, tandis que le maître est sur le SSD et l'esclave sur le disque dur

Cela semble être une solution évidente pour ajouter une colonne - Alter Table.

alter table table_name add source varchar(32) 

Nous l'avons utilisé (oui, nous avons compris que c'était mauvais, mais dans ce cas particulier les risques étaient minimes).

Les résultats ont été assez désagréables:

• sur l'assistant, le processus d'ajout d'une colonne a pris environ une heure (!)
• sur l'esclave, il a commencé après la fin du processus sur le maître et a duré environ 8 heures (!!)
• pendant la table alter, la réplication des données (!!!) s'est complètement arrêtée sur l'esclave

Mais il y a une doublure argentée: un petit bonus était qu'après l'ajout d'une colonne, la taille du tableau diminuait de 10%.

Dans les graphiques ci-dessous, il est clairement visible.


Graphique de charge CPU sur l'assistant.


Graphique de charge CPU sur l'esclave.


Retard de réplication.

Quels ennuis attendent ceux qui font cela sur les tables de bataille?

Tout d'abord, pendant la durée de la table Alter, vous ne pouvez pas écrire de données dans la table (mais vous pouvez les lire). En fait, cela dépend de la version de MySQL, dans ce dernier, ce n'est pas le cas, mais vous devez néanmoins comprendre de quoi votre version est capable afin d'éviter les problèmes.

En conséquence, si le tableau est volumineux, le temps d'indisponibilité sera important (comme avec nous, lors de l'utilisation du SSD, cela a pris une heure, et sur un disque normal - 8 heures), ce que vos clients ne s'attendent probablement pas.

Deuxièmement, comme dans notre cas, lors de l'exécution de la table Alter, la synchronisation de toutes les tables , pas seulement celle que nous avons modifiée, s'est complètement arrêtée sur l'esclave. Par conséquent, si vos données sur le deuxième serveur sont critiques et doivent être fraîches - vous risquez de vous retrouver sans mises à jour avec toutes les conséquences qui en découlent.

Un autre point non évident que nous avons rencontré lors de l'ajout d'une colonne (mais c'était une autre fois) - un espace disque supplémentaire est nécessaire .

Le fait est que certaines modifications apportées aux tables recréent la table à partir de zéro, vous n'avez donc pas besoin de moins d'espace qu'une table existante. Pour les grandes tables, respectivement, beaucoup d'espace est nécessaire, pour le moins. Selon la documentation, une table temporaire est créée dans le même répertoire que l'original.

De plus, lors de l'exécution de toutes sortes d'Alter Table, toutes les modifications sont enregistrées dans un fichier journal, de sorte qu'après les modifications, les données peuvent être reportées sur la durée pendant laquelle l'opération a été effectuée. Et là aussi, une surprise désagréable peut vous attendre: si la table change pendant longtemps et que le volume des opérations est important, non seulement l'espace disque peut se terminer, mais également la limite de taille de fichier spécifiée dans les paramètres SQL peut être dépassée. Dans tous les cas, «l'opération DDL en ligne échoue et les opérations DML simultanées non validées sont annulées» vous attend.

Nous étions confrontés au fait que le répertoire des fichiers temporaires était petit, par conséquent nous avons dû redéfinir innodb_tmpdir .

Pour voir où la variable pointe actuellement, vous pouvez procéder comme suit:

 select @@GLOBAL.innodb_tmpdir; 

Gardez à l'esprit que la taille du répertoire temporaire peut également être nécessaire de la taille d'une table + index. En général, faites le plein d'espace.

Afin de ne pas répéter la documentation, lisez plus en détail à https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-space-requirements.html

Mais comment faire? En fait, il n'y a pas de recette unique pour toutes les occasions.

L'une des options possibles, comme nous le faisons pour les tableaux qui ne sont pas critiques pour la mise à jour:

• Créer une nouvelle table avec la structure souhaitée
• Remplissez les champs de l'ancienne table
• Supprimer ou renommer une ancienne table
• Renommer le nouveau

Je répète que cela fonctionne pour les tables de mise à jour non critiques. Et en même temps, évite le blocage de la réplication. Il convient de garder à l'esprit que le remplissage d'une nouvelle table doit être effectué de manière à permettre la réplication de continuer, et comme il s'exécute séquentiellement, vous ne pouvez pas le faire avec une seule expression SQL, vous devez la diviser en plusieurs petites requêtes entre lesquelles la réplication d'autres données aura lieu. Dans d'autres cas, d'autres options sont possibles, peut-être que quelqu'un partagera les commentaires.

UPD Syavadee a suggéré d'utiliser le changement de schéma en ligne percona. En fait, il implémente l'algorithme décrit ci-dessus avec des goodies supplémentaires.

UPD Arheops recommande d'activer la réplication parallèle / gtid pour résoudre les problèmes de réplication.

Eh bien, accessoirement, parfois, afin de comprendre la taille de la table et le nombre de lignes qu'elle contient, vous devez apprendre à enseigner

 select count(*) from table_name 

Mais sur les tables volumineuses et chargées, ce n'est pas non plus l'opération la plus rapide, surtout lorsque vous avez un demi-million de lignes ou plus.

Par conséquent, pour une estimation approximative du volume, vous pouvez utiliser la méthode suivante:

 SHOW TABLE STATUS FROM express where name='table_name' 

Malheureusement, sur le moteur InnoDB, la taille résultante peut différer de 50% (dans notre cas, avec le tableau ci-dessus, le nombre réel d'enregistrements est d'environ 7,5 millions, et cette méthode n'a montré que 5 millions), mais cela est tout à fait approprié pour une estimation indicative.

C'est tout, j'espère que cette note aide quelqu'un à éviter de gros problèmes avec des commandes SQL soi-disant inoffensives.