ALTER TABLE sans verrouillage de la table?
Lors d'une instruction ALTER TABLE dans MySQL, l'ensemble du tableau est verrouillé en lecture pour la durée de l'instruction. Si c'est un grand tableau, cela signifie que d'insérer ou de mettre à jour les déclarations peuvent être verrouillés pour un looooong moment. Est-il un moyen de faire un "alter", comme l'ajout d'une colonne dans une telle manière que la table est toujours mis à jour tout au long du processus?
Surtout je suis intéressé par une solution de MySQL, mais je serais intéressé par d'autres SGBDR si MySQL ne peut pas le faire.
Pour préciser, mon but est simplement d'éviter les temps d'arrêt lorsqu'une nouvelle fonctionnalité qui nécessite une colonne de la table est poussé à la production. Tout schéma de base de données sera changer au fil du temps, c'est juste un fait de vie. Je ne vois pas pourquoi nous devrions accepter que ces changements doivent inévitablement entraîner des temps d'arrêt; c'est tout simplement faible.
- Me demande combien de fois vous serez en modifiant la table?
- À mon humble avis, schéma de base de données changements sont liés à l'ensemble des nouvelles versions - ils n'obtiennent pas roulé de façon sporadique, à l'instar d'autres changements. C'est forcément une grosse affaire.
- plus de 0 fois fait cette question est légitime, surtout si le temps d'arrêt de votre système de coût de la vie ou beaucoup d'argent. Et dans tous les cas, les nouvelles exigences en matière de logiciel ne montrent parfois.
Vous devez vous connecter pour publier un commentaire.
La seule autre option est de faire manuellement ce que de nombreux systèmes SGBDR faire de toute façon...
- Créer une nouvelle table
Vous pouvez ensuite copier le contenu de l'ancienne table sur un morceau à la fois. Tout en étant toujours prudent de toutes les INSERT/UPDATE/DELETE sur la table source. (Peut être géré par un déclencheur. Bien que ce serait la cause d'un ralentissement, ce n'est pas une serrure...)
Une fois terminé, changez le nom de la table source, puis modifier le nom de la nouvelle table. De préférence dans une transaction.
Une fois terminé, recompiler toutes les procédures stockées, etc qui utilisent cette table. Les plans d'exécution seront probablement de ne plus être valide.
EDIT:
Certains commentaires ont été faits sur cette limitation étant un peu pauvre. Donc, j'ai pensé mettre une nouvelle perspective sur les choses à montrer pourquoi il est comme ça...
Percona fait un outil appelé pt-online-schéma de changement qui permet de le faire.
Il en fait essentiellement une copie de la table et modifie la nouvelle table. Pour garder la nouvelle table en synchronisation avec l'original, il utilise des déclencheurs pour la mise à jour. Cela permet à la table d'origine pour être accessible, tandis que la nouvelle table est préparée dans l'arrière-plan.
Ceci est similaire à Dems méthode proposée ci-dessus, mais ce fait de manière automatisée.
Certains de leurs outils ont une courbe d'apprentissage, à savoir la connexion à la base de données, mais une fois que vous avez cela, ils sont d'excellents outils pour avoir.
Ex:
Cette question à partir de 2009. Maintenant, MySQL propose une solution:
en ligne DDL
Il vous permet d'ajuster l'équilibre entre la performance et de la concurrence au cours de l'opération DDL, de par le choix que de bloquer l'accès à la table entièrement (LOCK=clause d'EXCLUSIVITÉ), autoriser les requêtes, mais pas DML (LOCK=PARTAGÉE de la clause), ou de permettre requête complète et DML l'accès à la table (LOCK=AUCUNE clause). Si vous omettez la clause de VERROUILLAGE ou de spécifier LOCK=par DÉFAUT, MySQL permet la simultanéité possible en fonction du type d'opération.
D'effectuer des changements en place, si possible, plutôt que de créer une nouvelle copie de la table, évite les hausses temporaires d'utilisation de l'espace disque et I/O frais généraux associés à la copie de la table et de la reconstruction des index secondaires.
voir MySQL 5.6 Manuel de Référence -> InnoDB en Ligne et DDL pour plus d'info.
Il semble qu'en ligne DDL également disponible dans MariaDB
MariaDB KO sur ALTER TABLE
Voir Facebook en ligne du schéma de changement de l'outil.
http://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932
Pas pour les faibles de cœur, mais il va faire le travail.
Je recommande Postgres si c'est une option. Avec postgres il n'y a pratiquement aucun temps d'arrêt avec les procédures suivantes:
Autre grande caractéristique est que la plupart des instructions DDL sont transactionnelles, de sorte que vous pourrait faire toute une migration à l'intérieur d'une transaction SQL, et si quelque chose va mal, toute la chose est annulée.
J'ai écrit cette un peu tôt, peut-être qu'il peut faire plus de perspicacité sur le bien-fondé.
DROP COLUMN
commandes, et si oui, sont-ils aussi peu près aussi vite qu'un complément, ou ont-ils besoin de "toucher" à chaque ligne?Depuis que vous avez demandé sur d'autres bases de données, voici quelques informations sur Oracle.
L'ajout d'une colonne NULL à une table Oracle est un moyen très rapide d'utilisation, comme cela ne met à jour le dictionnaire de données. Ceci est un verrou exclusif sur la table pour une très courte période de temps. Il sera toutefois invalider toute depedant procédures stockées, vues, triggers, etc. Ces obtiendrez de recompiler automatiquement.
Partir de là, si nécessaire, vous pouvez créer des index de l'aide en LIGNE de la clause. Encore une fois, très peu de données dictionnaire de serrures. Il va lire l'ensemble de la table à la recherche de choses à l'index, mais ne bloque pas la personne tout en faisant cela.
Si vous devez ajouter une clé étrangère, vous pouvez le faire et obtenir de l'Oracle de confiance vous que les données sont correctes. Sinon, il faut lire l'ensemble de la table et de valider toutes les valeurs qui peut être lent (créer votre index en premier).
Si vous avez besoin de mettre une valeur par défaut ou la valeur calculée dans chaque ligne de la nouvelle colonne, vous aurez besoin de lancer une mise à jour massive ou peut-être un petit programme utilitaire qui remplit les nouvelles données. Cela peut être long, surtout si les lignes d'obtenir beaucoup plus grand et ne rentrent plus dans leurs blocs. Le verrouillage peut être géré au cours de ce processus. Depuis l'ancien versino de votre application, qui est toujours en cours d'exécution, ne savent pas à propos de cette colonne, vous pourriez avoir besoin d'un sournois de déclenchement ou de spécifier une valeur par défaut.
À partir de là, vous pouvez faire un switcharoo sur vos serveurs d'application à la nouvelle version du code, et il va continuer à courir. Déposer vos sournois déclencheur.
Alternativement, vous pouvez utiliser DBMS_REDEFINITION qui est une boîte noire conçu pour faire ce genre de chose.
Tout cela est tellement la peine de tester, etc que nous avons juste un dimanche matin panne à chaque fois que la sortie d'une version majeure.
Si vous ne pouvez pas vous permettre un temps d'arrêt pour votre base de données lorsque vous faites des mises à jour d'application, vous devriez envisager de maintenir un cluster à deux nœuds pour la haute disponibilité. Avec une simple configuration de réplication, vous pouvez faire presque entièrement en ligne les changements structurels comme l'un de vous suggérer:
Il n'est pas toujours facile, mais il fonctionne, généralement avec 0 les temps d'arrêt! Le deuxième nœud ne doit pas être seulement passif, il peut être utilisé pour tester, faire des statistiques ou comme un nœud de secours.
Si vous n'avez pas l'infrastructure de réplication peut être mis en place au sein d'une seule machine (avec deux instances de MySQL).
Nope. Si vous utilisez les tables MyISAM, à mon meilleur compréhension qu'ils viennent de faire les verrous de table - il n'y a pas d'enregistrer les verrous, ils ont juste essayer de garder tout hyperfast à travers la simplicité. (D'autres tables MySQL fonctionnent différemment.) Dans tous les cas, vous pouvez copier la table à une autre, de le modifier, puis de les transférer, mettre à jour des différences.
C'est d'une telle modification que je doute qu'un SGBD le soutiendra. Il est considéré comme un avantage à être en mesure de le faire avec les données de la table dans la première place.
Solution temporaire...
Autre solution pourrait être d'ajouter une autre table avec une clé primaire de la table d'origine, le long de avec votre nouvelle colonne.
Remplir votre clé primaire sur la table et la remplir de valeurs pour la nouvelle colonne dans votre nouvelle table, et de modifier la requête pour joindre ce tableau pour sélectionner les opérations et vous avez aussi besoin d'insérer, mettre à jour séparément pour cette valeur de la colonne.
Lorsque vous en mesure d'obtenir les temps d'arrêt, vous pouvez modifier la table d'origine, de modifier vos requêtes DML et déposez votre nouvelle table créée précédemment
D'autre, vous pouvez aller pour la méthode de clustering, la réplication, pt-online-schéma de l'outil de percona
À l'aide de la Innodb plugin, instructions ALTER TABLE qui seulement ajouter ou supprimer des index secondaires peut être fait "rapidement", c'est à dire sans la reconstruction de la table.
En général, MySQL, tout ALTER TABLE implique la reconstruction de l'ensemble de la table, ce qui peut prendre un temps très long (c'est à dire si la table a une quantité appréciable de données).
Vous avez vraiment besoin pour la conception de votre application, de sorte que les instructions ALTER TABLE ne doivent pas être effectués régulièrement; vous ne voulez certainement pas à tout ALTER TABLE fait pendant le fonctionnement normal de l'application, sauf si vous êtes prêts à attendre, ou vous êtes modifier minuscules tables.
Je recommanderais l'un des deux approches:
La conception de vos tables de base de données avec les changements potentiels dans l'esprit. Par exemple, j'ai travaillé avec des Systèmes de Gestion de Contenu, qui fait changer les champs de données dans le contenu régulièrement. Au lieu de construire la physique de la structure de base de données pour correspondre à l'initiale de la CMS besoins du terrain, il est beaucoup mieux de construire dans une structure souple. Dans ce cas, à l'aide d'une goutte de champ de texte (de type varchar(max) par exemple), pour tenir flexible de données XML. Cela fait des changements structurels très de moins en moins fréquentes. Les changements structurels peuvent être coûteux, il a un avantage de coût ici.
Avoir un système de temps de maintenance. Le système passe en mode hors connexion lors de changements (mensuelle, etc), et les changements sont prévus au cours de la moins achalandée moment de la journée (3-5h du matin, par exemple). Les changements sont mis en scène avant la production de déploiement, de sorte que vous aurez une bonne fenêtre fixe estimation de temps d'arrêt.
2a. Avoir des serveurs redondants, de sorte que lorsque le système a des temps d'arrêt, l'ensemble du site ne descend pas. Cela vous permettra de "rouleau" de vos mises à jour de façon échelonnée, sans prendre l'ensemble du site vers le bas.
Les Options 2 et 2a peut ne pas être possible; ils ont tendance à être seulement pour les sites de grande taille/de l'exploitation. Ils sont valables options, cependant, et j'ai personnellement utilisé toutes les options présentées ici.
Si quelqu'un est encore la lecture de ce ou qui arrive de venir ici, c'est le grand avantage de l'utilisation d'un système de base de données NoSQL comme mongodb. J'ai eu la même question lors de la modification de la table pour ajouter des colonnes pour des fonctionnalités supplémentaires ou des index sur une grande table avec des millions de lignes et de haute écrit. Ça finirait de verrouillage pour un temps très long donc faire sur le LIVE de la base de données seraient contraires à nos utilisateurs. Sur les petites tables que vous pouvez sortir avec elle.
Je déteste le fait que nous avons à "la conception de nos tables pour éviter de les modifier". Je ne pense pas que fonctionne aujourd'hui dans le site web du monde. Vous ne pouvez pas prédire comment les gens vont utiliser votre logiciel, c'est pourquoi vous de changer rapidement les choses en se basant sur les commentaires des utilisateurs. Avec mongodb, vous pouvez ajouter des "colonnes" à volonté avec des pas de temps d'arrêt. Vous n'avez pas vraiment à même de les ajouter, il vous suffit d'insérer des données avec de nouvelles colonnes et il le fait automatiquement.
La peine de vérifier: http://www.mongodb.com
En général, la réponse sera "Non". Vous êtes à la modification de la structure de la table qui, potentiellement, il faudra beaucoup de mises à jour" et je suis absolument d'accord avec ça. Si vous vous attendez à le faire souvent, alors je vais vous proposer une alternative à la "dummy" colonnes - utilisation
VIEW
s au lieu de tables pourSELECT
ing données. IIRC, la modification de la définition d'un point de vue est relativement léger et l'indirection par l'intermédiaire d'un point de vue est effectuée lorsque le plan de requête est compilé. La dépense est que vous devez ajouter la colonne à une nouvelle table et de rendre la vueJOIN
dans la colonne.Bien sûr, cela ne fonctionne que si vous pouvez utiliser des touches pour effectuer une cascade de suppressions et autres joyeusetés. L'autre bonus est que vous pouvez créer une nouvelle table contenant une combinaison de données et de point de vue à elle sans perturber l'utilisation du client.
Juste une pensée.
La différence entre Postgres et MySQL à cet égard est que, dans Postgres il n'a pas re-crée un tableau, mais modifie dictionnaire de données qui est similaire à Oracle. Par conséquent, l'opération est rapide, bien que cela nécessite d'allouer une exclusivité DDL verrou de table pour le temps très court comme indiqué ci-dessus par d'autres.
Dans MySQL l'opération de copier des données dans une nouvelle table tout en bloquant les transactions, ce qui a été principale de la douleur pour les Administrateurs de base de données MySQL avant de v. 5.6.
La bonne nouvelle, c'est que depuis MySQL 5.6 libération de la restriction a été la plupart levée et vous pouvez maintenant profiter de la vraie puissance de la DB MYSQL.
Comme SeanDowney a mentionné,
pt-online-schema-change
est l'un des meilleurs outils pour faire ce que vous avez décrit dans la question ici. Récemment, j'ai fait beaucoup de modifications de schéma sur un live DB et il est allé très bien. Vous pouvez en lire plus à ce sujet sur mon blog ici: http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/.Vous devriez certainement essayer
pt-online-schema-change
. J'ai été en utilisant cet outil pour faire des migrations sur AWS RDS avec plusieurs esclaves et il a très bien fonctionné pour moi. J'ai écrit un complexe de blog sur la façon de faire ce qui pourrait être utile pour vous.Blog: http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/
Les colonnes factices sont une bonne idée si vous pouvez prédire leur type (et les faire accepter les valeurs null). De vérifier la façon dont votre moteur de stockage gère les valeurs null.
MyISAM verrouille tout, si vous même mentionner un nom de table, en passant, sur le téléphone, à l'aéroport. Il n'a tout simplement que...
Cela étant dit, les verrous ne sont pas vraiment un gros problème, tant que vous n'essayez pas d'ajouter une valeur par défaut pour la nouvelle colonne pour chaque ligne, mais laissez reposer nulle, et votre moteur de stockage est assez intelligent pour ne pas aller à l'écrire, vous devriez être ok avec une serrure qui est seulement tenu assez longtemps pour mettre à jour les métadonnées. Si vous essayez d'écrire une nouvelle valeur, eh bien, vous êtes grillé.
TokuDB pouvez ajouter/supprimer des colonnes et ajouter des index "chaud", la table est disponible dans tout le processus. Il est disponible via http://www.tokutek.com
Pas vraiment.
Vous SONT en train de modifier la structure sous-jacente de la table, après tout, et c'est un bit d'information qui est très important pour le système sous-jacent. Vous êtes aussi (probablement) se déplaçant beaucoup de données sur le disque.
Si vous prévoyez de le faire beaucoup, vous êtes mieux de simplement remplir la table avec "dummy" colonnes qui sont disponibles pour une utilisation future.