IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)

14.4. Remplir une base de données

Vous pourriez avoir besoin d'insérer un grand nombre de données pour remplir une base de données au tout début. Cette section contient quelques suggestions pour réaliser cela de la façon la plus efficace.

14.4.1. Désactivez la validation automatique (autocommit)

Lors d'INSERT multiples, désactivez la validation automatique et faites une seule validation à la fin (en SQL, ceci signifie de lancer BEGIN au début et COMMIT à la fin. Quelques bibliothèques client pourraient le faire derrière votre dos auquel cas vous devez vous assurer que la bibliothèque le fait quand vous le voulez). Si vous permettez à chaque insertion d'être validée séparément, PostgreSQL™ fait un gros travail pour chaque ligne ajoutée. Un bénéfice supplémentaire de réaliser toutes les insertions dans une seule transaction est que si l'insertion d'une ligne échoue alors les lignes insérées jusqu'à maintenant seront annulées. Vous ne serez donc pas bloqué avec des données partiellement chargées.

14.4.2. Utilisez COPY

Utilisez COPY pour charger toutes les lignes en une seule commande, plutôt que d'utiliser une série de commandes INSERT. La commande COPY est optimisée pour charger un grand nombre de lignes ; elle est moins flexible que INSERT mais introduit significativement moins de surcharge lors du chargement de grosses quantités de données. Comme COPY est une seule commande, il n'y a pas besoin de désactiver la validation automatique (autocommit) si vous utilisez cette méthode pour remplir une table.

Si vous ne pouvez pas utiliser COPY, utiliser PREPARE pourrait vous aider à créer une instruction préparée INSERT, puis utilisez EXECUTE autant de fois que nécessaire. Ceci évite certaines surcharges lors d'une analyse et d'une planification répétées de commandes INSERT. Différentes interfaces fournissent cette fonctionnalité de plusieurs façons ; recherchez « instructions préparées » dans la documentation de l'interface.

Notez que charger un grand nombre de lignes en utilisant COPY est pratiquement toujours plus rapide que d'utiliser INSERT, même si PREPARE ... INSERT est utilisé lorsque de nombreuses insertions sont groupées en une seule transaction.

COPY est plus rapide quand il est utilisé dans la même transaction que la commande CREATE TABLE ou TRUNCATE précédente. Dans ce cas, les journaux de transactions ne sont pas impactés car, en cas d'erreur, les fichiers contenant les données nouvellement chargées seront supprimés de toute façon. Néanmoins, cette considération ne s'applique pas quand archive_mode est activé, car toutes les commandes doivent écrire dans les journaux de transaction dans ce cas.

14.4.3. Supprimez les index

Si vous chargez une table tout juste créée, la méthode la plus rapide est de créer la table, de charger en lot les données de cette table en utilisant COPY, puis de créer tous les index nécessaires pour la table. Créer un index sur des données déjà existantes est plus rapide que de mettre à jour de façon incrémentale à chaque ligne ajoutée.

Si vous ajoutez beaucoup de données à une table existante, il pourrait être avantageux de supprimer l'index, de charger la table, puis de recréer l'index. Bien sûr, les performances de la base de données pour les autres utilisateurs pourraient souffrir tout le temps où l'index sera manquant. Vous devez aussi y penser à deux fois avant de supprimer des index uniques car la vérification d'erreur apportée par la contrainte unique sera perdue tout le temps où l'index est manquant.

14.4.4. Suppression des contraintes de clés étrangères

Comme avec les index, une contrainte de clé étrangère peut être vérifiée « en gros volume » plus efficacement que ligne par ligne. Donc, il pourrait être utile de supprimer les contraintes de clés étrangères, de charger les données et de créer de nouveau les contraintes. De nouveau, il y a un compromis entre la vitesse de chargement des données et la perte de la vérification des erreurs lorsque la contrainte manque.

14.4.5. Augmentez maintenance_work_mem

Augmentez temporairement la variable maintenance_work_mem lors du chargement de grosses quantités de données peut amener une amélioration des performances. Ceci aidera à l'accélération des commandes CREATE INDEX et ALTER TABLE ADD FOREIGN KEY. Cela ne changera pas grand chose pour la commande COPY. Donc, ce conseil est seulement utile quand vous utilisez une des deux ou les deux techniques ci-dessus.

14.4.6. Augmentez checkpoint_segments

Augmenter temporairement la variable de configuration checkpoint_segments peut aussi aider à un chargement rapide de grosses quantités de données. Ceci est dû au fait que charger une grosse quantité de données dans PostgreSQL™ causera la venue trop fréquente de points de vérification (la fréquence de ces points de vérification est spécifiée par la variable de configuration checkpoint_timeout). Quand survient un point de vérification, toutes les pages modifiées sont écrites sur le disque. En augmentant checkpoint_segments temporairement lors du chargement des données, le nombre de points de vérification requis peut être diminué.

