概述

我正在尝试提高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)

我可以添加更多（我们的数据可以多达太字节），但我认为从数据中可以看出改变斜率 . 随着数据集大小的增加，JDBC的表现会更好 . 一些笔记......

计时表备注：

数据量是近似值，但它们应该可以让您了解数据量 .
来自Linux bash命令行的
I 'm using the ' time'工具 .
时间是挂钟时间（即真实时间） .
我'm using Python 2.6.6 and I'我正在运行 python -u
获取大小为10,000
我'm not really worried about the Psql timing, it'仅作为参考点 . 我可能没有为它正确设置fetchsize .
我也真的不担心提取大小以下的时间，因为我的应用程序可以忽略不到5秒 .
Java和Psql似乎占用了大约1GB的内存资源; Python更像是100MB（yay !!） .
我正在使用[cdecimals]库 .
我注意到[recent article]讨论了类似的事情 . 似乎JDBC驱动程序设计与psycopg2设计完全不同（考虑到性能差异，我觉得这很烦人） .
我的用例基本上是我必须在非常大的数据集上运行每日过程（大约20,000个不同的步骤...多个查询），我有一个非常具体的时间窗口，我可以完成这个过程 . 我们使用的Java不仅仅是JDBC，它想要使用Java，我们想停止使用它的"smart"部分 .
I 'm using one of our production system' s框（数据库和后端进程）运行查询 . 所以这是我们最好的时机 . 我们有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: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)

计时配置文件（没有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'，否则processrow函数需要保持不变 . 我们目前使用的Java已经这样做了，所以比较（与processrow）需要苹果对苹果 . 如果有办法用纯Python或设置调整来提高processrow或SQLAlchemy的性能，我会非常感兴趣 .

4 回答

2
这不是一个开箱即用的答案，所有客户端/数据库的东西你可能需要做一些工作来确定究竟是什么错

备份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是否存在严重差异 .
回复于 2024-05-06T00:03:45+08:00
1
下面的内容可能超出您的想法或在您的环境中被认为可接受的东西，但我会把选项放在桌面上以防万一 .
- test.sql 中每个 SELECT 的目的地真的是一个简单的 | 分隔的结果文件吗？
- 不可移植性（Postgres特异性）是否可以接受？
- 你的后端是Postgres 8.2还是更新？
- 脚本是否与数据库后端在同一主机上运行，或者是否可以从后端生成 | -separated结果文件（例如，共享？）
如果上述所有问题的答案都是肯定的，那么您可以将 SELECT ... 语句转换为 COPY ( SELECT ... ) TO E'path-to-results-file' WITH DELIMITER '|' NULL E'\\N' .
回复于 2024-05-06T00:03:45+08:00
3

另一种方法是使用ODBC . 这假设Python ODBC驱动程序运行良好 .

PostgreSQL具有适用于Windows和Linux的ODBC驱动程序 .

回复于 2024-05-06T00:03:45+08:00
0

作为一个主要使用汇编程序编写程序的人，有一件事情显而易见 . 你在开销上浪费了时间，而且开销是需要的 .

而不是使用python，它将自己包装在与DB周围的C包装器集成的其他东西中......只需用C编写代码 . 我的意思是，需要多长时间？ Postgres并不难与之接口（恰恰相反） . C是一个简单的语言 . 您正在执行的操作看起来非常简单 . 您也可以使用嵌入在C中的SQL，这只是预编译的问题 . 无需翻译您的想法 - 只需将其与C一起编写并使用提供的ECPG编译器（阅读postgres手册第29章iirc） .

尽可能多地取出中间界面内容，切断中间人并与本地数据库进行交谈 . 在我看来，在尝试使系统更简单时，实际上使它变得比它需要的更复杂 . 当事情变得非常混乱时，我常常问自己一个问题：“我最害怕接触的代码是什么？” - 这通常指向我需要改变的东西 .

抱歉唠叨，但也许退后一步，一些新鲜空气将有所帮助;）

回复于 2024-05-06T00:03:45+08:00

将Python的Postgres psycopg2查询性能提高到与Java JDBC驱动程序相同的级别

概述

详情

时间表