Amélioration des performances des requêtes Postyres psycopg2 pour Python avec le même niveau de pilote JDBC de Java

Question

Vue d'ensemble

Je suis d'essayer d'améliorer la performance de nos requêtes de base de données de SQLAlchemy. Nous utilisons psycopg2. Dans notre système de production, nous sommes en choisissant d'aller à Java, car il est tout simplement plus rapide d'au moins 50%, si ce n'est plus près de 100%. Donc je suis en espérant que quelqu'un dans le Débordement de la Pile de la communauté a une manière d'améliorer mes performances.

Je pense que ma prochaine étape va être à la fin de la correction du psycopg2 de la bibliothèque à se comporter comme le pilote JDBC. Si c'est le cas et que quelqu'un l'a déjà fait, ce serait bien, mais je suis en espérant que j'ai encore des paramètres ou refactoring tweak je peux le faire à partir de Python.

Détails

J'ai un simple "SELECT * from someLargeDataSetTable" requête en cours d'exécution. Le dataset est Sgb dans la taille. Un rapide tableau des performances est comme suit:

Calendrier De Table

 Dossiers | JDBC | SQLAlchemy[1] | SQLAlchemy[2] | Psql
-------------------------------------------------------------------- 
 1 (4 ko) | 200 ms | 300 ms | 250ms | 10ms
 10 (8 ko) | 200 ms | 300 ms | 250ms | 10ms
 100 (88kB) | 200 ms | 300 ms | 250ms | 10ms
 De 1 000 (600 ko) | 300 ms | 300 ms | 370ms | 100ms
 De 10 000 (6 MO) | 800ms | 830ms | 730ms | 850ms 
 De 100 000 (50 MO) | 4s | 5s | 4,6 s | 8s
 De 1 000 000 (510 MO) | 30 | 50 | 50 | 1m32s 
10 000 000 de (5.1 GO) | 4m44s | 7m55s | 6m39s | n/a
-------------------------------------------------------------------- 
 De 5 000 000 (2,6 GO) | 2m30s | 4m45s | 3m52s | 14m22s
-------------------------------------------------------------------- 
[1] - Avec la fonction processrow
[2] - Sans le processrow fonction (copie directe)

Je pourrais ajouter de plus (nos données peuvent être autant que téraoctets), mais je pense que changer la pente est évident à partir des données. JDBC juste fonctionne nettement mieux que le jeu de données de taille augmente. Quelques notes...

Calendrier De Table Notes:

• Le datasizes sont approximatifs, mais ils devraient vous donner une idée de la quantité de données.
• Je suis en utilisant le "temps" de l'outil à partir d'un Linux en ligne de commande bash.
• Les temps sont à l'horloge murale fois (c'est à dire vrai).
• Je suis à l'aide de Python 2.6.6 et je suis en cours d'exécution avec python -u
• Taille de l'extraction est de 10 000
• Je ne suis pas vraiment inquiet au sujet de la Psql le moment, il est là juste comme un point de référence. Je ne peut pas avoir correctement défini fetchsize pour elle.
• Je suis aussi vraiment pas inquiet sur le calendrier ci-dessous la taille de l'extraction de moins de 5 secondes est négligeable à ma demande.
• Java et Psql semblent prendre 1 go de mémoire de ressources; Python est plus de 100 MO (yay!!).
• Je suis en utilisant le [cdecimals] de la bibliothèque.
• J'ai remarqué un [article récent] discuter de quelque chose de semblable. Il semble que le pilote JDBC de conception est totalement différente de la psycopg2 conception (qui je pense est plutôt gênant compte tenu de la différence de performances).
• Mon cas d'utilisation est fondamentalement que j'ai pour exécuter un processus quotidien (avec environ 20 000 différentes étapes... de multiples requêtes) sur de très grands ensembles de données et j'ai une question très spécifique de la fenêtre de temps où je peux terminer ce processus. Le Java que nous utilisons n'est pas simplement JDBC, c'est un "smart" wrapper sur le dessus de la JDBC moteur... nous ne voulons pas utiliser Java et nous aimerions arrêter à l'aide de la fonction "smart" de la partie.
• Je suis l'aide de l'un de notre système de production de boîtes (base de données et backend processus) pour exécuter la requête. Donc, c'est notre meilleur des cas, le timing. Nous avons QA et boîtes de Dev qui s'exécutent beaucoup plus lent et le moment de la requête peut devenir important.

