IDLE vs checked in connection

[sumau]

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.

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

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

I was wondering if:

1. There was a reason for this difference in behaviour? Should a checked-in connection show up as 'IDLE'?
2. It is recommended to disable the pool when using autocommit? 

Thanks !

Soumaya

[sumau]

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.

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

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().execution_options(isolation_level="AUTOCOMMIT")

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

[Mike Bayer]

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.

2. 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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/1f6b6767-4d38-4de9-903f-28e49e92a8b8n%40googlegroups.com.

[Mike Bayer]

On Sat, May 8, 2021, at 11:05 AM, Mike Bayer wrote:

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.

to clarify, yes it *is* expected that checked in connections are IDLE in all cases.  it is *not* expected that you are only getting one row back when you have a pool with two connections.

[Soumaya Mauthoor]

Hello Mike

Thanks for quick response as always :-) So I understand setting AUTOCOMMIT has no impact on the number of 'IDLE' connections and will look for different culprit.

About the single row in pg_stat_activity, I was actually running the query below, but sent you the shortened version because I didn't think it would make a difference.

engine = create_engine("postgresql://*@localhost:5432/postgres")

print("create engine")
print(engine.pool.status())

conn1 = engine.connect()
print("create conn1")

print(conn1.execute("select state from pg_stat_activity where usename='souma'").fetchall())
print(engine.pool.status())

conn2 = engine.connect()
print("create conn2")
print(conn2.execute("select state from pg_stat_activity where usename='souma'").fetchall())
print(engine.pool.status())

conn2.close()
print("close conn2")

print(conn1.execute("select state from pg_stat_activity where usename='souma'").fetchall())
print(engine.pool.status())

I have run this query multiple times and get a single row every time in the last query against pg_stat_activity. However, if I close conn1 instead of conn2, I do get two rows [('idle in transaction',), ('active',)] in the last query against pg_stat_activity. I also noticed that if I only use conn1 to query pg_stat_activity then I get at most one row:

engine = create_engine("postgresql://souma:ppp@localhost:5432/postgres")

print("create engine")
print(engine.pool.status())

conn1 = engine.connect()
print("create conn1")

print(conn1.execute("select state from pg_stat_activity where usename='souma'").fetchall())
print(engine.pool.status())

conn2 = engine.connect()
print("create conn2")

print(conn1.execute("select state from pg_stat_activity where usename='souma'").fetchall())
print(engine.pool.status())

conn2.close()
print("close conn2")

print(conn1.execute("select state from pg_stat_activity where usename='souma'").fetchall())
print(engine.pool.status())

create engine
Pool size: 5  Connections in pool: 0 Current Overflow: -5 Current Checked out connections: 0
create conn1
[('active',)]
Pool size: 5  Connections in pool: 0 Current Overflow: -4 Current Checked out connections: 1
create conn2
[('active',)]
Pool size: 5  Connections in pool: 0 Current Overflow: -3 Current Checked out connections: 2
close conn2

[('active',)]
Pool size: 5  Connections in pool: 1 Current Overflow: -3 Current Checked out connections: 1

I'm guessing this is due to some difference about how "open" TCP/IP connections are treated by postgres/psycopg2? 

Regards

Soumaya

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f47be475-b873-4dcb-959d-4b4f8d9a04e5%40www.fastmail.com.

[Mike Bayer]

Postgresql session isolation is playing tricks on you. 

go to psql and do the query from your commandline, and you will see all connections listed.

in your program, you can get it to "see" connections that were made after the scope of the current-checked-out-connection started like this:

from sqlalchemy import create_engine

engine = create_engine("postgresql://scott:tiger@localhost/test")

print("create engine")

print(engine.pool.status())

conn1 = engine.connect()

print("create conn1")

print(conn1.execute("rollback; select state from pg_stat_activity where usename='scott'").fetchall())

print(engine.pool.status())

conn2 = engine.connect()

print("create conn2")

print(conn2.execute("rollback; select state from pg_stat_activity where usename='scott'").fetchall())

print(engine.pool.status())

conn2.close()

print("close conn2")

print(conn1.execute("rollback; select state from pg_stat_activity where usename='scott'").fetchall())

print(engine.pool.status())

then you will see all the connections:

$ python test3.py

create engine

Pool size: 5  Connections in pool: 0 Current Overflow: -5 Current Checked out connections: 0

create conn1

[('active',)]

Pool size: 5  Connections in pool: 0 Current Overflow: -4 Current Checked out connections: 1

create conn2

[('idle',), ('active',)]

Pool size: 5  Connections in pool: 0 Current Overflow: -3 Current Checked out connections: 2

close conn2

[('active',), ('idle',)]

Pool size: 5  Connections in pool: 1 Current Overflow: -3 Current Checked out connections: 1

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAN14jWT02rwRS5nPDe45RYXv4BBWZHLczXD0DsHJ0u8V9xSm5Q%40mail.gmail.com.