Optimisation de MySQL pour ALTER TABLE of InnoDB

Question

Prochainement nous allons avoir besoin de faire des modifications de schéma à notre base de données de production. Nous avons besoin de minimiser les temps d'arrêt pour cet effort, cependant, les instructions ALTER TABLE vont courir pendant un certain temps. Nos plus grandes tables de 150 millions d'albums, le plus grand fichier de la table est de 50G. Toutes les tables sont en InnoDB, et il a été mis en place comme un gros fichier de données (au lieu d'un fichier par table). Nous sommes en cours d'exécution MySQL 5.0.46 sur un 8 core de la machine, 16G de mémoire et un RAID10 config.

J'ai une certaine expérience avec MySQL tuning, mais c'est généralement axé sur le lit ou écrit à partir de plusieurs clients. Il y a beaucoup d'info pour être trouvé sur Internet à ce sujet, cependant, il semble y avoir très peu d'informations sur les meilleures pratiques pour (temporairement) le paramétrage de votre serveur MySQL pour accélérer l'instruction ALTER TABLE sur les tables InnoDB, ou pour l'INSERTION DANS .. SÉLECTIONNER (nous allons probablement utiliser ce lieu de l'instruction ALTER TABLE pour avoir un peu plus de possibilités pour accélérer un peu les choses).

Le schéma changements que nous prévoyons de faire est d'ajouter une colonne de type entier à toutes les tables et faire la clé primaire, au lieu de l'actuelle clé primaire. Nous avons besoin de garder les "anciens" de la colonne afin d'écraser les valeurs existantes n'est pas une option.

Ce serait l'endroit idéal pour obtenir cette tâche fait aussi vite que possible?

Answer 1

Vous devez penser à vos exigences un peu plus attentivement.

Au niveau le plus simple, le "plus rapide" de façon à obtenir la table changé, c'est de le faire en quelques ALTER TABLE des déclarations que possible, de préférence. C'est parce que MySQL copies des données d'une table à modifier le schéma et faire quinze évolue, alors que faire une seule copie est évidemment (et vraiment) plus rapide que la copie de la table de la quinzaine de fois, en faisant un changement à la fois.

Mais je suppose que vous vous demandez comment faire ce changement avec le moins de temps d'arrêt. La façon dont je voudrais faire, vous avez essentiellement de synthétiser de la façon dont un non-bloc ALTER TABLE serait de travailler. Mais il a des exigences supplémentaires:

1. vous avez besoin d'un moyen de suivre ajoutés et modifiés de données, comme avec un "modified" champ de date pour le dernier, ou un AUTO_INCREMENT champ pour l'ancien.
2. vous avez besoin d'espace pour avoir deux copies de votre table sur la base de données.
3. vous avez besoin d'une période de temps où les modifications de la table ne soit pas trop loin d'un instantané

La technique de base est comme vous l'avez suggéré, c'est à dire à l'aide d'un INSERT INTO ... SELECT .... Au moins vous êtes en face parce que vous êtes débutant avec une table InnoDB, de sorte que l' SELECT ne bloquera pas. Je recommande de faire de l' ALTER TABLE sur le nouveau tableau vide, ce qui permettra d'économiser MySQL copie de toutes les données à nouveau, ce qui signifie que vous devez dresser une liste de tous les champs correctement dans l' INSERT INTO ... SELECT ... déclaration. Ensuite, vous pouvez faire un simple RENAME déclaration de swap sur. Ensuite, vous avez besoin de faire un autre INSERT INTO ... SELECT ... WHERE ... et peut-être un UPDATE ... INNER JOIN ... WHERE ... pour récupérer toutes les données modifiées. Vous avez besoin de faire l' INSERT et UPDATE rapidement ou votre code de départ de l'ajout de nouvelles lignes et de mises à jour de votre capture d'écran qui va interférer avec votre mise à jour. (Vous n'aurez pas ce problème si vous pouvez mettre votre application en mode maintenance pour quelques minutes avant de l' RENAME.)

En dehors de cela, il ya quelques clés et tampon liées paramètres que vous pouvez modifier pour une séance qui peut aider les principales données se déplacer. Des choses comme read_rnd_buffer_size et read_buffer_size serait utile d'augmenter.

Answer 2

Vous regardez le pt-online-schéma de changement de Percona toolkit. Essentiellement, ce qu'il fait est:

• Des Copies structure originale de la table, court de MODIFIER.
• Copie des lignes de vieille table à nouveau.
• Utilise des déclencheurs de suivre et de synchroniser les modifications lors de la copie.
• Quand tout est terminé, il swaps de tables en renommant les deux.

Fonctionne très bien pour l'unique instance de bases de données, mais peut être très difficile si vous utilisez la réplication et vous ne pouvez pas se permettre l'arrêt des esclaves et de les reconstruire plus tard.

Il y a aussi une belle webinaire sur ce ici.

PS: je sais que c'est une vieille question, juste pour répondre au cas où quelqu'un frappe ce via le moteur de recherche.

Answer 3

1. Configuration de l'esclave
2. Arrêtez la réplication.
3. Faire ALTER sur esclave
4. Laissez l'esclave rattraper le maître
5. swap master et slave, afin que l'esclave devienne un serveur de production avec une structure modifiée et un temps d'arrêt minimum

Answer 4

Malheureusement, ce n'est pas toujours aussi simple que staticsan conduit dans sa réponse. La création de la nouvelle table en ligne, et de déplacer les données de plus est assez facile, et de faire un nettoyage en mode maintenance est également faisable assez, cependant, la base de RENOMMER automatiquement la manipule d'une clé étrangère des références à votre vieille table. Ce que cela signifie, c'est que d'une clé étrangère références à la table d'origine sera toujours le point à ce que vous renommez la table.

Donc, si vous avez une clé étrangère références à la table que vous essayez de modifier vous êtes coincé, soit en changeant les tables de remplacer la référence à votre nouvelle table, ou pire si la table est grande, vous devrez répéter le processus avec une grande table numéro deux.

Une autre approche qui a travaillé pour nous dans le passé a été de jongler avec un ensemble de Mysql répliques de la manipulation de la modifier. Je ne suis pas la meilleure personne pour parler du processus, mais il se compose essentiellement de la rupture de la réplication d'un esclave, l'exécution du patch sur un exemple, activer la réplication sur le dos une fois de l'instruction alter table est terminée afin que celui-ci se rattrape sur la réplication. Une fois la réplication rattrape, vous mettre le site en mode maintenance (si nécessaire) pour basculer de votre maître pour cette nouvelle corrigé de l'esclave comme le nouveau maître de la base de données.

La seule chose que je ne peux pas me rappeler exactement quand vous pointez les autres esclaves au nouveau maître, de sorte qu'ils ont aussi la modifier appliquée. Un inconvénient de ce processus, nous utilisons généralement ce rouleau de modifier les correctifs avant que le code a besoin de la changer, ou après le code a changé de ne plus faire référence aux colonnes/clés.

Answer 5

J'ai testé différentes stratégies pour accélérer un alter table. Finalement, j'ai obtenu environ 10x augmentation de la vitesse dans mon cas particulier. Les résultats peuvent ou peuvent ne pas s'appliquer à votre situation. Toutefois, en se fondant sur ce que je suggère d'expérimenter avec InnoDB fichier journal/taille de la mémoire tampon paramètres.

En bref, seul l'augmentation de innodb_log_file_size et innodb_log_buffer_size eu un effet mesurable (attention! Changer innodb_log_file_size est risqué. Regardez ci-dessous pour plus d'infos).

Basé sur le brut écrire des données de taux (iostat) et l'activité de l'uc le goulot d'étranglement a été io, mais pas le débit de données. Dans le plus rapide de 500 exécute l'écriture de débit est au moins au même stade que vous pouvez vous attendre à partir du disque dur.

Essayé de l'optimisation des performances:

• select * into outfile + load data infile, comme suggéré dans http://stackoverflow.com/a/5303805/1148030
• "désactiver les touches" j'ai raté le fait qu'il n'a pas d'effet en InnoDB, qui est également vrai sur la base des résultats. C'était dans tous les cas, sauf le dernier alter table. Comment faire pour désactiver l'index en innodb
• innodb_buffer_pool_size, "Disk I/O Conseils" à http://dev.mysql.com/doc/refman/5.1/en/innodb-tuning.html
• foreign_key_checks=0 et al, "en Vrac chargement des données des conseils" à http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html
• Plus innodb_log_file_size (Voir mises en garde ci-dessous) et innodb_log_buffer_size. Voir "enregistrement des trucs" à http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html et http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_log_file_size et http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_log_buffer_size

Changer innodb_log_file_size peut être dangereux. Voir http://www.mysqlperformanceblog.com/2011/07/09/how-to-change-innodb_log_file_size-safely/ Les technique (fichier) expliqué dans le lien travaillent bien dans mon cas.

Voir aussi http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/ et http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/ pour plus d'informations sur innodb et le réglage de la taille des fichiers journaux. Un inconvénient des gros fichiers journaux est plus le temps de récupération après un crash.

Essais et bruts horaires:

• Le simple chargement de données à un fraîchement créerun table: 6500s
• charger des données w. innodb_log_file_size=200M, innodb_log_buffer_size=8M, innodb_buffer_pool_size=2200M, autocommit= 0; unique_checks=0, foreign_key_checks=0: 500s
• charger des données w. innodb_log_file_size=200M, innodb_log_buffer_size=8M: 500s
• Équivalent droite alter table w. datainnodb_log_file_size=200M, innodb_log_buffer_size=8M: 500s

Les tests de détails: Tableau: InnoDB, 6M lignes, 2,8 G sur le disque, fichier unique (innodb_file_per_table option), la clé primaire est de 1 entier, +2 unque contraintes/indices, 8 colonnes, avg. longueur de ligne 218 octets. Serveur: Ubuntu 12.04, x86_64, machine virtuelle, 8 coeurs, 16 GO, sata grand public de disque, pas de raid, pas de base de données d'activité, minuscule autres processus de l'activité, minuscule activité dans d'autres et de plus petites machines virtuelles. Mysql 5.1.53. Les initiales de la configuration du serveur est assez défaut, sauf pour l'augmentation de la innodb_buffer_pool_size de 1400M. L'instruction alter table ajoute 2 petites colonnes. Je n'ai pas l'horloge de la crue de l'instruction alter table, mais plutôt expérimenté avec l'équivalent de load data infile déclaration, j'ai enfin le droit de modifier le tableau et a obtenu de résultat comparable.

Cette question est liée à au moins une des questions suivantes:

• MySQL: ajouter un champ à une table de grande taille
• Ajouter une nouvelle colonne de la grande table mysql
• mysql requête lente
• MySQL "set unique_checks", "set foreign_key_checks" vs "alter table désactiver les touches"
• L'optimisation de MySQL pour ALTER TABLE de InnoDB