Importation de données dans une instance de base de données MySQL - Amazon Relational Database Service
PrésentationConsidérations sur l'importation de données

Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.

Importation de données dans une instance de base de données MySQL

Vous pouvez utiliser plusieurs techniques pour importer des données dans une instance de base de données RDS for MySQL. La meilleure méthode dépend de la source des données, de la quantité de données et de savoir si l'importation est effectuée une seule fois ou en continu. Si vous migrez une application avec les données, tenez également compte du temps d'immobilisation que vous êtes prêt à accepter.

Présentation

Retrouvez dans le tableau suivant les techniques d'importation de données dans une instance de base de données RDS for MySQL.

Source Quantité de données Une seule fois ou en continu Interruption de l'application Technique En savoir plus
Base de données MySQL existante sur site ou sur Amazon EC2 N'importe quel compte Une seule fois Momentanée Créez une sauvegarde de votre base de données sur site, stockez-la sur Amazon S3, puis restituez le fichier de sauvegarde sur une nouvelle instance de base de données Amazon RDS exécutant MySQL. Restauration d'une sauvegarde dans une instance de base de données MySQL
Toute base de données existante N'importe quel compte Une seule fois ou en continu Minimale AWS Database Migration Service À utiliser pour migrer la base de données avec un temps d'arrêt minimal et, pour de nombreux moteurs de base de données, poursuivre la réplication continue. Présentation d' AWS Database Migration Service et Utilisation d'une base de données compatible MySQL comme cible pour AWS DMS dans le Guide de l'utilisateur AWS Database Migration Service
Instance de base de données MySQL existante N'importe quel compte Une seule fois ou en continu Minimale Créez un réplica en lecture pour la réplication continue. Promouvez le réplica en lecture pour la création unique d'une nouvelle instance de base de données. Utilisation des réplicas en lecture d'instance de base de données
Base de données MariaDB ou MySQL existante Petite Une seule fois Momentanée Copiez les données directement dans votre instance de base de données MySQL à l'aide d'un utilitaire de ligne de commande. Importation de données depuis une base de données externe MariaDB ou MySQL vers une instance de base de données RDS pour MariaDB ou RDS pour MySQL
Données non stockées dans une base de données existante Medium Une seule fois Momentanée Créez des fichiers plats et importez-les à l'aide LOAD DATA LOCAL INFILE des instructions MySQL. Importation de données depuis n'importe quelle source vers une instance de base de données MariaDB ou MySQL
Base de données MariaDB ou MySQL existante sur site ou sur Amazon EC2 N’importe quel compte En continu Minimale Configurez la réplication avec une base de données MariaDB ou MySQL existante comme source de réplication.

Configuration d'une réplication de position de fichier journal binaire avec une instance source externe

Importation de données vers une base de données MariaDB ou MySQL Amazon RDS avec un temps d'arrêt réduit
Note

La base de données système 'mysql' contient les informations d'authentification et d'autorisation requises pour se connecter à l'instance de base de données et accéder aux données. La suppression, la modification, le renommage ou la troncation de tables, de données ou d'autres contenus de la base de données 'mysql' de votre instance de base de données peut produire une erreur et rendre inaccessibles l'instance de base de données et vos données. Dans ce cas, vous pouvez restaurer l'instance de base de données à partir d'un instantané à l'aide de la AWS CLI restore-db-instance-from-db-snapshot commande. Vous pouvez récupérer l'instance de base de données à l'aide de la AWS CLI restore-db-instance-to-point-in-time commande.

Considérations sur l'importation de données

Voici des informations techniques supplémentaires sur le chargement de données dans MySQL. Elles sont destinées aux utilisateurs avancés qui connaissent bien l'architecture de serveur MySQL.

Journal binaire

Si la journalisation binaire est activée, les chargements de données s'exposent à des pertes de performance et nécessitent un espace disque supplémentaire (jusqu'à 4 fois plus grand), par comparaison avec le chargement des mêmes données lorsque la journalisation binaire est désactivée. La gravité des pertes de performance et la quantité d'espace disque disponible requis sont directement proportionnelles à la taille des transactions utilisées pour charger les données.

