Considérations relatives à l'importation de données pour MariaDB

Le contenu suivant contient des informations techniques relatives au chargement de données dans MariaDB. Ce contenu est destiné aux utilisateurs qui connaissent l'architecture du serveur MariaDB.

Journalisation binaire

L'activation de la journalisation binaire réduit les performances de chargement des données et nécessite jusqu'à quatre fois plus d'espace disque que la journalisation désactivée. La taille des transactions utilisées pour charger les données influe directement sur les performances du système et les besoins en espace disque. Les transactions plus importantes nécessitent davantage de ressources.

Taille de la transaction

La taille des transactions influence les aspects suivants du chargement de données MariaDB :

• Consommation de ressources

• Utilisation de l'espace disque

• Processus de CV

• Il est temps de récupérer

• 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. Vous pouvez activer et désactiver la journalisation binaire en définissant la période de conservation automatique des sauvegardes Amazon RDS. Les valeurs différentes de zéro activent la journalisation binaire, tandis que la valeur zéro la désactive. Pour de plus amples informations, veuillez consulter Période de rétention des sauvegardes.

Cette section décrit également l'impact des transactions importantes sur InnoDB et explique pourquoi il est important de réduire la taille des transactions.

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 subie dépend en partie des facteurs suivants :

• Taux de téléchargement

• Autres activités de base de données ayant lieu pendant le chargement

• Capacité de votre instance de base de données Amazon RDS

Les journaux binaires consomment également un espace disque à peu près égal à la quantité de données chargées jusqu'à ce que les journaux soient sauvegardés et supprimés. Amazon RDS minimise ce problème en sauvegardant et en supprimant fréquemment les journaux binaires.

Transactions volumineuses

Pour les transactions importantes, la journalisation binaire triple les IOPS et l'utilisation du disque pour les raisons suivantes :

• Le cache du journal binaire stocke temporairement les données de transaction sur le disque.

• Ce cache augmente avec la taille de la transaction, ce qui consomme de l'espace disque.

• Lorsque la transaction (validation ou annulation) est terminée, le système copie le cache dans le journal binaire.

Ce processus crée trois copies des données :

• Les données d'origine

• Le cache sur le disque

• La dernière entrée du journal binaire

Chaque opération d'écriture entraîne des E/S supplémentaires, ce qui a un impact supplémentaire sur les performances.

De ce fait, la journalisation binaire nécessite trois fois plus d'espace disque que la journalisation désactivée. Par exemple, le chargement de 10 GiB de données en une seule transaction crée trois copies :

• 10 GiB pour les données du tableau

• 10 GiB pour le cache du journal binaire

• 10 GiB pour le fichier journal binaire

L'espace disque temporaire total requis est de 30 GiB.

Considérations importantes relatives à l'espace disque :

• Le fichier de cache est conservé jusqu'à la fin de la session ou jusqu'à ce qu'une nouvelle transaction crée un autre cache.

• Le journal binaire est conservé jusqu'à ce qu'il soit sauvegardé et peut contenir 20 GiB (cache et journal) pendant une période prolongée.

Si vous avez l'LOAD DATA LOCAL INFILEhabitude de charger les données, Data Recovery crée une quatrième copie au cas où la base de données doive être restaurée à partir d'une sauvegarde effectuée avant le chargement. Pendant la restauration, MariaDB extrait les données du journal binaire dans un fichier plat. MariaDB s'exécute ensuite. LOAD DATA LOCAL INFILE Sur la base de l'exemple précédent, cette restauration nécessite un espace disque temporaire total de 40 Go, soit 10 Go chacun pour la table, le cache, le journal et le fichier local. Sans au moins 40 GiB d'espace disque disponible, la restauration échoue.

Optimisation des charges de données importantes

Pour les chargements de données importants, désactivez la journalisation binaire afin de réduire les frais généraux et les besoins en espace disque. Vous pouvez désactiver la journalisation binaire en définissant la période de conservation des sauvegardes sur 0. Une fois le chargement terminé, restaurez la période de conservation des sauvegardes à la valeur appropriée différente de zéro. Pour plus d'informations, reportez-vous Modification d'une RDS instance de base de données Amazon à la section « Durée de conservation des sauvegardes » dans le tableau des paramètres.

