如何让Python/PostgreSQL更快？

Question

如何让Python/PostgreSQL更快？

7

目前我有一个日志解析器正在读取515MB的纯文本文件（过去四年中每天一个文件）。我的代码目前是这样的：http://gist.github.com/12978。我使用了Psyco（如代码所示），并编译并使用编译版本。它每0.3秒处理约100行。这台机器是标准的15英寸MacBook Pro（2.4ghz C2D，2GB RAM）。

这个速度是否可以更快，还是这是语言/数据库的限制？

- Radar

5个回答

4

在for循环中，您正在重复插入“chats”表，因此您只需要一个带有绑定变量的单个SQL语句，以使用不同的值执行。因此，您可以将此放在for循环之前：

insert_statement="""
    INSERT INTO chats(person_id, message_type, created_at, channel)
    VALUES(:person_id,:message_type,:created_at,:channel)
"""

然后，替换每个执行的SQL语句，将其替换为以下内容：

cursor.execute(insert_statement, person_id='person',message_type='msg',created_at=some_date, channel=3)

这将使事情更快，因为：

游标对象不必每次重新解析语句
数据库服务器不必生成新的执行计划，因为它可以使用先前创建的计划。
您无需调用sanitize()，因为绑定变量中的特殊字符不会成为执行的SQL语句的一部分。

注意：我使用的绑定变量语法是Oracle特定的。您需要查看psycopg2库的文档以获取确切的语法。

其他优化：

在每个循环迭代后，您正在使用“UPDATE people SET chatscount”进行递增。保留一个将用户映射到chat_count的字典，然后执行您已经查看的总数的语句。这比每个记录后都要访问数据库更快。
在所有查询中使用绑定变量。不仅仅是插入语句，我选择它作为示例。
更改所有查找_*（）函数以缓存其结果，以便它们不必每次都访问数据库。
Psycho优化执行大量数字操作的Python程序。该脚本的IO开销很大，而不是CPU开销，因此我不指望能够为您提供任何优化。

- Mark Roddy

以上代码无法运行，它给了我一个 TypeError: 'd' is an invalid keyword argument for this function。显然，这个语法对于 psycopg2 来说是无效的，而我明确声明我正在运行一个 PostgreSQL 数据库。 - Ryan Bigg

cursor.execute() 方法在不同的 db api 实现中需要略微不同的参数。例如，psycopg2 实现使用字典中的绑定变量。例如： - Mark Roddy

bind_vars={'person_id:'person','message_type':'msg','created_at':some_date,'channel':3) cursor.execute(sql_statement,bindvars) - Mark Roddy

3

在sql语句中使用绑定变量而不是字面值，并为每个唯一的sql语句创建一个游标，这样下次使用该语句时就无需重新解析。从Python DB API文档中可以得知：

“准备并执行数据库操作（查询或命令）。参数可以作为序列或映射提供，并将绑定到操作中的变量。变量以特定于数据库的符号表示（有关详细信息，请参见模块的paramstyle属性）。游标将保留对操作的引用。如果再次传递相同的操作对象，则游标可以优化其行为。这对于使用相同操作但多次绑定不同参数的算法最有效。”

“始终始终始终使用绑定变量。”

- Mark Roddy

2

如Mark建议的那样，使用绑定变量。数据库只需准备一次每个语句，然后为每次执行“填写空白”。作为一个不错的副作用，它会自动处理字符串引用问题（你的程序没有处理）。

打开事务（如果尚未打开），并在程序结束时进行单个提交。在需要提交所有数据之前，数据库不必将任何内容写入磁盘。如果您的程序遇到错误，则不会提交任何行，允许您在问题被纠正后简单地重新运行程序。

您的log_hostname、log_person和log_date函数正在表格上进行无用的SELECT操作。将适当的表属性设置为主键或唯一。然后，在插入之前不要检查键是否存在，只需插入即可。如果人/日期/主机名已经存在，则由于违反约束而插入失败。（如果使用如上所述的单个提交事务，则此方法将不起作用。）

或者，如果您知道在程序运行时只有您一个人在插入表中，请在内存中创建并维护平行数据结构，同时进行插入。例如，在程序开始时从表中读取所有主机名，并将其读入关联数组中。当想知道是否进行插入时，只需进行数组查找。如果没有找到条目，请进行插入并适当更新数组。（此建议与事务和单个提交兼容，但需要更多的编程。然而它将会快速得多。）

- Barry Brown

1

除了@Mark Roddy提出的许多好建议之外，还要做以下操作：

不要使用readlines，可以迭代文件对象
尝试使用executemany而不是execute：尝试进行批量插入而不是单个插入，这通常更快，因为开销较小。它还减少了提交的次数
str.rstrip将完全可以代替使用正则表达式去掉换行符

批处理插入将在暂时使用更多内存，但当您不将整个文件读入内存时，这应该是可以接受的。

- Torsten Marek

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- S.Lott · Accepted Answer

不要浪费时间进行性能分析。时间总是花在数据库操作上。尽量少做，只做最少量的插入。

三个建议:

一、不要反复选择日期、主机名和人员维度来确认数据。将所有数据一次性获取到Python字典中，在内存中使用它。不要进行重复的单例选择。使用Python。

二、不要更新。

具体来说，不要这样做。这段代码有两个问题。

cursor.execute("UPDATE people SET chats_count = chats_count + 1 WHERE id = '%s'" % person_id)

应该用一个简单的SELECT COUNT(*) FROM ...来替换它。永远不要更新以增加计数。只需使用SELECT语句计算存在的行数。[如果您无法使用简单的SELECT COUNT或SELECT COUNT(DISTINCT)实现此目的，则缺少某些数据--您的数据模型应始终提供正确的完整计数。永远不要更新。]

另外，永远不要使用字符串替换构建SQL。完全是愚蠢的。

如果由于某种原因SELECT COUNT(*)不够快（在执行任何糟糕操作之前，请先进行基准测试），可以将计数结果缓存到另一个表中。在所有加载之后执行SELECT COUNT(*) FROM whatever GROUP BY whatever，并将其插入计数表中。永远不要更新。

三，始终使用绑定变量。

cursor.execute( "INSERT INTO ... VALUES( %(x)s, %(y)s, %(z)s )", {'x':person_id, 'y':time_to_string(time), 'z':channel,} )

SQL语句永远不会改变。绑定的值可能会变，但SQL语句本身不会改变。这种方式速度要快得多。永远不要动态构建SQL语句，绝对不要。