Taille de la transaction

La taille de la transaction joue un rôle important dans les chargements de données MySQL. Elle exerce une influence majeure sur la consommation des ressources, l'utilisation de l'espace disque, le processus de reprise, la durée de la récupération et le format d'entrée (fichiers plats ou SQL). Cette section décrit comment la taille de la transaction influe sur la journalisation binaire. En outre, elle plaide en faveur de la désactivation de cette même journalisation lors de chargements de données volumineux. Comme évoqué précédemment, la journalisation binaire est activée et désactivée selon la valeur attribuée à la période de rétention des sauvegardes automatiques Amazon RDS. Les valeurs différentes de zéro activent la journalisation binaire, tandis que la valeur zéro la désactive. Nous décrivons aussi l'impact des transactions volumineuses sur InnoDB et expliquons pourquoi il importe que les transactions conservent une petite taille.

Petites transactions

Pour les petites transactions, la journalisation binaire multiplie par deux le nombre d'écritures sur disque requises pour charger les données. Elle peut ainsi affecter gravement les performances des autres sessions de base de données et accroître le temps requis pour charger les données. La dégradation qu'elle provoque dépend en partie du taux de chargement, des autres activités de base données se déroulant en parallèle et de la capacité de l'instance de base de données Amazon RDS.

Les journaux binaires consomment aussi un espace disque approximativement égal à la quantité de données chargées jusqu'à leur sauvegarde et leur suppression. Heureusement, Amazon RDS réduit cette consommation en sauvegardant et en supprimant régulièrement les journaux binaires.

Transactions volumineuses

Les transactions volumineuses s'exposent à des pertes de performance 3 fois supérieures pour les IOPS et l'utilisation du disque quand la journalisation binaire est activée. En effet, le cache du journal binaire est alors écrit sur disque, ce qui entraîne une utilisation de l'espace disque et une opération d'I/O supplémentaire pour chaque écriture. Comme le cache ne peut pas être écrit sur le journal binaire tant que la transaction n'est pas validée ou annulée, il utilise de l'espace disque proportionnellement à la quantité de données chargée. Lorsque la transaction est validée, le cache doit être copié sur le journal binaire, ce qui crée une troisième copie des données sur disque.

Pour cette raison, il doit y avoir au moins trois fois plus d'espace disque disponible pour charger les données que lorsque la journalisation binaire est désactivée. Par exemple, 10 Gio de données chargées dans une même transaction utilisent au moins 30 Gio d'espace disque pendant le chargement. Cette transaction utilise 10 Gio pour la table + 10 Gio pour le cache du journal binaire + 10 Gio pour le journal binaire lui-même. Le fichier cache demeure sur le disque jusqu'à ce que la session qui l'a créé se termine ou que la session remplisse à nouveau le cache du journal binaire lors d'une autre transaction. Étant donné que le journal binaire demeure sur le disque jusqu'à la sauvegarde, la libération des 20 Gio supplémentaires peut prendre un certain temps.

Si les données ont été chargées à l'aide de LOAD DATA LOCAL INFILE, une autre copie des données est toutefois créée lorsque la base de données doit être récupérée à partir d'une sauvegarde exécutée avant le chargement. Pendant le récupération, MySQL extrait les données du journal binaire dans un fichier plat. MySQL exécute ensuite LOAD DATA LOCAL INFILE, exactement comme dans la transaction initiale. Cependant, le fichier d'entrée se trouve alors sur le serveur de base de données. Dans le cas de l'exemple précédent, la récupération échoue, sauf si 40 Gio d'espace disque ou plus sont disponibles.

Désactiver la journalisation binaire

Chaque fois que possible, désactivez la journalisation binaire lors des chargements de données volumineux afin d'éviter une surcharge des ressources et des contraintes d'espace disque supplémentaire. Dans Amazon RDS, la désactivation de la journalisation binaire consiste simplement à définir la période de rétention des sauvegardes avec la valeur zéro (0). Si vous procédez ainsi, nous vous recommandons de prendre un instantané de l'instance de base de données immédiatement avant le chargement. Au besoin, vous pourrez ainsi annuler rapidement et facilement les modifications effectuées pendant le chargement.

