Share via

Meilleures pratiques pour charger des données en bloc dans Azure Database pour PostgreSQL – Serveur flexible

  • Article

S’APPLIQUE À : Azure Database pour PostgreSQL – Serveur flexible

Cet article décrit différentes méthodes de chargement de données en bloc dans Azure Database pour PostgreSQL serveur flexible, ainsi que les meilleures pratiques pour les chargements de données initiales dans les bases de données vides et les chargements incrémentiels de données.

Méthodes de chargement

Les méthodes de chargement de données suivantes sont organisées de la plus longue à la moins longue :

  • Exécutez une commande INSERT à enregistrement unique.
  • Réalisez des lots compris entre 100 et 1 000 lignes par validation. Vous pouvez utiliser un bloc de transaction pour envelopper plusieurs enregistrements par validation.
  • Exécutez INSERT avec plusieurs valeurs de ligne.
  • Exécutez la commande COPY.

La méthode recommandée pour charger des données dans une base de données consiste à utiliser la commande COPY. Si la commande COPY ne peut pas être exécutée, le traitement INSERT est la deuxième meilleure méthode. Le multithreading avec une commande COPY est la méthode optimale pour les chargements en bloc de données.

Meilleures pratiques pour les chargements de données initiales

Supprimer les index

Avant d’effectuer un chargement initial des données, nous vous recommandons de supprimer tous les index dans les tables. Il est toujours plus efficace de créer les index après le chargement des données.

Supprimer les contraintes

Les principales contraintes à supprimer sont décrites ici :

  • Contraintes de clés uniques

    Pour obtenir de bonnes performances, nous vous recommandons de supprimer les contraintes de clé unique avant le chargement initial des données et de les recréer une fois le chargement des données terminé. Toutefois, la suppression des contraintes de clé unique annule les protections contre les données dupliquées.

  • Contraintes de clés étrangères

    Nous vous recommandons de supprimer les contraintes de clé étrangère avant le chargement initial des données et de les recréer une fois le chargement des données terminé.

    Le fait de modifier le paramètre session_replication_role pour le définir sur replica désactive également toutes les vérifications de clé étrangère. Toutefois, sachez que la modification peut laisser des données dans un état incohérent si elle n’est pas correctement utilisée.

Tables non journalisées

Tenez compte des avantages et des inconvénients de l’utilisation de tables non journalisées avant de les utiliser dans les chargements de données initiaux.

L’utilisation de tables non journalisées accélère le chargement des données. Les données écrites dans des tables non journalisées ne sont pas écrites dans le journal WAL (write-ahead log).

Les inconvénients liés à l’utilisation de tables non journalisées sont les suivants :

  • Elles ne sont pas protégées contre les incidents. Une table non journalisée est automatiquement tronquée après un incident ou un arrêt brutal.
  • Les données des tables non journalisées ne peuvent pas être répliquées sur des serveurs de secours.

Pour créer une table non journalisée ou modifier une table existante en table non journalisée, utilisez les options suivantes :

  • Créez une nouvelle table non journalisée en utilisant la syntaxe :

    CREATE UNLOGGED TABLE <tablename>;

  • Convertissez une table journalisée existante en table non journalisée en utilisant la syntaxe suivante :

    ALTER TABLE <tablename> SET UNLOGGED;

Optimisation des paramètres de serveur

  • autovacuum : pendant le chargement initial des données, il est préférable de désactiver autovacuum. Une fois le chargement initial terminé, nous vous recommandons d’exécuter un VACUUM ANALYZE manuel sur toutes les tables de la base de données, puis d’activer autovacuum.

Notes

