Улучшение производительности Postgres psycopg2 для Python на одном уровне с драйвером Java JDBC

Обзор

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

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

Подробнее

У меня есть простой запрос SELECT * FROM someLargeDataSetTable. Набор данных имеет размер GB. Таблица быстрой производительности выглядит следующим образом:

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

        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 просто выполняет значительно лучше, так как размер набора данных увеличивается. Некоторые примечания...

Примечания к таблице времени:

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

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:[email protected]: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, и это свалка худших нарушителей:

Профиль синхронизации (с технологическим процессом)

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)

Профиль синхронизации (без обработки)

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__)

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

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

Ответ 1

Это не ответ из коробки, со всеми материалами client/db вам может понадобиться сделать какую-то работу, чтобы точно определить, что такое amiss

резервное копирование postgresql.conf изменение

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)

Остановите и перезапустите сервер базы данных (перезагрузка может не забрать изменения) Воспроизводите свои тесты, гарантируя совпадение времени сервера и клиента и записывая время начала и т.д.

скопируйте файл журнала с импорта в выбранный вами редактор (excel или другая таблица могут быть полезны для получения предварительной обработки для sql и планов и т.д.)

теперь изучите тайминги со стороны сервера и обратите внимание:

- это sql, сообщаемый на сервере одинаково в каждом случае
если то же самое вы должны иметь одинаковые тайминги
- клиент, создающий курсор, а не передающий sql
- один из драйверов, делающий много кастингов/преобразований между наборами символов или неявное преобразование других типов, таких как даты или временные метки.

и т.д.

Данные плана будут включены для полноты, это может сообщить о наличии грубых различий в SQL, представленных клиентами.

Ответ 2

Нижеприведенный материал, вероятно, нацелен выше и выше того, что вы имеете в виду, или того, что считается приемлемым в вашей среде, но на всякий случай я поставлю опцию на стол.

Является ли назначение каждого SELECT в вашем test.sql действительно простейшем | -сепаратированном файле результатов?
Является ли неприемлемость (специфика Postgres) приемлемой?
Является ли ваш backend Postgres 8.2 или новее?
Будет ли script работать на том же хосте, что и бэкэнд базы данных, или было бы приемлемо создать -сегментированный файл результатов изнутри бэкэнд (например, для совместного использования?)

Если ответ на все приведенные выше вопросы да, то вы можете преобразовать свои операторы SELECT ... в COPY ( SELECT ... ) TO E'path-to-results-file' WITH DELIMITER '|' NULL E'\\N'.

Ответ 3

Альтернативой может быть использование ODBC. Это предполагает, что драйвер ODBC Python хорошо работает.

PostgreSQL имеет драйверы ODBC для Windows и Linux.

Ответ 4

Как кто-то, кто программировал в основном на ассемблере, есть одна вещь, которая проявляется как очевидная. Вы теряете время в накладных расходах, и накладные расходы - это то, что нужно сделать.

Вместо того, чтобы использовать python, который обертывается во что-то еще, что интегрируется с чем-то, что является оберткой C вокруг БД... просто напишите код на C. Я имею в виду, сколько времени это займет? Postgres нетрудно связать (совершенно наоборот). C - простая langauge. Операции, которые вы выполняете, выглядят довольно просто. Вы также можете использовать SQL, встроенный в C, это просто вопрос предварительной компиляции. Не нужно переводить то, о чем вы думали, - просто напишите его вместе с C и используйте прилагаемый компилятор ECPG (прочитайте главу 29 руководства по постгрессу).

Извлеките как можно больше средств интерфейса между собой, вырежьте среднего человека и поговорите с базой данных изначально. Мне кажется, что, пытаясь сделать систему проще, вы на самом деле делаете ее более сложной, чем она должна быть. Когда все становится очень грязным, я обычно задаю себе вопрос: "Какой бит кода я больше всего боюсь коснуться?" - это обычно указывает на то, что нужно изменить.

Извините за журчание, но, может быть, шаг назад и некоторый свежий воздух поможет;)