psycopg2在执行大型查询后存在内存泄漏。

Question

我在使用psycopg2（我已经升级到版本2.5）的python脚本中对我的PostgreSQL数据库运行一个大查询。查询结束后，我关闭了游标和连接，甚至运行了gc，但该进程仍然消耗了大量内存（确切地说是7.3GB）。我是否漏掉了清理步骤？

import psycopg2
conn = psycopg2.connect("dbname='dbname' user='user' host='host'")
cursor = conn.cursor()
cursor.execute("""large query""")
rows = cursor.fetchall()
del rows
cursor.close()
conn.close()
import gc
gc.collect()

- Adam Berlinsky-Schine

Answer 1

我遇到了类似的问题，经过几个小时的努力和挫折，我发现答案只需要添加一个参数。

而不是

cursor = conn.cursor()

写

cursor = conn.cursor(name="my_cursor_name")

或者更简单一些。

cursor = conn.cursor("my_cursor_name")

详细信息请参见http://initd.org/psycopg/docs/usage.html#server-side-cursors 我感到有些困惑，因为我以为需要重写我的SQL语句，包括"DECLARE my_cursor_name ...."和"FETCH count 2000 FROM my_cursor_name"，但事实证明，如果您在创建游标时简单地覆盖默认参数"name=None"，psycopg会在底层为您完成所有操作。

上面建议使用fetchone或fetchmany并不能解决问题，因为如果未设置名称参数，psycopg默认尝试将整个查询加载到内存中。除了声明名称参数外，您可能还需要更改cursor.itersize属性，从默认值2000更改为例如1000，如果仍然内存不足的话。

- joeblog

---

我在 sqlalchemy 中找不到任何有助于避免OOM问题的东西，但这个解决方案对我很有效。谢谢！ - ryantuck

@RyanTuck 通过在 create_engine 中传递 server_sider_cursors=True，似乎可以在 sqlalchemy 中实现此功能：http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html?highlight=server_side_cursors#psycopg2-connect-arguments - FoxMulder900

@joeblog，您能否详细解释一下您上面的答案？在创建游标之前，您是否声明变量并给它赋值（例如my_cursor_name = 1000）？我想采用您的解决方案，在调用cursor.execute('query')之前从我的Python脚本中设置FETCH_COUNT。 - arilwan

@FoxMulder900 为了记录，看起来 server_side_cursors 已经被 stream_results 取代了。 - Joril

服务器端游标对我有用。谢谢！！！它运行得非常好，而不是使用fetchall并陷入分段错误。 - Nikhil S

Answer 2

Joeblog给出了正确的答案。处理获取数据的方式很重要，但比定义游标的方式更加明显。这里有一个简单的示例来说明这一点，并为您提供可复制粘贴的内容。

import datetime as dt
import psycopg2
import sys
import time

conPG = psycopg2.connect("dbname='myDearDB'")
curPG = conPG.cursor('testCursor')
curPG.itersize = 100000 # Rows fetched at one time from the server

curPG.execute("SELECT * FROM myBigTable LIMIT 10000000")
# Warning: curPG.rowcount == -1 ALWAYS !!
cptLigne = 0
for rec in curPG:
   cptLigne += 1
   if cptLigne % 10000 == 0:
      print('.', end='')
      sys.stdout.flush() # To see the progression
conPG.commit() # Also close the cursor
conPG.close()

正如你所看到的，点会快速地分组，然后暂停以获得一定数量的行（itersize）的缓冲区，因此你不需要使用fetchmany来提高性能。当我使用/usr/bin/time -v运行时，对于1000万行，我只用了不到3分钟的时间，并且仅使用了200MB的RAM（而客户端游标需要60GB）。由于使用了临时表，服务器不需要更多的内存。

- Le Droid

---

一个选择查询的 commit()？ - Amit Naidu

@AmitNaidu 是的，在关闭PG连接之前，如果您有很长的暂停时间，关闭事务是一个好习惯，以免出现可怕的环绕警报。 - Le Droid

Answer 3

请参见@joeblog的下一个答案，以获取更好的解决方案。

---

首先，您不应该在第一次使用时需要所有那么多的RAM。你应该在这里获取结果集的块而不是执行fetchall()。而是使用更高效的cursor.fetchmany方法。请参见psycopg2文档。

现在，解释为什么它没有被释放以及为什么在该术语的正式正确使用中它不是内存泄漏。

大多数进程在释放内存时不会将其返回到操作系统，它们只会使其在程序中的其他位置可用于重复使用。

只有当程序可以紧凑地排列分散在内存中的剩余对象时，才能将内存释放给操作系统。如果不使用间接句柄引用，则移动对象将使现有指向对象的指针无效。间接引用相当低效，特别是在现代CPU上追踪指针会对性能产生可怕的影响。

通常情况下，除非程序特别小心，否则每个使用brk()分配的大块内存都会包含一些仍在使用的小块。

操作系统无法确定程序是否仍然使用此内存，因此无法将其取回。由于程序不 tend 到访问内存，因此操作系统通常会随时间逐渐将其交换出去，为其他用途释放物理内存。这是您应该拥有交换空间的原因之一。

可以编写向操作系统返还内存的程序，但我不确定您是否可以使用Python实现。

另请参见：

• python - memory not being given back to kernel
• 为什么在django中进行大量查询（或一系列查询）后内存不会释放给系统？
• 在Python中释放内存

所以：这实际上并不是内存泄漏。如果你做其他使用大量内存的事情，进程应该不会增长太多，它将重用上次大量分配所释放的内存。

- Craig Ringer

---

谢谢！通过在同一进程中两次运行上述代码来确认内存被重复使用。第二次运行期间内存没有增加。 - Adam Berlinsky-Schine

虽然这里提到的一切都是正确的，但通常查询结果会完全在客户端传输（不是通过 fetch*() 而是通过 execute()）。因此，虽然使用 fetchmany() 而不是 fetchall() 可以节省一些 Python 对象创建的内存，但像 @joeblog 建议的使用服务器端游标才是正确的解决方案。 - piro