Suivez les recommandations indiquées ici uniquement s’il y a suffisamment de mémoire et d’espace disque.

  • maintenance_work_mem: peut être défini sur un maximum de 2 gigaoctets (Go) sur une instance de serveur flexible Azure Database pour PostgreSQL. maintenance_work_mem permet d’accélérer la création de clés étrangères, d’index et le nettoyage automatique.

  • checkpoint_timeout: sur une instance de serveur flexible Azure Database pour PostgreSQL, la checkpoint_timeout valeur peut être augmentée à un maximum de 24 heures à partir du paramètre par défaut de 5 minutes. Nous vous recommandons d’augmenter la valeur à 1 heure avant de charger initialement des données sur l’instance de serveur flexible Azure Database pour PostgreSQL.

  • checkpoint_completion_target : nous recommandons une valeur de 0,9.

  • max_wal_size: peut être défini sur la valeur maximale autorisée sur une instance de serveur flexible Azure Database pour PostgreSQL, qui est de 64 Go pendant que vous effectuez la charge initiale des données.

  • wal_compression : peut être activé. L’activation du paramètre peut entraîner un coût supplémentaire pour le processeur par rapport à la compression pendant la journalisation dans le journal WAL (write-ahead log) et à la décompression pendant la relecture WAL.

Azure Database pour PostgreSQL recommandations de serveur flexibles

Avant de commencer une charge de données initiale sur l’instance de serveur flexible Azure Database pour PostgreSQL, nous vous recommandons de :

  • Désactiver la haute disponibilité sur le serveur. Vous pouvez l’activer une fois le chargement initial terminé sur le serveur principal.
  • Créer des réplicas en lecture une fois le chargement initial des données terminé.
  • Rendre la journalisation minimale ou la désactiver complètement pendant les chargements de données initiaux (par exemple : désactiver pgaudit, pg_stat_statements, magasin de requêtes).

Recréer des index et ajouter des contraintes

En supposant que vous avez supprimé les index et les contraintes avant le chargement initial, nous vous recommandons d’utiliser les valeurs élevées dans maintenance_work_mem (comme mentionné précédemment) pour créer des index et ajouter des contraintes. En outre, à compter de PostgreSQL version 11, les paramètres suivants peuvent être modifiés pour accélérer la création d’index parallèle après le chargement initial des données :

  • max_parallel_workers : définit le nombre maximal de Workers que le système peut prendre en charge pour les requêtes parallèles.

  • max_parallel_maintenance_workers : contrôle le nombre maximal de processus Worker, qui peuvent être utilisés dans CREATE INDEX.

Vous pouvez également créer les index en utilisant les paramètres recommandés au niveau de la session. Voici un exemple de la procédure à suivre :

SET maintenance_work_mem = '2GB';
SET max_parallel_workers = 16;
SET max_parallel_maintenance_workers = 8;
CREATE INDEX test_index ON test_table (test_column);

Meilleures pratiques pour les chargements de données incrémentielles

Partitionner des tables

Nous vous recommandons toujours de partitionner des tables volumineuses. Voici certains avantages du partitionnement, en particulier pendant les chargements incrémentiels :

  • La création de nouvelles partitions basées sur les nouveaux deltas permet d’ajouter de nouvelles données à la table.
  • La gestion des tables devient plus facile. Vous pouvez supprimer une partition pendant un chargement incrémentiel des données pour éviter les suppressions fastidieuses dans les tables volumineuses.
  • Le nettoyage automatique est déclenché uniquement sur les partitions qui ont été modifiées ou ajoutées pendant les chargements incrémentiels, ce qui facilite la maintenance des statistiques sur la table.

Maintenir à jour les statistiques sur la table

La surveillance et la maintenance des statistiques sur la table sont importantes pour les performances des requêtes sur la base de données. Cela inclut également les scénarios dans lesquels vous avez des chargements de données incrémentielles. PostgreSQL utilise le processus de démon de nettoyage automatique pour nettoyer les tuples morts et analyser les tables pour maintenir les statistiques à jour. Pour plus d’informations, consultez Surveillance et réglage du nettoyage automatique.

Créer des index sur des contraintes de clé étrangère

La création d’index sur des clés étrangères dans les tables enfants peut être bénéfique dans les scénarios suivants :

  • Mises à jour ou suppressions de données dans la table parent. Lorsque les données sont mises à jour ou supprimées dans la table parent, les recherches sont effectuées dans la table enfant. Pour accélérer les recherches, vous pouvez indexer les clés étrangères sur la table enfant.
  • Les requêtes, où vous pouvez voir la jointure de tables parentes et enfants sur des colonnes clés.

Identifier les index inutilisés

