Comment accélérer les performances d'insertion dans PostgreSQL

Question

Je teste les performances d'insertion de Postgres. J'ai une table avec une colonne dont le type de données est un nombre. Il y a également un index sur cette colonne. J'ai rempli la base de données en utilisant cette requête :

insert into aNumber (id) values (564),(43536),(34560) ...

J'ai inséré 4 millions de lignes très rapidement, 10 000 à la fois, avec la requête ci-dessus. Lorsque la base de données a atteint 6 millions d'enregistrements, les performances ont considérablement diminué pour atteindre 1 million d'enregistrements toutes les 15 minutes. Existe-t-il un moyen d'améliorer les performances d'insertion ? J'ai besoin de performances d'insertion optimales pour ce projet.

J'utilise Windows 7 Pro sur une machine dotée de 5 Go de RAM.

Answer 1

Voir alimenter une base de données dans le manuel de PostgreSQL, l'excellent article de depesz, comme d'habitude sur le sujet, et cette question SO .

_(Notez que cette réponse concerne le chargement en masse de données dans une BD existante ou la création d'une nouvelle BD. Si vous êtes intéressé par les performances de restauration de BD avec pg_restore ou psql l'exécution de pg_dump la sortie, beaucoup de cela ne s'applique pas puisque pg_dump et pg_restore fait déjà des choses comme créer des triggers et des index après avoir terminé une restauration de schéma+données)_ .

Il y a beaucoup de choses à faire. La solution idéale serait d'importer dans un UNLOGGED sans index, puis la changer en table enregistrée et ajouter les index. Malheureusement, dans PostgreSQL 9.4, il n'y a pas de support pour changer les tables de la catégorie UNLOGGED à enregistrer. 9.5 ajoute ALTER TABLE ... SET LOGGED pour vous permettre de le faire.

Si vous pouvez mettre votre base de données hors ligne pour l'importation en masse, utilisez pg_bulkload .

Autrement :

• Désactiver tous les déclencheurs sur la table

• Supprimez les index avant de commencer l'importation, recréez-les ensuite. (Il faut beaucoup Il faut moins de temps pour construire un index en une seule passe que pour y ajouter progressivement les mêmes données, et l'index résultant est beaucoup plus compact).

• Si vous effectuez l'importation au cours d'une seule transaction, il est prudent de supprimer les contraintes de clé étrangère, d'effectuer l'importation et de recréer les contraintes avant de valider. Ne faites pas cela si l'importation est répartie sur plusieurs transactions, car vous risquez d'introduire des données non valides.

• Si possible, utilisez COPY au lieu de INSERT s

• Si vous ne pouvez pas utiliser COPY envisager d'utiliser des INSERT si possible. Vous semblez déjà le faire. N'essayez pas d'énumérer trop de nombreuses valeurs dans un seul VALUES Cependant, ces valeurs doivent tenir dans la mémoire plusieurs fois, il faut donc s'en tenir à quelques centaines par déclaration.

• Regroupez vos insertions dans des transactions explicites, en effectuant des centaines de milliers ou des millions d'insertions par transaction. Il n'y a pas de limite pratique AFAIK, mais le regroupement vous permettra de récupérer d'une erreur en marquant le début de chaque lot dans vos données d'entrée. Encore une fois, vous semblez déjà le faire.

• Utilisez synchronous_commit=off et un énorme commit_delay pour réduire les coûts de fsync(). Cela ne sera pas d'une grande aide si vous avez regroupé votre travail dans de grosses transactions, cependant.

• INSERT ou COPY en parallèle à partir de plusieurs connexions. Le nombre de connexions dépend du sous-système de disque de votre matériel ; en règle générale, il est préférable d'avoir une connexion par disque dur physique si vous utilisez un stockage en attachement direct.

• Fixez un niveau élevé max_wal_size valeur ( checkpoint_segments dans les anciennes versions) et activer log_checkpoints . Regardez les journaux de PostgreSQL et assurez-vous qu'il ne se plaint pas de points de contrôle trop fréquents.

• Si et seulement si vous n'avez pas peur de perdre tout votre cluster PostgreSQL (votre base de données et toutes les autres sur le même cluster) à cause d'une corruption catastrophique si le système se plante pendant l'importation, vous pouvez arrêter Pg, mettre fsync=off lancez Pg, réalisez votre importation, puis (c'est essentiel) arrêtez Pg et réglez les paramètres d'importation. fsync=on à nouveau. Voir Configuration WAL . Ne faites pas cela s'il y a déjà des données qui vous intéressent dans une base de données de votre installation PostgreSQL. Si vous définissez fsync=off vous pouvez également définir full_page_writes=off ; encore une fois, n'oubliez pas de la réactiver après votre importation pour éviter la corruption de la base de données et la perte de données. Voir paramètres non durables dans le manuel Pg.

Vous devriez également envisager de régler votre système :

• Utilisez bonne qualité SSDs pour le stockage autant que possible. Les bons disques SSD dotés de caches d'écriture fiables et protégés par une alimentation électrique accélèrent considérablement les taux d'engagement. Ils sont moins avantageux lorsque vous suivez les conseils ci-dessus - qui réduisent les vidages de disque / le nombre de commit. fsync() mais peuvent néanmoins être d'une grande aide. N'utilisez pas de disques SSD bon marché sans protection adéquate contre les pannes d'électricité, à moins que vous ne teniez pas à conserver vos données.