testSqlAlchemy.py

#!/usr/bin/env python
# testSqlAlchemy.py
import sys
essayez:
 importation cdecimal
sys.modules["décimal"]=cdecimal
sauf ImportError,e:
 impression > > > sys.stderr, "Erreur: cdecimal ne se charge pas correctement."
 soulever SystemExit
de sqlalchemy importation create_engine
de sqlalchemy.orm importation sessionmaker

def processrow (ligne,delimiter="|",null="\N"):
 newrow = []
 pour x en ligne:
 si x n'est:
 x = null
newrow.append(str(x))
 retour délimiteur.join(newrow)

fetchsize = 10000
connectionString = "postgresql+psycopg2://usr:pass@server:port/db"
fra = create_engine(connectionString, server_side_cursors=True)
session = sessionmaker(bind=fra)()

avec open("test.sql","r") comme queryFD:
 avec open("/dev/null","w") comme nullDev:
 query = session.execute(queryFD.read())
 cur = requête.curseur
 tandis que cur.statusmessage pas dans ['FETCH 0','FERMER le CURSEUR']:
 pour la ligne de requête.fetchmany(fetchsize):
 impression > > > nullDev, processrow(ligne)

Après la synchronisation, j'ai aussi couru un cProfile et c'est le cliché des pires délinquants:

Synchronisation de Profil (avec processrow)

Ven Mar 4 13:49:45 2011 sqlAlchemy.prof

 415757706 appels de fonction (415756424 primitive appels) en 563.923 CPU secondes

 Commandé par: temps cumulé

 ncalls tottime percall cumtime percall nom de fichier:lineno(fonction)
 1 0.001 0.001 563.924 563.924 {execfile}
 1 25.151 25.151 563.924 563.924 testSqlAlchemy.py:2()
 1001 0.050 0.000 329.285 0.329 de base.py:2679(fetchmany)
 1001 5.503 0.005 314.665 0.314 de base.py:2804(_fetchmany_impl)
 10000003 4.328 0.000 307.843 0.000 base.py:2795(_fetchone_impl)
 10011 0.309 0.000 302.743 0.030 de base.py:2790(__tampon_lignes)
 10011 233.620 0.023 302.425 0.030 {méthode 'fetchmany' de 'psycopg2._psycopg.curseur' objets}
 10000000 145.459 0.000 209.147 0.000 testSqlAlchemy.py:13(processrow)

Synchronisation de Profil (sans processrow)

Ven Mar 4 14:03:06 2011 sqlAlchemy.prof

 305460312 appels de fonction (305459030 primitive appels) en 536.368 CPU secondes

 Commandé par: temps cumulé

 ncalls tottime percall cumtime percall nom de fichier:lineno(fonction)
 1 0.001 0.001 536.370 536.370 {execfile}
 1 29.503 29.503 536.369 536.369 testSqlAlchemy.py:2()
 1001 0.066 0.000 333.806 0.333 de base.py:2679(fetchmany)
 1001 5.444 0.005 318.462 0.318 de base.py:2804(_fetchmany_impl)
 10000003 4.389 0.000 311.647 0.000 base.py:2795(_fetchone_impl)
 10011 0.339 0.000 306.452 0.031 de base.py:2790(__tampon_lignes)
 10011 235.664 de 0,024 306.102 0.031 {méthode 'fetchmany' de 'psycopg2._psycopg.curseur' objets}
 10000000 32.904 0.000 172.802 0.000 base.py:2246(__repr__)

Derniers Commentaires

Malheureusement, la processrow fonction des besoins de séjour, sauf si il y a un chemin à l'intérieur de SQLAlchemy pour spécifier la valeur null = 'userDefinedValueOrString" et delimiter = 'userDefinedValueOrString' de la sortie. Le Java que nous utilisons actuellement déjà fait cela, donc la comparaison (avec processrow) nécessaires pour être des pommes avec des pommes. Si il y a un moyen pour améliorer les performances de processrow ou SQLAlchemy pur Python ou les paramètres de tweak, je suis très intéressé.

