Amélioration des performances des requêtes Postgres psycopg2 pour Python au même niveau que celles du pilote JDBC Java

Vue d’ensemble

Je tente d’améliorer les performances de nos requêtes de firebase database pour SQLAlchemy. Nous utilisons psycopg2. Dans notre système de production, nous choisissons d’utiliser Java car il est tout simplement plus rapide d’au moins 50%, voire plus proche de 100%. J’espère donc que quelqu’un dans la communauté Stack Overflow pourra améliorer mes performances.

Je pense que ma prochaine étape sera de finir par patcher la bibliothèque psycopg2 pour qu’elle se comporte comme le pilote JDBC. Si tel est le cas et que quelqu’un l’a déjà fait, ce serait bien, mais j’espère avoir encore un réglage ou un ajustement que je peux faire à partir de Python.

Détails

J’ai une simple requête “SELECT * FROM someLargeDataSetTable” en cours d’exécution. Le jeu de données a une taille en Go. Un tableau de performance rapide est comme suit:

Table de chronométrage

         Records |  JDBC |  SQLAlchemy [1] |  SQLAlchemy [2] |  Psql
 -------------------------------------------------- ------------------ 
          1 (4kB) |  200ms |  300ms |  250ms |  10ms
         10 (8kB) |  200ms |  300ms |  250ms |  10ms
        100 (88kB) |  200ms |  300ms |  250ms |  10ms
      1,000 (600kB) |  300ms |  300ms |  370ms |  100ms
     10,000 (6MB) |  800ms |  830ms |  730ms |  850ms  
    100 000 (50 Mo) |  4s |  5s |  4.6s |  8s
  1 000 000 (510 Mo) |  30s  50s |  50s |  1m32s  
 10 000 000 (5,1 Go) |  4m44s |  7m55s |  6m39s |  n / a
 -------------------------------------------------- ------------------ 
  5 000 000 (2,6 Go) |  2m30s |  4m45s |  3m52s |  14m22s
 -------------------------------------------------- ------------------ 
 [1] - Avec la fonction processrow
 [2] - Sans la fonction processrow (vidage direct)

Je pourrais en append d’autres (nos données peuvent atteindre des téraoctets), mais je pense que l’évolution des données est évidente. JDBC fonctionne beaucoup mieux avec la taille du jeu de données. Quelques notes…

Notes de tableau de chronométrage:

  • Les données sont approximatives, mais elles devraient vous donner une idée de la quantité de données.
  • J’utilise l’outil ‘time’ à partir d’une ligne de commande Linux bash.
  • Les temps sont les heures d’horloge murale (c.-à-d. Réelles).
  • J’utilise Python 2.6.6 et je cours avec python -u
  • La taille de récupération est de 10 000
  • Je ne m’inquiète pas vraiment du timing Psql, il est juste là comme un sharepoint référence. Je n’ai peut-être pas correctement défini la taille des fetchs.
  • Je ne m’inquiète pas vraiment du fait que le délai de récupération inférieur à 5 secondes est négligeable pour mon application.
  • Java et Psql semblent prendre environ 1 Go de ressources mémoire; Python ressemble plus à 100 Mo (yay !!).
  • J’utilise la bibliothèque [cdecimals] .
  • J’ai remarqué un [article récent] discutant de quelque chose de similaire. Il semble que la conception du pilote JDBC soit totalement différente de la conception de psycopg2 (ce qui, à mon avis, est plutôt ennuyeux compte tenu de la différence de performance).
  • Mon cas pratique est fondamentalement que je dois exécuter un processus quotidien (avec environ 20 000 étapes différentes … plusieurs requêtes) sur des ensembles de données très volumineux et j’ai une fenêtre de temps très spécifique pour terminer ce processus. Le Java que nous utilisons n’est pas simplement JDBC, c’est un wrapper “intelligent” sur le moteur JDBC … nous ne voulons pas utiliser Java et nous aimerions arrêter d’utiliser la partie “intelligente”.
  • J’utilise l’une des boîtes de notre système de production (firebase database et processus backend) pour exécuter la requête. C’est donc notre meilleur timing. Nous avons des boîtes d’assurance qualité et de développement beaucoup plus lentes et le temps de requête supplémentaire peut devenir important.

testSqlAlchemy.py

 #! / usr / bin / env python
 # testSqlAlchemy.py
 import sys
 essayer:
     import cdecimal
     sys.modules ["decimal"] = cdecimal
 sauf ImportError, e:
     print >> sys.stderr, "Erreur: cdecimal ne s'est pas chargé correctement."
     augmenter SystemExit
 à partir de sqlalchemy import create_engine
 à partir de sqlalchemy.orm import sessionmaker

 def processrow (row, delimiter = "|", null = "\ N"):
     newrow = []
     pour x en ligne:
         si x est aucun:
             x = null
         newrow.append (str (x))
     retourne delimiter.join (newrow)

 taille de fetch = 10000
 connectionSsortingng = "postgresql + psycopg2: // usr: pass @ server: port / db"
 eng = create_engine (connectionSsortingng, server_side_cursors = True)
 session = faiseur de session (bind = eng) ()

 avec open ("test.sql", "r") comme queryFD:
    avec open ("/ dev / null", "w") comme nullDev:
         query = session.execute (queryFD.read ())
         cur = query.cursor
         tandis que cur.statusmessage pas dans ['FETCH 0', 'CLOSE CURSOR']:
             pour la ligne dans query.fetchmany (taille maximale):
                 print >> nullDev, processrow (row)

