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 sqlalchemy+unsubscr...@googlegroups.com. > 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 sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f47be475-b873-4dcb-959d-4b4f8d9a04e5%40www.fastmail.com.