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

je teste la performance D'insertion de Postgres. J'ai un tableau avec une colonne avec le nombre comme type de données. Il existe un index sur elle ainsi. 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. Après que la base de données ait atteint 6 millions de lignes la performance a radicalement diminué à 1 Million de lignes toutes les 15 min. Y a-t-il un truc pour augmenter la performance d'insertion? J'ai besoin optimale performance d'insertion sur ce projet.

utilisant Windows 7 Pro sur une machine avec 5 Go de RAM.

153
demandé sur Erwin Brandstetter 2012-08-31 02:39:25

6 réponses

Voir remplir une base de données dans le manuel PostgreSQL, depesz est excellent comme d'habitude l'article sur le sujet, et cela DONC, la question .

(notez que cette réponse concerne le chargement de données en vrac dans un DB existant ou pour en créer un nouveau. Si vous êtes intéressé DB restaurer la performance avec pg_restore ou psql exécution de pg_dump sortie, une grande partie de cela ne appliquer depuis pg_dump et pg_restore déjà faire des choses comme la création de déclencheurs et des index après qu'il termine un schéma+restauration de données) .

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

si vous pouvez désactiver votre base de données pour l'importation en masse, utilisez pg_bulkload .

autrement:

  • désactiver tout élément déclencheur sur la table

  • déposer les index avant de commencer l'importation, les recréer ensuite. (Il faut beaucoup moins de temps pour construire un indice en un passage que pour ajouter le même données à elle progressivement, et l'indice qui en résulte est beaucoup plus compact).

  • si vous effectuez l'importation dans le cadre d'une seule transaction, il est sûr de laisser tomber les contraintes de clé étrangère, de faire l'importation, et de recréer les contraintes avant de s'engager. Ne faites pas cela si l'importation est divisée entre plusieurs transactions car vous pourriez introduire des données invalides.

  • si possible, utiliser COPY au lieu de INSERT s

  • si vous ne pouvez pas utiliser COPY envisagez d'utiliser INSERT à plusieurs valeurs si possible. Vous semblez être le faire déjà. N'essayez pas d'énumérer trop beaucoup de valeurs dans un seul VALUES cependant; ces valeurs doivent tenir dans la mémoire un couple de fois plus, alors gardez-le à quelques centaines par déclaration.

  • produisez vos inserts dans des transactions explicites, faire des centaines de milliers ou des millions d'inserts par transaction. Il n'y a pas de limite pratique AFAIK, mais batching 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 le faire déjà.

  • utilisez synchronous_commit=off et un énorme commit_delay pour réduire les coûts de fsync (). Cela n'aidera pas beaucoup si vous avez mis votre travail dans de grandes transactions, bien que.

  • INSERT ou COPY en parallèle de plusieurs connexions. Combien dépendent du sous-système de disque de votre matériel; en règle générale, vous voulez une connexion par disque dur physique si vous utilisez le stockage attaché direct.

  • mettez une valeur élevée checkpoint_segments et activez log_checkpoints . Regardez les journaux de PostgreSQL et assurez-vous qu'il ne se plaint pas des points de contrôle se produisant trop fréquemment.

  • si et seulement si cela ne vous dérange pas de perdre votre cluster PostgreSQL entier (votre base de données et tous les autres sur le même cluster) à la corruption catastrophique si le système plante pendant l'importation, vous pouvez arrêter Pg, mettre fsync=off , Démarrer Pg, faire votre importation, puis (vitale) arrêter Pg et mettre fsync=on encore. Voir configuration WAL . ne faites pas cela s'il y a déjà des données dont vous vous souciez 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 le rallumer après votre importation pour prévenir 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 regarder à l'accord de votre système:

  • Utiliser de bonne qualité Ssd pour le stockage autant que possible. Bon Ssd grâce à des caches de retour d'écriture fiables et protégés, les taux de propagation sont incroyablement plus rapides. Ils sont moins bénéfiques lorsque vous suivez les conseils ci - dessus - qui réduit les bouffées de chaleur / nombre de fsync() s-mais peut encore être une grande aide. N'utilisez pas de SSD bon marché sans protection adéquate contre les pannes de courant, sauf si vous ne vous souciez pas de conserver vos données.

  • si vous utilisez le RAID 5 ou le RAID 6 pour le stockage direct, arrêtez maintenant. Sauvegarder vos données, restructurer votre système RAID à RAID 10, et réessayez. Les RAID 5/6 sont sans espoir pour les performances d'écriture en vrac - bien qu'un bon contrôleur RAID avec un gros cache puisse aider.

  • si vous avez l'option d'utiliser un contrôleur RAID matériel avec une grande mémoire cache en écriture sur batterie, cela peut vraiment améliorer les performances en écriture pour les charges de travail avec beaucoup de propagations. Cela n'aide pas autant si vous utilisez async commit avec un commit_delay ou si vous faites moins de big transactions lors du chargement en vrac.

  • si possible, stockez WAL ( pg_xlog ) sur un disque / tableau de disques séparé. Il y a peu d'intérêt à utiliser un système de fichiers séparé 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 il a peu d'effet si vous utilisez une table non compilée comme cible de charge de données.

