performance - 将 Python 的 Postgres psycopg2 查询性能提高到与 Java 的 JDBC 驱动程序相同的水平

Question

概述

我正在尝试提高 SQLAlchemy 数据库查询的性能。我们正在使用 psycopg2。在我们的生产系统中，我们选择使用 Java，因为它的速度至少快了 50%，甚至接近 100%。因此，我希望 Stack Overflow 社区中的某个人能够找到一种方法来提高我的表现。

我认为我的下一步最终是修补 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)

我可以添加更多(我们的数据可以多达 TB)，但我认为从数据中可以明显看出斜率的变化。随着数据集大小的增加，JDBC 的性能显着提高。一些注释...

时序表注释:

• 数据大小是近似值，但它们应该能让您了解数据量。
• 我正在使用 Linux bash 命令行中的“时间”工具。
• 时间是挂钟时间(即真实时间)。
• 我使用的是 Python 2.6.6，并且使用 python -u 运行
• 获取大小为 10,000
• 我并不真正担心 Psql 时间，它只是作为引用点。我可能没有正确设置它的 fetchsize。
• 我也不担心低于提取大小的时间，因为对于我的应用程序来说，少于 5 秒的时间可以忽略不计。
• Java 和 Psql 似乎占用了大约 1GB 的内存资源； Python 更像是 100MB(耶!!)。
• 我正在使用[cdecimals]图书馆。
• 我注意到一个 [recent article]讨论与此类似的事情。看来 JDBC 驱动程序设计与 psycopg2 设计完全不同(考虑到性能差异，我认为这相当烦人)。
• 我的用例基本上是，我必须在非常大的数据集上运行日常流程(大约 20,000 个不同的步骤...多个查询)，并且我有一个非常特定的时间窗口来完成此过程。我们使用的 Java 不仅仅是 JDBC，它是 JDBC 引擎之上的“智能”包装器...我们不想使用 Java，并且希望停止使用它的“智能”部分。<
• 我正在使用我们的生产系统的一个盒子(数据库和后端进程)来运行查询。所以这是我们最好的时机。我们的 QA 和开发框运行速度要慢得多，额外的查询时间可能会变得很长。

testSqlAlchemy.py

#!/usr/bin/env python# testSqlAlchemy.pyimport systry:    import cdecimal    sys.modules["decimal"]=cdecimalexcept ImportError,e:    print >> sys.stderr, "Error: cdecimal didn't load properly."    raise SystemExitfrom sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmakerdef 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 = 10000connectionString = "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，这是最严重违规者的转储:

时序配置文件(带进程行)

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

最终评论

不幸的是，processrow 函数需要保留，除非 SQLAlchemy 中有一种方法可以指定输出的 null = 'userDefinedValueOrString' 和 delimiter = 'userDefinedValueOrString'。我们当前使用的 Java 已经做到了这一点，因此(与 processrow)需要进行同类比较。如果有一种方法可以通过纯 Python 或设置调整来提高 processrow 或 SQLAlchemy 的性能，我非常感兴趣。

Answer 1

这不是一个开箱即用的答案，对于所有客户端/数据库内容，您可能需要做一些工作来确定到底出了什么问题

备份 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 是否存在明显差异。