Comment fonctionne la copie et pourquoi est-elle beaucoup plus rapide que L'insertion?
Aujourd'hui j'ai passé ma journée à améliorer les performances de mon script Python qui pousse des données dans ma base de données Postgres. J'ai déjà de l'insertion d'enregistrements en tant que telle:
query = "INSERT INTO my_table (a,b,c ... ) VALUES (%s, %s, %s ...)";
for d in data:
cursor.execute(query, d)
j'ai alors réécrit mon script pour qu'il crée un fichier en mémoire que celui utilisé pour Postgres' COPY commande, qui me permet de copier des données d'un fichier vers ma table:
f = StringIO(my_tsv_string)
cursor.copy_expert("COPY my_table FROM STDIN WITH CSV DELIMITER AS E't' ENCODING 'utf-8' QUOTE E'b' NULL ''", f)
COPY la méthode était incroyablement plus rapide.
METHOD | TIME (secs) | # RECORDS
=======================================
COPY_FROM | 92.998 | 48339
INSERT | 1011.931 | 48377
mais je n'en trouve pas les raisons pour lesquelles? Comment fonctionne différemment d'un multiligne INSERT de sorte qu'il le rend tellement plus rapide?
Voir test ainsi:
# original
0.008857011795043945: query_builder_insert
0.0029380321502685547: copy_from_insert
# 10 records
0.00867605209350586: query_builder_insert
0.003248929977416992: copy_from_insert
# 10k records
0.041108131408691406: query_builder_insert
0.010066032409667969: copy_from_insert
# 1M records
3.464181900024414: query_builder_insert
0.47070908546447754: copy_from_insert
# 10M records
38.96936798095703: query_builder_insert
5.955034017562866: copy_from_insert
3 réponses
il y a un certain nombre de facteurs en jeu ici:
- latence du réseau et retards aller-retour
- Par relevé de frais généraux dans PostgreSQL
- les changements de Contexte et de planificateur de retard
COMMITcoûts, si pour les gens qui font un commit par insert (vous n'êtes pas)COPY-optimisations spécifiques pour le chargement en vrac
latence du réseau
si le serveur est distant, vous pourriez "payer" un par énoncé temps fixe "prix" de, disons, 50ms (1/20ème de seconde). Ou bien plus encore pour certains DBs hébergés dans le cloud. Puisque le prochain insert ne peut pas commencer avant que le dernier ne soit terminé avec succès, cela signifie votre maximum le taux d'inserts est de 1000/lignes aller-retour-latence-en-ms par seconde. À une latence de 50ms ("ping time"), c'est 20 lignes/seconde. Même sur un serveur local, ce délai est non nul. Wheras COPY il suffit de remplir le TCP envoyer et recevoir windows, et les lignes de flux aussi vite que le DB pouvez leur écrire et le réseau peut les transférer. Il n'est pas affecté par la latence beaucoup, et pourrait être insérant des milliers de lignes par seconde sur le même lien de réseau.
coûts par relevé en PostgreSQL
il y a aussi des coûts d'analyse, de planification et d'exécution d'un relevé dans PostgreSQL. Il doit prendre des serrures, ouvrir des dossiers de relation, chercher des index, etc. COPY essaie de faire tout cela une fois, au début, puis concentre-toi sur le chargement des lignes aussi vite que possible.
coûts de changement de tâche/contexte
il y a d'autres coûts de temps payés en raison du système d'exploitation qui doit passer entre les postgres en attente d'une rangée pendant que votre application la prépare et l'envoie, et puis votre application en attente de la réponse de postgres pendant que postgres traite la rangée. Chaque fois que vous passer de l'un à l'autre, vous perdez un peu de temps. Plus de temps est potentiellement perdu à suspendre et à reprendre divers états de noyau de bas niveau lorsque les processus entrent et laisser les états d'attente.
Manquant sur la COPIE d'optimisations
en plus de tout cela, COPY a quelques optimisations qu'il peut utiliser pour certains types de charges. S'il n'y a pas de clé générée et que les valeurs par défaut sont des constantes par exemple, il peut les pré-calculer et contourner complètement l'exécuteur, en chargeant rapidement les données dans la table à un niveau inférieur qui saute une partie du travail normal de PostgreSQL entièrement. Si vous CREATE TABLE ou TRUNCATE dans la même transaction vous COPY, il peut le faire encore plus d'astuces pour accélérer le chargement en contournant la comptabilité des transactions normale nécessaire dans une base de données multi-clients.
malgré cela, PostgreSQL est COPY pourrait encore faire beaucoup plus pour accélérer les choses, des choses qu'il ne sait pas encore comment le faire. Il pourrait automatiquement sauter des mises à jour d'index puis reconstruire des index si vous changez plus qu'une certaine proportion de la table. Il pourrait effectuer des mises à jour d'index par lots. Beaucoup plus.
Valider coûts
une dernière chose à considérer est d'engager des coûts. Ce n'est probablement pas un problème pour vous, parce que psycopg2 par défaut pour ouvrir une transaction et ne pas s'engager jusqu'à ce que vous le lui demandiez. Sauf si vous lui avez dit d'utiliser autocommit. Mais pour de nombreux pilotes DB autocommit est la valeur par défaut. Dans de tels cas, vous faites un commit pour chaque INSERT. Cela signifie un disque flush, où le serveur s'assure qu'il écrit toutes les données en mémoire sur le disque et dit aux disques d'écrire leurs propres caches à le stockage persistant. Cela peut prendre un long temps, et varie beaucoup en fonction du matériel. Mon portable NVMe BTRFS basé sur le SSD peut faire seulement 200 fsyncs / seconde, vs 300.000 écritures non-synchronisées / seconde. Il ne chargera que 200 lignes / seconde! Certains serveurs ne peuvent faire que 50 fsyncs / seconde. Certains peuvent en faire 20 000. Donc, si vous devez vous engager régulièrement, essayez de charger et de vous engager par lots, faites des inserts à plusieurs rangées, etc. Parce que COPY un seul commit à la fin, les coûts de commit sont négligeables. Mais c'est aussi signifie COPY ne peut pas récupérer des erreurs à mi-chemin à travers les données; il défait l'ensemble de la charge globale.
la copie utilise la charge en vrac, ce qui signifie qu'elle insère plusieurs lignes à chaque fois, alors que l'insert simple, fait un insert à la fois, cependant vous pouvez insérer plusieurs lignes avec insert suivant la syntaxe:
insert into table_name (column1, .., columnn) values (val1, ..valn), ..., (val1, ..valn)
pour plus d'informations sur l'utilisation de la charge globale, reportez-vous par exemple à le moyen le plus rapide de charger des lignes de 1m en postgresql par Daniel Westermann.
la question du nombre de lignes que vous devez insérer à la fois, dépend de la longueur de la ligne, une bonne règle empirique est pour insérer 100 ligne par instruction insert.
insérez des INSERTs dans une transaction pour accélérer.
Essais en bash sans transaction:
> time ( for((i=0;i<100000;i++)); do echo 'INSERT INTO testtable (value) VALUES ('$i');'; done ) | psql root | uniq -c
100000 INSERT 0 1
real 0m15.257s
user 0m2.344s
sys 0m2.102s
et avec transaction:
> time ( echo 'BEGIN;' && for((i=0;i<100000;i++)); do echo 'INSERT INTO testtable (value) VALUES ('$i');'; done && echo 'COMMIT;' ) | psql root | uniq -c
1 BEGIN
100000 INSERT 0 1
1 COMMIT
real 0m7.933s
user 0m2.549s
sys 0m2.118s