• Si vous utilisez RAID 5 ou RAID 6 pour le stockage en attachement direct, arrêtez maintenant. Sauvegardez vos données, restructurez votre matrice RAID en RAID 10, et réessayez. Les RAID 5/6 sont sans espoir pour les performances d'écriture en masse - bien qu'un bon contrôleur RAID avec un gros cache puisse aider.

• Si vous avez la possibilité d'utiliser un contrôleur RAID matériel avec un grand cache d'écriture sur batterie, cela peut vraiment améliorer les performances d'écriture pour les charges de travail avec beaucoup de commits. Cela n'est pas aussi utile si vous utilisez un commit asynchrone avec un commit_delay ou si vous faites moins de grosses transactions pendant le chargement en masse.

• Si possible, stockez WAL ( pg_wal ou pg_xlog dans les anciennes versions) sur un disque ou une matrice de disques séparés. Il y a peu d'intérêt à utiliser un système de fichiers distinct sur le même disque. Les gens choisissent souvent d'utiliser une paire RAID1 pour WAL. Encore une fois, cela a plus d'effet sur les systèmes avec des taux de commit élevés, et cela a peu d'effet si vous utilisez une table non journalisée comme cible de chargement de données.

Vous pouvez également être intéressé par Optimiser PostgreSQL pour des tests rapides .

Answer 2

J'ai passé environ 6 heures sur le même problème aujourd'hui. Les insertions se déroulent à une vitesse "normale" (moins de 3 secondes par 100K) jusqu'à 5MI (sur un total de 30MI) rangées et ensuite les performances chutent drastiquement (jusqu'à 1 minute par 100K).

Je ne vais pas énumérer toutes les choses qui n'ont pas fonctionné et aller directement à l'essentiel.

I a abandonné une clé primaire sur la table cible (qui était un GUID) et mes 30 millions de lignes se sont joyeusement écoulées vers leur destination à une vitesse constante de moins de 3 secondes par 100K.

Answer 3

Utilisez COPY table TO ... WITH BINARY qui est selon la documentation " est un peu plus rapide que les formats texte et CSV. ." Ne faites cela que si vous avez des millions de lignes à insérer et si vous êtes à l'aise avec les données binaires.

Voici un exemple de recette en Python, utilisant psycopg2 avec une entrée binaire .

Answer 4

En plus de l'excellent article de Craig Ringer et de l'article du blog de depesz, si vous souhaitez accélérer vos insertions via ODBC ( psqlodbc ) en utilisant des insertions d'instructions préparées à l'intérieur d'une transaction, il y a quelques choses supplémentaires que vous devez faire pour que cela fonctionne rapidement :

1. Définir le niveau de retour sur erreur à "Transaction" en spécifiant Protocol=-1 dans la chaîne de connexion. Par défaut, psqlodbc utilise le niveau "Statement", qui crée un SAVEPOINT pour chaque déclaration plutôt que pour une transaction entière, ce qui rend les insertions plus lentes.
2. Utilisez les instructions préparées côté serveur en spécifiant UseServerSidePrepare=1 dans la chaîne de connexion. Sans cette option, le client envoie la totalité de l'instruction d'insertion avec chaque ligne insérée.
3. Désactiver l'auto-commit sur chaque déclaration en utilisant SQLSetConnectAttr(conn, SQL_ATTR_AUTOCOMMIT, reinterpret_cast<SQLPOINTER>(SQL_AUTOCOMMIT_OFF), 0);
4. Une fois que toutes les lignes ont été insérées, validez la transaction en utilisant la méthode suivante SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT); . Il n'est pas nécessaire d'ouvrir explicitement une transaction.

Malheureusement, psqlodbc "implémente" SQLBulkOperations en émettant une série d'instructions d'insertion non préparées, de sorte que pour obtenir l'insertion la plus rapide, il faut coder manuellement les étapes ci-dessus.

Answer 5

S'il vous arrivait d'insérer des colonnes avec des UUID (ce qui n'est pas le cas), vous ne seriez pas en mesure de le faire. exactement votre cas) et pour ajouter à @Dennis réponse (Je ne peux pas encore commenter), sachez que l'utilisation de gen_random_uuid() (nécessite PG 9.4 et le module pgcrypto) est (beaucoup) plus rapide que uuid_generate_v4().

=# explain analyze select uuid_generate_v4(),* from generate_series(1,10000);
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series  (cost=0.00..12.50 rows=1000 width=4) (actual time=11.674..10304.959 rows=10000 loops=1)
 Planning time: 0.157 ms
 Execution time: 13353.098 ms
(3 filas)

vs

=# explain analyze select gen_random_uuid(),* from generate_series(1,10000);
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series  (cost=0.00..12.50 rows=1000 width=4) (actual time=252.274..418.137 rows=10000 loops=1)
 Planning time: 0.064 ms
 Execution time: 503.818 ms
(3 filas)

De plus, c'est le Proposition de la manière officielle de le faire

Nota

Si vous n'avez besoin que d'UUID générés de manière aléatoire (version 4), envisagez d'utiliser la fonction gen_random_uuid() du module pgcrypto à la place.

Le temps d'insertion est passé de ~2 heures à ~10 minutes pour 3,7 millions de lignes.