Answer 1

Ce n'est pas une réponse hors de la boîte, avec tous les clients/db choses vous devrez peut-être faire un peu de travail pour déterminer exactement ce qui ne va pas

sauvegarde de postgresql.conf changer

log_min_duration_statement to 0 
log_destination = 'csvlog'              # Valid values are combinations of      
logging_collector = on                # Enable capturing of stderr and csvlog 
log_directory = 'pg_log'                # directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,        
debug_print_parse = on
debug_print_rewritten = on
debug_print_plan output = on
log_min_messages = info (debug1 for all server versions prior to 8.4)

Arrêtez et redémarrez votre serveur de base de données ( rechargement ne peut pas ramasser les modifications ) Reproduire vos tests de veiller à ce que l'heure du serveur et de client temps de match et que vous enregistrez le temps de démarrage etc.

copiez le fichier de log off une importation dans l'éditeur de votre choix (excel ou un autre tableur peut être utile pour obtenir l'avance de manipulation pour sql & plans etc)

examinons maintenant les horaires à partir du côté serveur et note:

• est sql signalé sur le serveur les mêmes dans chaque cas

• si la même chose vous devriez avoir les mêmes timings

• le client est-il de la génération d'un curseur plutôt que de passer à sql

• est un pilote de faire beaucoup de coulée/convertir entre les jeux de caractères ou implicite de la conversion d'autres types de données telles que les dates ou les horodatages.

et ainsi de suite

Le plan de données seront inclus pour l'intégralité, ce qui peut les informer si il y a des brutes différences dans le SQL soumis par les clients.

Answer 2

Les trucs ci-dessous est probablement visée ci-dessus et au-delà de ce que vous avez à l'esprit ou ce qui est jugé acceptable dans votre environnement, mais je vais mettre l'option sur la table, juste au cas où.

1. C'est la destination de chaque SELECT votre test.sql vraiment un simple |-séparés fichier de résultats?
2. Est non-portabilité (Postgres-spécificité) est-il acceptable?
3. Est votre backend Postgresql 8.2 ou plus récent?
4. Va le script de s'exécuter sur le même hôte que la base de données back-end, ou serait-il acceptable pour générer l' |-séparés résultats de fichier(s) à partir du backend (par exemple pour une action?)

Si la réponse à toutes ces questions est oui, alors vous pouvez transformer votre SELECT ... des déclarations d' COPY ( SELECT ... ) TO E'path-to-results-file' WITH DELIMITER '|' NULL E'\\N'.

Answer 3

Une alternative pourrait être d'utiliser ODBC. Cela suppose que le pilote ODBC Python fonctionne bien.

PostgreSQL a des pilotes ODBC pour Windows et Linux.

Answer 4

Comme quelqu'un qui a programmé principalement en assembleur, il y a une chose qui colle comme une évidence. Vous êtes de perdre du temps dans les frais généraux et les frais généraux est ce qui doit aller.

Plutôt que d'utiliser python, qui s'enroule dans quelque chose d'autre qui s'intègre avec quelque chose qui est un C wrapper autour de la DB.... il suffit d'écrire le code en C. je veux dire, combien de temps cela peut-il prendre? Postgres n'est pas difficile de faire l'interface avec (bien au contraire). C est une langue facile. Les opérations que vous effectuez semble assez simple. Vous pouvez également utiliser le SQL embarqué dans du C, c'est juste une question de pré-compilation. Pas besoin de traduire ce que vous pensiez - il suffit de l'écrire là, le long de avec le C et l'utilisation fournis ECPG compilateur (lire postgres manuel le chapitre 29 de l'iirc).

Prendre autant de betweeny interfacey de choses que vous pouvez, couper l'homme du milieu et de faire parler de la base de données en mode natif. Il me semble que, en essayant de rendre le système plus simple que vous êtes en réalité en la rendant plus compliqué qu'il doit être. Quand les choses deviennent vraiment pénibles, j'ai l'habitude de me poser la question "Quelles sont les bits du code suis-je plus peur de la toucher?" - qui, généralement, les points de moi de ce qui doit changer.

Désolé pour le babillage, mais peut-être un pas en arrière et un peu d'air frais va aider ;)