Après le chargement, définissez la période de rétention des sauvegardes avec une valeur appropriée, différente de zéro.

Vous ne pouvez pas définir la période de rétention des sauvegardes sur la valeur zéro si l'instance de base de données est une instance de base de données source pour les réplicas en lecture.

InnoDB

Les informations de cette section fournissent un puissant argument pour que les transactions conservent une petite taille lors de l'utilisation d'InnoDB.

Annuler

InnoDB génère l'annulation pour prendre en charge des fonctions telles que la restauration de transaction et MVCC. L'annulation est stockée dans l'espace de table système InnoDB (généralement ibdata1) et conservée jusqu'à ce qu'elle soit supprimée par le thread de purge. Comme le thread de purge ne peut pas aller au-delà de l'annulation de la transaction active la plus ancienne, il est effectivement bloqué jusqu'à ce que la transaction soit validée ou restaurée. Si la base de données traite d'autres transactions pendant le chargement, leur annulation s'accumule également dans l'espace de table système et ne peut pas être supprimée, même si les transactions sont validées et qu'aucune transaction ne nécessite l'annulation pour MVCC. Dans ce cas, toutes les transactions (y compris les transactions en lecture seule) qui accèdent aux lignes modifiées par une transaction quelle qu'elle soit (pas simplement la transaction de chargement) ralentissent, car elles analysent les annulations susceptibles d'avoir été purgées si ce n'est pour la transaction de chargement de longue durée. Ce ralentissement provient du fait que les transactions analysent les annulations susceptibles d'avoir été purgées si ce n'est pour la transaction de chargement de longue durée.

Les annulations sont stockées dans l'espace de table du système, et celui-ci ne peut jamais être réduit. Les transactions de chargements de données volumineux peuvent donc entraîner un agrandissement conséquent de l'espace de table système et utiliser ainsi une espace disque que vous ne pouvez pas revendiquer sans recréer intégralement la base de données.

Restauration

InnoDB est optimisé pour les validations. La restauration d'une transaction à une version antérieure peut être extrêmement longue. Dans certains cas, il peut être plus rapide d'effectuer une point-in-time restauration ou de restaurer un instantané de base de données.

Format des données en entrée

MySQL peut accepter les données entrantes sous deux formes différentes : fichiers plats et SQL. Cette section soulignes certains des principaux avantages et désavantages de chaque format.

Fichiers plats

Le chargement de fichiers plats avec LOAD DATA LOCAL INFILE peut être la solution la plus rapide et la moins coûteuse pour charger les données, aussi longtemps que la taille des transactions demeure relativement petite. Comparés au chargement des mêmes données avec SQL, les fichiers plats nécessitent généralement moins de trafic réseau, des coûts de transmission inférieurs et un chargement plus rapide en raison d'une surcharge réduite de la base de données.

Une seule transaction de grande taille

LOAD DATA LOCAL INFILE charge la totalité du fichier plat comme une seule transaction. Ce n'est pas nécessairement un inconvénient. Si la taille des fichiers individuels peut demeurer petite, cette situation présente un certain nombre d'avantages :

  • Capacité de reprise – il est facile de suivre les fichiers qui ont été chargés. Si un problème survient pendant le chargement, vous pouvez reprendre la transaction là où elle s'est arrêtée sans trop de peine. Certaines données devront peut-être être retransmises vers Amazon RDS, mais dans le cas de petits fichiers, la quantité retransmise est minimale.

  • Chargement de données en parallèle – si vous devez économiser les opérations d'IOPS et la bande passante réseau avec un seul chargement de fichier, le chargement en parallèle peut économiser du temps.

  • Limitation du taux de chargement – le chargement des données impacte-t-il négativement d'autres processus ? Limitez le chargement en augmentant l'intervalle entre les fichiers.

Soyez vigilant