Note

Si l'instance de base de données est une instance de base de données source pour les répliques de lecture, vous ne pouvez pas définir la période de rétention des sauvegardes sur 0.

Avant de charger les données, nous vous recommandons de créer un instantané de base de données. Pour de plus amples informations, veuillez consulter Gestion des sauvegardes manuelles.

InnoDB

Les informations suivantes sur les options d'annulation de journalisation et de restauration permettent de limiter les transactions InnoDB afin d'optimiser les performances de la base de données.

Comprendre la journalisation des annulations par InnoDB

L'annulation est un mécanisme de journalisation qui permet l'annulation des transactions et prend en charge le contrôle de simultanéité multiversion (MVCC).

Pour les versions 10.11 et antérieures de MariaDB, les journaux d'annulation sont stockés dans le tablespace du système InnoDB (généralement ibdata1) et sont conservés jusqu'à ce que le thread de purge les supprime. Par conséquent, des transactions de chargement de données importantes peuvent entraîner une augmentation importante de l'espace disque logique du système et consommer de l'espace disque que vous ne pouvez récupérer que si vous recréez la base de données.

Pour toutes les versions de MariaDB, le thread de purge doit attendre que la transaction active la plus ancienne soit validée ou annulée pour supprimer les journaux d'annulation. Si la base de données traite d'autres transactions pendant le chargement, leurs journaux d'annulation s'accumulent également et ne peuvent pas être supprimés, même si les transactions sont validées et qu'aucune autre transaction n'a besoin des journaux d'annulation pour MVCC. Dans ce cas, toutes les transactions, y compris les transactions en lecture seule, ralentissent. Ce ralentissement se produit parce que toutes les transactions accèdent à toutes les lignes modifiées par toutes les transactions, et pas seulement par les transactions de chargement. En effet, les transactions doivent parcourir les journaux d'annulation que les transactions de chargement de longue durée ont empêchées d'être purgées lors du nettoyage du journal d'annulation. Cela affecte les performances de toute opération accédant aux lignes modifiées.

Options de récupération des transactions InnoDB

Bien qu'InnoDB optimise les opérations de validation, les annulations de transactions importantes sont lentes. Pour accélérer la restauration, effectuez une point-in-time restauration ou restaurez un instantané de base de données. Pour plus d’informations, consultez Point-in-time rétablissement et Restauration vers une instance de base de données.

Formats d'importation de données

MariaDB prend en charge deux formats d'importation de données : les fichiers plats et le SQL. Passez en revue les informations relatives à chaque format afin de déterminer l'option la mieux adaptée à vos besoins.

Fichiers plats

Pour les petites transactions, chargez des fichiers plats avecLOAD DATA LOCAL INFILE. Ce format d'importation de données peut offrir les avantages suivants par rapport à l'utilisation de SQL :

• Moins de trafic réseau

• Réduction des coûts de transmission de données

• Diminution des frais de traitement des bases

• Traitement plus rapide

LOAD DATA LOCAL INFILEcharge l'intégralité du fichier plat en une seule transaction. Réduisez la taille de chaque fichier pour bénéficier des avantages suivants :

• Fonctionnalité de reprise : vous pouvez suivre les fichiers qui ont été chargés. Si un problème survient pendant le chargement, vous pouvez reprendre là où vous vous étiez arrêté. Il se peut que vous deviez retransmettre certaines données vers Amazon RDS, mais dans le cas de petits fichiers, le volume retransmis est minime.

• Chargement parallèle des données : si vous disposez de suffisamment d'IOPS et de bande passante réseau pour le chargement d'un seul fichier, le chargement en parallèle peut vous faire gagner du temps.

• Contrôle du taux de charge : si le chargement de vos données a un impact négatif sur les autres processus, vous pouvez contrôler le taux de charge en augmentant l'intervalle entre les fichiers.

