Optimisation de MySQL pour modifier la TABLE D'InnoDB

nous aurons bientôt besoin d'apporter des changements de schéma à notre base de données de production. Nous devons minimiser les temps d'arrêt pour cet effort, cependant, les déclarations ALTER TABLE vont courir pendant un certain temps. Nos plus grandes tables ont 150 millions de disques, le plus grand fichier de table est de 50G. Toutes les tables sont InnoDB, et il a été mis en place comme un seul fichier big data (au lieu d'un fichier-par-table). Nous exécutons MySQL 5.0.46 sur une machine 8 core, Mémoire 16G et une configuration RAID10.

j'en ai expérience avec MySQL tuning, mais cela se concentre habituellement sur les lectures ou les Écritures de clients multiples. Il y a beaucoup d'information à trouver sur Internet à ce sujet, mais il semble y avoir très peu d'information disponible sur les meilleures pratiques pour (Temporairement) régler votre serveur MySQL pour accélérer ALTER TABLE sur les tables InnoDB, ou pour insérer dans .. Sélectionnez de (nous allons probablement utiliser ce à la place de ALTER TABLE pour avoir plus d'occasions d'accélérer les choses un peu).

Les changements de schéma que nous prévoyons faire est d'ajouter une colonne entière à toutes les tables et en faire la clé primaire, au lieu de la clé primaire actuelle. Nous devons également conserver la colonne "ancienne" pour ne pas surcharger les valeurs existantes.

quel serait le cadre idéal pour effectuer cette tâche le plus rapidement possible?

28
demandé sur schuilr 2009-03-17 17:56:59

6 réponses

Vous devez penser à vos exigences un peu plus attentivement.

au niveau le plus simple, le moyen le plus" rapide "d'obtenir la table changée est de le faire en aussi peu de ALTER TABLE énoncés que possible, de préférence un. C'est parce que MySQL copie les données d'une table pour changer le schéma et faire quinze changements alors que faire une seule copie est évidemment (et est vraiment) plus rapide que Copier la table quinze fois, faisant un changement à la fois.

mais je soupçonne que vous demandez comment faire ce changement avec le moins de temps d'arrêt. La façon dont je le ferais, vous synthétisez fondamentalement la façon dont un non-bloc ALTER TABLE fonctionnerait. Mais il a quelques exigences supplémentaires:

  1. vous avez besoin d'un moyen pour suivre les données ajoutées et modifiées, comme avec un champ de date "modifié" pour ce dernier, ou un champ AUTO_INCREMENT pour le premier.
  2. vous avez besoin d'espace pour avoir deux copies de votre table sur la base de données.
  3. vous avez besoin d'un délai où les modifications de la table ne sera pas trop loin d'un instantané

la technique de base est celle que vous avez suggérée, c'est-à-dire l'utilisation d'un INSERT INTO ... SELECT ... . Au moins tu es en tête parce que tu commences avec une table InnoDB, donc le SELECT ne bloquera pas. Je recommande de faire le ALTER TABLE sur la nouvelle table vide, ce qui sauvera MySQL copier toutes les données à nouveau, ce qui signifie que vous devez énumérer tous les champs correctement dans le INSERT INTO ... SELECT ... . Ensuite, vous pouvez faire une simple déclaration RENAME pour l'échanger. Ensuite, vous devez 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 devez faire les INSERT et UPDATE rapidement ou votre code commencera à ajouter de nouvelles lignes et mises à jour à votre snapshot qui interférera avec votre mise à jour. (Vous n'aurez pas ce problème si vous pouvez mettre votre application en mode maintenance pour quelques minutes d'avant la RENAME .)

en dehors de cela, il y a quelques paramètres liés à la clé et au buffer que vous pouvez modifier pour une seule session qui peut aider le déplacement de données principal. Des choses comme read_rnd_buffer_size et read_buffer_size seraient utiles pour augmenter.

15
répondu staticsan 2009-03-18 05:38:22

vous pourriez vouloir regarder pt-online-schema-change de Percona toolkit. Essentiellement ce qu'il fait est:

  • Copies structure originale de la table, court de MODIFIER.
  • copie les lignes de l'ancienne table à la nouvelle.
  • utilise des déclencheurs pour suivre et synchroniser les changements pendant la copie.
  • quand tout est terminé, il échange les tables en renommant les deux.

fonctionne très bien pour les bases de données à instance simple, mais pourrait être assez délicat si vous utilisez la réplication et vous ne pouvez pas vous permettre d'arrêter les esclaves et de les reconstruire plus tard.

il y a aussi un webinaire sympa sur ce ici .

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

15
répondu Tadas Sasnauskas 2012-09-04 10:53:22
  1. Configuration de l'esclave
  2. Arrêter la réplication.
  3. Faire MODIFIER sur l'esclave
  4. Laissez esclave rattraper le maître
  5. maître de swap et esclave, donc esclave devient serveur de production avec une structure modifiée et un temps d'arrêt minimum
12
répondu noonex 2009-10-06 12:57:56

malheureusement, ce n'est pas toujours aussi simple que staticsan mène sur dans sa réponse. Créer la nouvelle table en ligne et déplacer les données est assez facile, et faire le nettoyage en mode maintenance est aussi assez faisable, cependant, L'opération de renommage Mysql manipule automatiquement toutes les références de clé étrangère à votre ancienne table. Ce que cela signifie est que toute référence à clé étrangère à la table d'origine sera toujours pointer vers ce que vous renommez le table de.

donc, si vous avez des références de clé étrangère à la table que vous essayez de modifier vous êtes coincé soit modifier ces tables pour remplacer la référence à votre nouvelle table, ou pire si cette table est grande, vous devez répéter le processus avec la grande table numéro deux.

une autre approche qui a fonctionné pour nous dans le passé a été de jongler avec un ensemble de répliques Mysql manipulant l'alter. Je ne suis pas la meilleure personne pour parler du processus, mais il il s'agit essentiellement de casser la réplication à un seul esclave, d'exécuter le patch sur cette instance, de remettre la réplication en marche une fois que la table d'alter est terminée de sorte qu'elle rattrape la réplication. Une fois la réplication terminée, vous mettez le site en mode maintenance (si nécessaire) pour passer de votre maître à ce nouvel esclave patché en tant que nouvelle base de données maître.

la seule chose dont je ne me souviens pas, c'est quand vous pointez les autres esclaves vers le nouveau maître pour qu'ils bénéficiez également de modifier appliquée. Une mise en garde à ce processus, nous l'utilisons généralement pour lancer les correctifs alter avant que le code ait besoin du changement, ou après que le code a changé pour ne plus faire référence aux colonnes/touches.

11
répondu RC. 2017-05-23 11:46:18

j'ai testé diverses stratégies pour accélérer un alter table. Finalement j'ai eu une augmentation de vitesse d'environ 10x dans mon cas particulier. Les résultats peuvent ou non s'appliquer à votre situation. Cependant, sur la base de ceci, je suggérerais d'expérimenter avec les paramètres de taille du fichier log/tampon InnoDB.

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

basé sur le débit de données approximatives (iostat) et l'activité cpu le goulot d'étranglement était basé sur io, mais pas le débit de données. Dans les 500s plus rapides, le débit d'écriture est au moins du même ordre que celui que vous attendez 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 (déplacement de fichier) expliquée dans le lien a bien fonctionné 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 fichiers journaux plus grands est le temps de récupération plus long après le crash.

essais et ébauches:

  • les données de charge simple à une fraîcheur createad table: 6500s
  • load data 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
  • load data W. innodb_log_file_size=200M, innodb_log_buffer_size=8M: 500s
  • Equivalent straight alter table W. dataainnodb_log_file_size=200M, innodb_log_buffer_size =8M: 500s

test 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 la ligne 218 octets. Serveur: Ubuntu 12.04, x86_64, machine virtuelle, 8 cœurs, 16 Go, sata disque de qualité consommateur, pas de raid, pas d'activité de base de données, minuscule autre activité de processus, minuscule activité dans d'autres et beaucoup plus petites machines virtuelles. Mysql 5.1.53. La configuration initiale du serveur est assez par défaut sauf pour les innodb_buffer_pool_size of 1400M. La table alter ajoute 2 petites colonnes. Je n'ai pas chronométré la table d'alter brute, mais plutôt expérimenté avec des données de charge équivalente déclaration infile, enfin j'ai fait la table d'alter droite et obtenu un résultat comparable.

cette question est liée au moins aux questions suivantes:

5
répondu Peter Lamberg 2017-05-23 12:02:05

Je ne sais vraiment pas comment optimiser cela, mais il est généralement une bonne pratique de mettre le site en mode hors ligne avant de faire de telles mises à jour.

ensuite, vous pouvez exécuter vos scripts DB à, disons, 3 heures du matin, donc cela ne devrait pas avoir d'importance si le temps d'arrêt est beaucoup plus long qu'idéal.

-4
répondu Seb 2009-03-17 15:01:33