Les avantages de LOAD DATA LOCAL INFILE diminuent rapidement lorsque la taille des transactions augmente. Si la décomposition d'un ensemble de données volumineux en ensembles de plus petite taille n'est pas une option, SQL peut être le meilleur choix.

SQL

SQL possède un avantage principal sur les fichiers plats : il permet facilement de conserver aux transactions une petite taille. Cependant, le chargement de SQL peut prendre significativement plus de temps que les fichiers plats et il peut être difficile de définir à quel endroit reprendre le chargement après une défaillance. Par exemple, il n'est pas possible de redémarrer les fichiers mysqldump. Si une défaillance se produit lors du chargement d'un fichier mysqldump, celui-ci nécessite une modification ou un remplacement avant que le chargement puisse reprendre. La solution consiste à procéder à une restauration à un instant dans le passé avant le chargement et à relire le fichier après que l'origine de la défaillance a été éliminée.

Effectuer des points de contrôle à l'aide des instantanés Amazon RDS

Si vous avez un chargement qui va nécessiter plusieurs heures, voire plusieurs jours, le chargement sans journalisation binaire n'est pas une perspective très attrayante, à moins que vous ne puissiez effectuer des points de contrôle réguliers. C'est là que la fonction de snapshot DB Amazon RDS se révèle très pratique. Un instantané de base de données crée une copie point-in-time cohérente de votre instance de base de données qui peut être utilisée pour restaurer la base de données à ce moment-là après un crash ou un autre incident.

Pour créer un point de contrôle, prenez simplement un snapshot DB. Tous les snapshots DB précédents pris pour les points de contrôle peuvent être supprimés sans affecter la durabilité ou le temps de restauration.

Comme les instantanés sont également rapides, les points de contrôle fréquents n'allongent pas de façon significative le temps de chargement.

Diminution du temps de chargement

Voici quelques conseils supplémentaires pour réduire les temps de chargement :

  • Créez tous les index secondaires avant le chargement. Cette solution est contre-intuitive pour ceux qui connaissent d'autres bases de données. L'ajout ou la modification d'un index secondaire entraîne la création par MySQL d'une nouvelle table avec les modifications d'index, la copie des données de la table existante vers la nouvelle table et la suppression de la table d'origine.

  • Chargez les données dans l'ordre PK. Ce conseil est particulièrement utile pour les tables InnoDB, pour lesquelles les temps de chargement peuvent être réduits de 75 à 80 % et la taille des fichiers de données divisée par deux.

  • Désactivez les contraintes de clé étrangère foreign_key_checks=0. Ceci est nécessaire pour les fichiers plats chargés avec LOAD DATA LOCAL INFILE dans de nombreux cas. Pour tout chargement, la désactivation des contrôles FK offre des gains de performance significatifs. Veillez simplement à bien activer les contraintes et à vérifier les données après le chargement.

  • Effectuez un chargement en parallèle à moins que vos ressources ne soient proches d'une limite. Utilisez des tables partitionnées le cas échéant.

  • Utilisez des insertions à valeurs multiples lors du chargement avec SQL pour minimiser les frais généraux lors de l'exécution d'instructions. Si vous utilisez mysqldump, cela est fait automatiquement.

  • Réduisez les I/O du journal InnoDB innodb_flush_log_at_trx_commit=0

  • Si vous chargez des données dans une instance de base de données ne disposant pas de réplicas en lecture, définissez le paramètre sync_binlog sur 0 lors du chargement des données. Une fois le chargement des données terminé, définissez le paramètre sync_binlog de nouveau sur 1.

  • Chargez les données avant de convertir l'instance de base de données en déploiement multi-AZ. Toutefois, si l'instance de base de données utilise déjà un déploiement multi-AZ, passer à un déploiement mono-AZ pour le chargement des données n'est pas recommandé, car cela fournit uniquement des améliorations marginales.

Note

L'utilisation d'innodb_flush_log_at_trx_commit=0 oblige InnoDB à vider ses journaux toutes les secondes, et non à chaque validation. Il en résulte un avantage conséquent en termes de vitesse, mais cela peut aussi conduire à une perte des données lors d'un incident. A utiliser avec précaution.

Rubriques