L'optimisation de MySQL pour ALTER TABLE de InnoDB

Très bientôt, nous aurons 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?

InformationsquelleAutor schuilr | 2009-03-17
  1. 15

    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 aussi peu de ALTER TABLE états 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 le SELECT ne bloquera pas. Je recommande de faire le 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 le 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 le INSERT et UPDATE rapidement ou votre code de départ de l'ajout de nouvelles lignes et de mises à jour de votre instantané qui sera 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 la 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.

    InformationsquelleAutor staticsan
  2. 15

    Vous voudrez peut-être regarder à pt-online-schéma de changement de Percona toolkit. Essentiellement, ce qu'il fait est:

    • Copies structure originale de la table, court de MODIFIER.
    • Copie des lignes de vieille table nouvellement créé un.
    • 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.

    • Une note de la pt-online-schéma de changement docs: L'utilisation de déclencheurs signifie que l'outil ne fonctionnera pas si tous les déclencheurs sont déjà définies sur la table.
    InformationsquelleAutor Tadas Sasnauskas
  3. 12
    1. De configuration de l'esclave
    2. Arrêter la réplication.
    3. Faire MODIFIER sur l'esclave
    4. Laisser esclave rattraper le maître
    5. swap maître et de l'esclave, de sorte que l'esclave devient serveur de production avec changement de structure et des temps d'arrêt minimum
    InformationsquelleAutor noonex
  4. 11

    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.

    • Je n'avais pas pensé à des clés étrangères. Où j'ai été un DBA où j'ai utilisé la technique que j'ai décrite, nous n'avons pas utilisé les clés étrangères que la demande cru et seulement s'occupait de tout cela.
    InformationsquelleAutor RC.
  5. 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:

    Changer innodb_log_file_size peut être dangereux. Voir http://www.mysqlperformanceblog.com/2011/07/09/how-to-change-innodb_log_file_size-safely/ La 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:

    • La simple charger des 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

    Test de détails: Table: 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:

    • Fantastique réponse, cela mérite beaucoup plus de voix. Je vous remercie pour la publication de vos recherches et de résultats.
    InformationsquelleAutor Peter Lamberg
  6. -4

    Je ne sais vraiment pas comment faire pour optimiser, mais c'est généralement une bonne pratique de mettre le site en mode hors-ligne avant de faire ces mises à jour.

    Ensuite, vous pouvez exécuter votre base de données de scripts, disons, à 3 heures du matin, donc ça ne devrait pas beaucoup d'importance si le temps d'arrêt est un gros plus de l'idéal.

    • Oui, nous allons faire tomber le site durant la nuit heures à faire cette tâche. Mais avec ces grands tableaux de l'instruction ALTER TABLE peut durer de nombreuses heures, et ce n'est probablement pas tenir sur une seule nuit 🙁
    • Peut-être que vous voulez exécuter le script dans un serveur séparé avec un dump de la base de données de production timing combien il prend. J'espère que vous aurez une belle surprise! 🙂
    • Oui, nous préparons un environnement d'assurance qualité pour cela. Ne peut pas optimiser sans tester vos optimisations 😉
    InformationsquelleAutor Seb