14.4.7. Désactiver archive_mode

Lors du chargement de grosses quantités de données dans un cluster qui utiliser l'archive des journaux de transaction, vous pouvez souhaiter désactiver l'archivage (désactiver le paramètre archive_mode) lors du chargement. Il peut être plus rapide de réaliser une nouvelle sauvegarde de base une fois le chargement terminé plutôt que de traiter un grand nombre de données WAL incrémentales. Mais notez qu'activer et désactiver archive_mode demande un redémarrage du serveur.

En dehors d'éviter le temps de traitement des données WAL, la désactivation rendra certaines commandes plus rapides car elles sont conçues pour ne pas écrire dans les WAL si archive_mode est désactivé (elles peuvent garantir une certaine immunité aux arrêts brutaux en exécutant un fsync à la fin plutôt que d'écrire dans les WAL.) Ceci s'applique aux commandes suivantes :

  • CREATE TABLE AS SELECT

  • CREATE INDEX (et les variantes telles que ALTER TABLE ADD PRIMARY KEY)

  • ALTER TABLE SET TABLESPACE

  • CLUSTER

  • COPY FROM, quand la table cible vient d'être créée ou vidée auparavant dans la transaction

14.4.8. Lancez ANALYZE après

Quand vous avez changé significativement la distribution des données à l'intérieur d'une table, lancer ANALYZE est fortement recommandée. Ceci inclut le chargement de grosses quantités de données dans la table. Lancer ANALYZE (ou VACUUM ANALYZE) vous assure que le planificateur dispose de statistiques à jour sur la table. Sans statistiques ou avec des statistiques obsolètes, le planificateur pourrait prendre de mauvaises décisions lors de la planification de la requête, amenant des performances pauvres sur toutes les tables sans statistiques ou avec des statistiques inexactes. Notez que si le démon autovacuum est désactivée, il pourrait exécuter ANALYZE automatiquement ; voir Maintenir les statistiques du planificateur et Le démon auto-vacuum pour plus d'informations.

14.4.9. Quelques notes sur pg_dump

Les scripts de sauvegarde générés par pg_dump appliquent automatiquement plusieurs des indications ci-dessus, mais pas toutes. Pour recharger une sauvegarde pg_dump aussi rapidement que possible, vous avez besoin de faire quelques étapes supplémentaires manuellement (notez que ces points s'appliquent lors de la restauration d'une sauvegarde, et non pas lors de sa création. Les mêmes points s'appliquent lors de l'utilisation de pg_restore pour charger un fichier de sauvegarde pg_dump).

Par défaut, pg_dump utilise COPY et, lorsqu'il génère une sauvegarde complexe, schéma et données, il est préférable de charger les données avant de créer les index et les clés étrangères. Donc, dans ce cas, plusieurs lignes de conduite sont gérées automatiquement. Ce qui vous reste à faire est de :

  • Configurez des valeurs appropriées (c'est-à-dire plus importante que la normale) pour maintenance_work_mem et checkpoint_segments.

  • Si vous utilisez l'archivage des WAL, considérez sa désactivation lors de la restauration. Pour cela, désactiver archive_mode avant de charger le script de sauvegarde, puis ré-activez-le et réalisez une sauvegarde de base.

  • Demandez-vous si la sauvegarde complète doit être restaurée dans une seule transaction. Pour cela, passez l'option -1 ou --single-transaction à psql pi pg_restore. Lors de l'utilisation de ce mode, même les erreurs les plus petites annuleront la restauration complète, peut-être en annulant des heures de traitement. Suivant à quel point les données sont en relation, il peut être préférable de faire un nettoyage manuel. Les commandes COPY s'exécuteront plus rapidement si vous utilisez une transaction simple et que vous avez désactivé l'archivage des journaux de transaction.

  • Exécutez ANALYZE après coup.

Une sauvegarde des données seules utilise toujours COPY mais elle ne supprime ni ne recrée les index et elle ne touche généralement pas les clés étrangères. [10] Donc, lorsque vous chargez une sauvegarde ne contenant que les données, c'est à vous de supprimer et recréer les index et clés étrangères si vous souhaitez utiliser ces techniques. Il est toujours utile d'augmenter checkpoint_segments lors du chargement des données mais ne vous embêtez pas à augmenter maintenance_work_mem ; en fait, vous le ferez lors d'une nouvelle création manuelle des index et des clés étrangères. Et n'oubliez pas ANALYZE une fois que vous avez terminé ; voir Maintenir les statistiques du planificateur et Le démon auto-vacuum pour plus d'informations.



[10] Vous pouvez obtenir l'effet de désactivation des clés étrangères en utilisant l'option --disable-triggers -- mais réalisez que cela élimine, plutôt que repousse, la validation des clés étrangères et qu'il est du coup possible d'insérer des données mauvaises si vous l'utilisez.