Après le chronométrage, j’ai également lancé un profil cProfile et c’est le vidage des pires délinquants:

Profil de synchronisation (avec processrow)

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

          415757706 appels de fonction (appels primitifs 415756424) dans 563.923 secondes du processeur

    Ordonné par: temps cumulé

    ncalls tottime percall nom de fichier cumall: 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 base.py:2679(fetchmany)
      1001 5,503 0,005 314,665 0,314 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 base.py:2790(__buffer_rows)
     10011 233.620 0.023 302.425 0.030 {méthode 'fetchmany' des objects 'psycopg2._psycopg.cursor'}
  10000000 145.459 0.000 209.147 0.000 testSqlAlchemy.py:13(processrow)

Profil de synchronisation (sans processrow)

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

          305460312 appels de fonction (appels primitifs 305459030) en secondes de processeur 536.368

    Ordonné par: temps cumulé

    ncalls tottime percall nom de fichier cumall: 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 base.py:2679(fetchmany)
      1001 5,444 0,005 318,462 0,318 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 base.py:2790(__buffer_rows)
     10011 235.664 0,024 306,102 0,031 {méthode 'fetchmany' des objects 'psycopg2._psycopg.cursor'}
  10000000 32.904 0.000 172.802 0.000 base.py:2246(__repr__)

Commentaires finaux

Malheureusement, la fonction processrow doit restr sauf si SQLAlchemy a la possibilité de spécifier null = ‘userDefinedValueOrSsortingng’ et delimiter = ‘userDefinedValueOrSsortingng’ de la sortie. Le Java que nous utilisons actuellement le fait déjà, donc la comparaison (avec processrow) devait être des pommes aux pommes. S’il existe un moyen d’améliorer les performances de processrow ou de SQLAlchemy avec un Python pur ou un réglage des parameters, je suis très intéressé.

    Ce n’est pas une réponse prête à l’emploi, avec tous les trucs client / db que vous pourriez avoir besoin de faire pour déterminer exactement ce qui ne va pas.

    sauvegarde postgresql.conf changement

     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 firebase database (le rechargement risque de ne pas prendre en compte les modifications)

    copier le fichier journal d’une importation dans l’éditeur de votre choix (Excel ou une autre feuille de calcul peut être utile pour obtenir des manipulations préalables pour sql & plans, etc.)

    Maintenant, examinez les délais du côté serveur et notez:

    • est le SQL signalé sur le serveur le même dans chaque cas

    • si le même, vous devriez avoir les mêmes horaires

    • le client génère-t-il un curseur plutôt que de transmettre sql

    • est un pilote effectuant beaucoup de transtypages / conversions entre les jeux de caractères ou la conversion implicite d’autres types tels que les dates ou les horodatages.

    etc

    Les données du plan seront incluses pour être complet, cela peut informer s’il existe des différences flagrantes dans le SQL soumis par les clients.

    Les éléments ci-dessous visent probablement au-delà de ce que vous avez à l’esprit ou de ce qui est jugé acceptable dans votre environnement, mais je mettrai l’option sur la table au cas où.

    1. La destination de chaque SELECT dans votre test.sql vraiment simple | fichier de résultats séparés?
    2. La non-portabilité (spécificité de Postgres) est-elle acceptable?
    3. Est-ce que votre backend Postgres 8.2 ou plus récent?
    4. Le script s’exécutera-t-il sur le même hôte que le backend de la firebase database ou serait-il acceptable de générer le | fichier (s) de résultats séparé (s) à partir du backend (par exemple à un partage?)

    Si la réponse à toutes les questions ci-dessus est oui , alors vous pouvez transformer vos SELECT ... en COPY ( SELECT ... ) TO E'path-to-results-file' WITH DELIMITER '|' NULL E'\\N' COPY ( SELECT ... ) TO E'path-to-results-file' WITH DELIMITER '|' NULL E'\\N' .

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

    PostgreSQL a des pilotes ODBC pour Windows et Linux.

    En tant que programmeur principalement en assembleur, il y a une chose qui ressort clairement. Vous perdez du temps dans les frais généraux, et les frais généraux sont ce qui doit aller.

    Plutôt que d’utiliser python, qui s’intègre à quelque chose qui s’intègre à quelque chose qui est un wrapper C autour de la firebase database… écrivez simplement le code en C. Je veux dire, combien de temps cela peut-il prendre? Postgres n’est pas difficile à interfacer (bien au contraire). C est une langue facile. Les opérations que vous effectuez semblent assez simples. Vous pouvez également utiliser SQL intégré à C, il ne s’agit que d’une pré-compilation. Pas besoin de traduire ce que vous pensiez – écrivez-le juste avec le C et utilisez le compilateur ECPG fourni (lisez le manuel postgres chapitre 29 iirc).

    Enlevez autant de choses que possible entre les interfaces, supprimez l’intermédiaire et parlez à la firebase database en mode natif. Il me semble qu’en essayant de simplifier le système, vous le rendez plus compliqué que nécessaire. Quand les choses deviennent vraiment compliquées, je me pose généralement la question “Quel est le code que je crains le plus de toucher?” – cela m’indique généralement ce qui doit changer.

    Désolé pour avoir bavardé, mais peut-être un pas en arrière et de l’air frais aidera;)