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 enginePool size: 5 Connections in pool: 0 Current Overflow: -5
Current Checked out connections: 0create conn1[('active',)]Pool size: 5
Connections in pool: 0 Current Overflow: -4 Current Checked out
connections: 1create conn2[('active',)]Pool size: 5 Connections in pool: 0
Current Overflow: -3 Current Checked out connections: 2close
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
Le sam. 8 mai 2021 à 16:05, Mike Bayer <mike...@zzzcomputing.com> a écrit :
>
>
> 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
> <https://groups.google.com/d/msgid/sqlalchemy/f47be475-b873-4dcb-959d-4b4f8d9a04e5%40www.fastmail.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/CAN14jWT02rwRS5nPDe45RYXv4BBWZHLczXD0DsHJ0u8V9xSm5Q%40mail.gmail.com.