vous pouvez également être intéressé par optimize PostgreSQL for fast testing .

380
répondu Craig Ringer 2017-05-23 12:18:30

Utiliser COPY table TO ... WITH BINARY qui est, selon la documentation est " un peu plus rapide que le texte et CSV ."Faites ceci seulement 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, en utilisant psycopg2 avec l'entrée binaire .

10
répondu Mike T 2017-05-23 11:55:03

en plus de L'excellent post de Craig Ringer et de l'article de blog de depesz, si vous souhaitez accélérer vos inserts par L'intermédiaire de L'interface ODBC ( psqlodbc ) en utilisant des inserts préétablis à l'intérieur d'une transaction, Il ya quelques choses supplémentaires que vous devez faire pour le faire fonctionner rapidement:

  1. définissez le niveau d'erreurs de retour en arrière à" Transaction "en spécifiant Protocol=-1 dans la chaîne de connexion. Par défaut psqlodbc utilise " Statement" niveau, qui crée un SAVEPOINT pour chaque instruction plutôt qu'une transaction entière, rendant les inserts plus lents.
  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 l'instruction insert entière avec chaque ligne insérée.
  3. Désactiver l'auto-commit sur chaque relevé à l'aide de SQLSetConnectAttr(conn, SQL_ATTR_AUTOCOMMIT, reinterpret_cast<SQLPOINTER>(SQL_AUTOCOMMIT_OFF), 0);
  4. une fois que toutes les lignes ont été insérées, transaction utilisant 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 insert non préparées, de sorte que pour obtenir l'insert le plus rapide, il faut coder les étapes ci-dessus manuellement.

8
répondu Maxim Egorushkin 2017-08-22 11:43:58

j'ai passé environ 6 heures sur le même sujet aujourd'hui. Les Inserts vont à une vitesse "régulière" (moins de 3sec par 100K) jusqu'à 5MI (sur un total de 30MI) de lignes et puis la performance descend drastiquement (jusqu'à 1min par 100K).

Je ne vais pas énumérer toutes les choses qui n'ont pas fonctionné et couper directement à la rencontre.

i a laissé tomber une clé primaire sur la table cible (qui était un GUID) et mes 30mi ou lignes heureusement à une vitesse constante de moins de 3seca par 100K.

2
répondu Dennis 2018-08-10 23:19:26

pour une performance d'Insertion optimale, désactivez l'index si c'est une option pour vous. Autre que cela, un meilleur matériel (disque, mémoire) est également utile

0
répondu Icarus 2012-08-30 22:43:06

j'ai également rencontré ce problème de performance d'insertion. Ma solution est de lancer quelques routines de go pour finir le travail d'insertion. Dans l'intervalle, SetMaxOpenConns devrait recevoir un numéro approprié, sinon trop d'erreurs de connexion ouverte seraient signalées. 

db, _ := sql.open() 
db.SetMaxOpenConns(SOME CONFIG INTEGER NUMBER) 
var wg sync.WaitGroup
for _, query := range queries {
    wg.Add(1)
    go func(msg string) {
        defer wg.Done()
        _, err := db.Exec(msg)
        if err != nil {
            fmt.Println(err)
        }
    }(query)
}
wg.Wait()

La vitesse de chargement est beaucoup plus rapide pour mon projet. Ce morceau de code vient de donner une idée de comment ça marche. Les lecteurs devraient pouvoir le modifier facilement.

0
répondu Patrick 2018-03-13 21:29:04