Les transactions importantes réduisent les avantages liés LOAD DATA LOCAL INFILE à l'importation de données. Lorsque vous ne pouvez pas diviser une grande quantité de données en fichiers plus petits, pensez à utiliser le langage SQL.

SQL

Le SQL présente un avantage majeur par rapport aux fichiers plats : vous pouvez facilement réduire la taille des transactions. Cependant, le chargement du SQL peut prendre beaucoup plus de temps que celui des fichiers plats. De plus, après un échec, il peut être difficile de déterminer où reprendre : vous ne pouvez pas redémarrer les fichiers mariadb-dump. Si un échec survient lors du chargement du fichier mariadb-dump, vous devez modifier ou remplacer le fichier avant que le chargement ne puisse reprendre. Ou bien, après avoir corrigé la cause de l'échec, vous pouvez restaurer le fichier à l'endroit où il se trouvait avant le chargement et renvoyer le fichier. Pour de plus amples informations, veuillez consulter Point-in-time rétablissement.

Utilisation des instantanés de base de données Amazon RDS pour les points de contrôle de base de données

Si vous chargez des données sur de longues durées, par exemple des heures ou des jours, sans journalisation binaire, utilisez des instantanés de base de données pour fournir des points de contrôle périodiques en matière de sécurité des données. Chaque instantané de base de données crée une copie cohérente de votre instance de base de données qui sert de point de restauration lors de défaillances du système ou d'événements de corruption de données. Les instantanés de base de données étant rapides, les points de contrôle fréquents ont un impact minimal sur les performances de chargement. Vous pouvez supprimer des instantanés de base de données précédents sans affecter la durabilité ou les capacités de restauration de la base de données. Pour plus d'informations sur les instantanés de base de données, consultezGestion des sauvegardes manuelles.

Réduction des temps de chargement des bases de données

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

• Créez tous les index secondaires avant de charger les données dans les bases de données MariaDB. Contrairement aux autres systèmes de base de données, MariaDB reconstruit l'intégralité de la table lors de l'ajout ou de la modification d'index secondaires. Ce processus crée une nouvelle table avec les modifications d'index, copie toutes les données et supprime la table d'origine.

• Chargez les données dans l'ordre des clés primaires. Pour les tables InnoDB, cela peut réduire les temps de chargement de 75 % à 80 % et la taille des fichiers de données de 50 %.

• Désactivez les contraintes liées aux clés étrangères en définissant foreign_key_checks sur0. Cela est souvent nécessaire pour les fichiers plats chargés avecLOAD DATA LOCAL INFILE. Quel que soit le chargement, la désactivation des vérifications par clé étrangère accélère le chargement des données. Une fois le chargement terminé, réactivez les contraintes en configurant 1 et foreign_key_checks en vérifiant les données.

• Chargez les données en parallèle, sauf si vous approchez d'une limite de ressources. Pour permettre le chargement simultané sur plusieurs segments de table, utilisez des tables partitionnées le cas échéant.

• Pour réduire la charge d'exécution du code SQL, combinez plusieurs INSERT instructions en INSERT opérations uniques à valeurs multiples. mariadb-dumpimplémente automatiquement cette optimisation.

• Réduisez les opérations d'E/S du journal InnoDB en innodb_flush_log_at_trx_commit réglant sur. 0 Une fois le chargement terminé, restaurez innodb_flush_log_at_trx_commit vers1.

  Avertissement

  Le réglage innodb_flush_log_at_trx_commit sur 0 oblige InnoDB à vider ses journaux toutes les secondes plutôt qu'à chaque validation. Ce paramètre améliore les performances mais peut entraîner la perte de transactions en cas de défaillance du système.

• Si vous chargez des données dans une instance de base de données qui ne possède pas de répliques de lecture, définissez sync_binlog cette valeur sur. 0 Une fois le chargement terminé, restaurez sync_binlog parameter vers1.

• Chargez les données dans une instance de base de données mono-AZ avant de convertir l'instance de base de données en un déploiement multi-AZ. Si l'instance de base de données utilise déjà un déploiement multi-AZ, nous ne recommandons pas de passer à un déploiement mono-AZ pour le chargement des données. Cela n'apporte que des améliorations marginales.