Django ORM在Postgres DB上留下空闲连接 [英] Django ORM leaves idle connections on Postgres DB
问题描述
最近，由于数据库连接错误，我的Django应用经常崩溃：
Recently, my Django app has been crashing frequently due to database connection errors:
OperationalError: FATAL: sorry, too many clients already
当我进入应用程序数据库时，我发现确实有近100个打开的连接，都具有相同的查询（由Django ORM执行），并且都处于 idle 状态。
When I go into the app database, I see that indeed there are nearly 100 open connections, all with the same query (executed by the Django ORM) and all in the idle state.
从pg_stat_activity中手动执行 SELECT pg_terminate_backend（pid）WHERE state ='idle'; 但我困惑为什么会这样。任何人都可以对这里发生的事情有任何见识吗？
I have been manually doing SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle'; but I am perplexed as to why this is happening. Can anyone shed any insight into what is happening here?
我的Django数据库设置不会偏离默认设置（我尚未定义 CONN_MAX_AGE 或任何类似性质的东西。）
My Django database settings do not stray from the defaults (I have not defined CONN_MAX_AGE or anything of that nature).
这是什么原因造成的？我没有进行任何高级Django查询。这可以通过Django设置或某些PostgreSQL配置解决吗？
What could cause this? I'm not doing any advanced Django queries. Is this something that can be solved with a Django setting or perhaps some PostgreSQL configuration? Any advice is appreciated.
推荐答案
显然您不会断开连接。查询完成后使用 db.close_connection（）会有所帮助。另外，如果我理解正确，将 CONN_MAX_AGE 设置为一些短值可能会有所帮助。并考虑使用某些会话池，例如pgbouncer用于django连接。这样，如果您有太多的连接，它将等待（或重用以前的配置，具体取决于配置），而不是因错误而中止执行...
apparently you don't disconnect. Using db.close_connection() after query finishes would help. Also If I get it right CONN_MAX_AGE to some short value could help. And consider using some session pooler, eg pgbouncer for django connections. This way if you have too many connections it will wait (or reuse previous, depending on config) instead of aborting execution with error...
更新：解释我提出建议的原因
update: explanation why I propose it
每个线程都维护自己的连接，您的数据库必须支持
至少同时具有工作线程数的连接。
each thread maintains its own connection, your database must support at least as many simultaneous connections as you have worker threads.
因此，如果您有更多线程，则postgres max_connections ，您会遇到提到的错误。如果未通过CONN_MAX_AGE，则每个线程都可以重用连接。您的设置为0，因此连接应在查询完成后关闭，但您会看到100个空闲连接。所以他们没有关闭。大量的连接意味着它们也不会被重用（逻辑：如果您有100个并行查询，它们将不会全部处于空闲状态；如果您有那么多，则它们将不会被重用-打开new）。因此，我认为django不会按照建议的方式关闭它们-因此将CONN_MAX_AGE设置为0在您的代码中不起作用。因此，我建议使用 db.close_connection（）强制断开连接，并将CONN_MAX_AGE设置为较小的值可以更改行为。
So if you have more threads then postgres max_connections, you get mentioned error. Each thread can reuse connection if CONN_MAX_AGE has not passed. Your setting is 0, so connection should be closed after query completion, but you see 100 idle connection. So they are not closing. The big number of connection means that they are not reused either (logic: if you would have 100 parallel queries they would not all be idle, and if you have so many, they are not reused - opening new). So I think django does not close them as prommised - so CONN_MAX_AGE set to 0 does not work in your code. So I propose using db.close_connection() to force the disconnect and setting CONN_MAX_AGE to some small value can change behaviour.
这篇关于Django ORM在Postgres DB上留下空闲连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
