Обзор

Я пытаюсь улучшить производительность наших запросов к базе данных для SQLAlchemy. Мы используем psycopg2. В нашей производственной системе мы выбрали Java, потому что она просто быстрее как минимум на 50%, если не ближе к 100%. Так что я надеюсь, что кто-то из сообщества Stack Overflow найдет способ улучшить мою производительность.

Я думаю, что моим следующим шагом будет исправление библиотеки psycopg2, чтобы она работала как драйвер JDBC. Если это так, и кто-то уже сделал это, это было бы хорошо, но я надеюсь, что у меня все еще есть настройки или корректировка рефакторинга, которые я могу сделать из Python.

Подробности

У меня есть простой "SELECT *" FROM someLargeDataSetTable "запрос выполняется. Размер набора данных составляет ГБ. Краткая диаграмма производительности выглядит следующим образом:

Таблица времени

        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 (50MB)  |    4s |            5s |           4.6s |     8s
 1,000,000 (510MB) |   30s |           50s |            50s |  1m32s  
10,000,000 (5.1GB) | 4m44s |         7m55s |          6m39s |    n/a
-------------------------------------------------------------------- 
 5,000,000 (2.6GB) | 2m30s |         4m45s |          3m52s | 14m22s
-------------------------------------------------------------------- 
[1] - With the processrow function
[2] - Without the processrow function (direct dump)

Я мог бы добавить больше (наши данные могут достигать терабайт), но я думаю, что изменение наклона очевидно из данных. JDBC просто работает значительно лучше по мере увеличения размера набора данных. Некоторые примечания ...

Примечания к таблице синхронизации:

• Данные являются приблизительными, но они должны дать вам представление об объеме данных.
• Я использую инструмент 'time' из командной строки Linux bash.
• Время - это время настенных часов (то есть реальное).
• I Я использую Python 2.6.6, и я использую python -u
• Размер выборки 10,000
• Я не особо беспокоюсь о времени Psql, это просто ориентир. Возможно, я неправильно установил для него fetchsize.
• Я также действительно не беспокоюсь о времени, меньшем размера выборки, поскольку менее 5 секунд незначительно для моего приложения.
• Java и Psql, похоже, занимают около 1 ГБ ресурсы памяти; Python больше похож на 100 МБ (ура !!).
• Я использую библиотеку [cdecimals] .
• Я заметил [недавняя статья] , в которой обсуждается нечто похожее на этот. Похоже, что дизайн драйвера JDBC полностью отличается от дизайна psycopg2 (что, на мой взгляд, довольно раздражает, учитывая разницу в производительности).
• Мой вариант использования в основном состоит в том, что мне нужно выполнять ежедневный процесс (примерно с 20 000 различных шагов). ... несколько запросов) для очень больших наборов данных, и у меня есть очень конкретное время, когда я могу завершить этот процесс. Используемая нами Java - это не просто JDBC, это «умная» оболочка поверх движка JDBC ... мы не хотим использовать Java, и мы хотели бы прекратить использовать ее «умную» часть.
• Я использую один из блоков нашей производственной системы (базу данных и внутренний процесс) для выполнения запроса. Так что это лучшее время для нас. У нас есть блоки контроля качества и разработки, которые работают намного медленнее, и дополнительное время запроса может стать значительным.

testSqlAlchemy.py

#!/usr/bin/env python
# testSqlAlchemy.py
import sys
try:
    import cdecimal
    sys.modules["decimal"]=cdecimal
except ImportError,e:
    print >> sys.stderr, "Error: cdecimal didn't load properly."
    raise SystemExit
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

def processrow (row,delimiter="|",null="\N"):
    newrow = []
    for x in row:
        if x is None:
            x = null
        newrow.append(str(x))
    return delimiter.join(newrow)

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

with open("test.sql","r") as queryFD:
   with open("/dev/null","w") as nullDev:
        query = session.execute(queryFD.read())
        cur = query.cursor
        while cur.statusmessage not in ['FETCH 0','CLOSE CURSOR']:
            for row in query.fetchmany(fetchsize):
                print >> nullDev, processrow(row)

По истечении времени Я также запустил cProfile, и это дамп худших нарушителей:

Профиль времени (с processrow)

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

         415757706 function calls (415756424 primitive calls) in 563.923 CPU seconds

   Ordered by: cumulative time

   ncalls  tottime  percall  cumtime  percall filename:lineno(function)
        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 {method 'fetchmany' of 'psycopg2._psycopg.cursor' objects}
 10000000  145.459    0.000  209.147    0.000 testSqlAlchemy.py:13(processrow)

Timing Profile (без processrow)

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

         305460312 function calls (305459030 primitive calls) in 536.368 CPU seconds

   Ordered by: cumulative time

   ncalls  tottime  percall  cumtime  percall filename:lineno(function)
        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 {method 'fetchmany' of 'psycopg2._psycopg.cursor' objects}
 10000000   32.904    0.000  172.802    0.000 base.py:2246(__repr__)

Заключительные комментарии

К сожалению, функция processrow должна оставаться это способ в SQLAlchemy указать null = 'userDefinedValueOrString' и delimiter = 'userDefinedValueOrString' вывода. Java, которую мы используем в настоящее время, уже делает это, поэтому сравнение (с processrow) должно быть яблоком с яблоком. Если есть способ улучшить производительность Processrow или SQLAlchemy с помощью чистого Python или настройки параметров, меня это очень интересует.