On Sat, May 8, 2021, at 10:34 AM, sumau wrote:
> Hello
>
> Our postgreSQL database is suffering from too many IDLE connections and I'm
> wondering if this has to do with isolation level which we usually set to
> AUTOCOMMIT.
that's not really possible as AUTOCOMMIT "isolation" merely means that the
psycopg2 driver will not emit BEGIN TRANSACTION on a connection before SQL is
emitted. an idle connection typically means the pool has this connection ready
for use; when AUTOCOMMIT is on in-play connections, they likely will also
appear as IDLE if they are not invoking a statement at that moment. so the
number of IDLE connections should never exceed pool_size + max_overflow for a
single process (and engine); multiply that by the number of processes you have
(and by engines, if you have more than one engine per process).
>
> When I use the default connection settings then checked-in connections do not
> appear as IDLE in postgres:
>
> engine = create_engine("postgresql://*@localhost:5432/postgres")
> conn1 = engine.connect()
> conn2 = engine.connect()
> conn2.close()
> print(conn1.execute("select state from pg_stat_activity where
> usename='souma'").fetchall())
> print(engine.pool.status())
>
> *[('active',)]*
> *Pool size: 5 Connections in pool: 1 Current Overflow: -3 Current Checked
> out connections: 1*
that response does not make any sense because you have two connections in play,
there should be two rows in pg_stat_activity, so make sure you are selecting
the correct criteria from pg_stat_activity and that you are not using something
like DISTINCT.
if I run your program as given the output is what's expected:
[('active',), ('idle',)]
Pool size: 5 Connections in pool: 1 Current Overflow: -3 Current Checked out
connections: 1
>
> However if I set the Isolation level to AUTOCOMMIT then the checked-in
> connection appears as IDLE in postgres:
>
> engine = create_engine("postgresql://*@localhost:5432/postgres")
> conn1 = engine.connect()
> conn2 = engine.connect()
> conn2.close()
> print(conn1.execute("select state from pg_stat_activity where
> usename='souma'").fetchall())
> print(engine.pool.status())
>
> *[('active','idle')]*
> *Pool size: 5 Connections in pool: 1 Current Overflow: -3 Current Checked
> out connections: 1*
That's the result we'd expect in your earlier case as well.
> **
> I was wondering if:
> 1. There was a reason for this difference in behaviour? Should a checked-in
> connection show up as 'IDLE'?
I would check your methodology of measurement because that's not the expected
result and it does not reproduce here.
> 1. It is recommended to disable the pool when using autocommit?
absolutely not.
> Thanks !
> Soumaya
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/1f6b6767-4d38-4de9-903f-28e49e92a8b8n%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/1f6b6767-4d38-4de9-903f-28e49e92a8b8n%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/f47be475-b873-4dcb-959d-4b4f8d9a04e5%40www.fastmail.com.