Identifiez les index inutilisés dans la base de données et supprimez-les. Les index constituent une surcharge pour les chargements de données. Plus les index d’une table sont réduits, meilleures sont les performances pendant l’ingestion des données.

Vous pouvez identifier les index inutilisés de deux façons : par magasin des requêtes et dans une requête d’utilisation d’index.

Magasin de requêtes

La fonctionnalité Magasin des requêtes permet d’identifier les index, qui peuvent être supprimés en fonction des modèles d’utilisation des requêtes sur la base de données. Pour des conseils étape par étape, consultez Magasin de requêtes.

Une fois que vous avez activé la fonctionnalité Magasin des requêtes sur le serveur, vous pouvez utiliser la requête suivante pour identifier les index qui peuvent être supprimés en vous connectant à la base de données azure_sys.

SELECT * FROM IntelligentPerformance.DropIndexRecommendations;

Utilisation de l’index

Vous pouvez également utiliser la requête suivante pour identifier les index inutilisés :

SELECT 
    t.schemaname, 
    t.tablename, 
    c.reltuples::bigint                            AS num_rows, 
    pg_size_pretty(pg_relation_size(c.oid))        AS table_size, 
    psai.indexrelname                              AS index_name, 
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, 
    CASE WHEN i.indisunique THEN 'Y' ELSE 'N' END AS "unique", 
    psai.idx_scan                                  AS number_of_scans, 
    psai.idx_tup_read                              AS tuples_read, 
    psai.idx_tup_fetch                             AS tuples_fetched 
FROM 
    pg_tables t 
    LEFT JOIN pg_class c ON t.tablename = c.relname 
    LEFT JOIN pg_index i ON c.oid = i.indrelid 
    LEFT JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelid 
WHERE 
    t.schemaname NOT IN ('pg_catalog', 'information_schema') 
ORDER BY 1, 2;

Les colonnes number_of_scans, tuples_read et tuples_fetched indiquent la valeur de colonne usage.number_of_scans de l’index de zéro points comme index inutilisé.

Optimisation des paramètres de serveur

Notes

Suivez les recommandations dans les paramètres suivants uniquement s’il y a suffisamment de mémoire et d’espace disque.

  • maintenance_work_mem: ce paramètre peut être défini sur un maximum de 2 Go sur l’instance de serveur flexible Azure Database pour PostgreSQL. maintenance_work_mem accélère la création d’index et les ajouts de clés étrangères.

  • checkpoint_timeout: sur l’instance de serveur flexible Azure Database pour PostgreSQL, la checkpoint_timeout valeur peut être augmentée à 10 ou 15 minutes à partir du paramètre par défaut de 5 minutes. L’augmentation du paramètre checkpoint_timeout pour le définir sur une valeur supérieure, telle que 15 minutes, peut réduire la charge d’E/S, mais l’inconvénient est qu’il faut plus de temps pour récupérer en cas d’incident. Nous vous recommandons d’y apporter une attention particulière avant d’apporter la modification.

  • checkpoint_completion_target : nous recommandons une valeur de 0,9.

  • max_wal_size : cette valeur dépend de la référence SKU, du stockage et de la charge de travail. Une façon de trouver la valeur appropriée pour max_wal_size est indiquée dans l’exemple suivant.

    Pendant les heures de pointe, atteignez une valeur en procédant comme suit :

    a. Prenez le numéro séquentiel de journal WAL actuel en exécutant la requête suivante :

    SELECT pg_current_wal_lsn ();

    b. Attendez le nombre de secondes de checkpoint_timeout. Prenez le numéro séquentiel de journal WAL actuel en exécutant la requête suivante :

    SELECT pg_current_wal_lsn ();

    c. Utilisez les deux résultats pour vérifier la différence, en Go :

    SELECT round (pg_wal_lsn_diff('LSN value when run second time','LSN value when run first time')/1024/1024/1024,2) WAL_CHANGE_GB;

  • wal_compression : peut être activé. L’activation de ce paramètre peut entraîner un coût supplémentaire pour le processeur par rapport à la compression pendant la journalisation WAL et à la décompression pendant la relecture WAL.

Étapes suivantes