On Thu, Oct 25, 2018 at 7:50 AM Sebastian Meinhardt <seb.meinha...@gmail.com> wrote: > > Our database environment drops idle connections after an hour. My application > uses the SQLAlchemy connection pool to deal with this. The problem is: When a > connection is closed, it takes the pool a real long time (15-20 minutes) to > recover. This happens with both strategies, pool_pre_ping and pool_recylce. > Apparently, the pool hangs when trying to operate on the already dead > connections.
I would classify this as an issue with cx_Oracle. Can we post an issue over at https://github.com/oracle/python-cx_Oracle/issues and ask for mitigation strategies? In particular, the .close() method taking 15 minutes is entirely not acceptable. Also, the strategy by which how the database environment is dropping these connections is likely very important, is the Oracle database itself doing so, or is there a proxy layer of some kind. Unfortunately on my end I don't have any good answers, pool_recycle is the more effective system but if even .close() is causing a problem, this implies you'd need to build a background thread that is actively closing out connections from the pool long before they are allowed to sit in the pool for too long. that would definitely solve the problem in the absense of any configurational / environmental reasons that .close() itself can't complete quickly. > > Here is an excerpt from the logs. Look at the timestamps! > > With pool_pre_ping: > > 2018-10-25 12:30:38,866:DEBUG:Connection <cx_Oracle.Connection to > USER@DATABASE> checked out from pool > 2018-10-25 12:30:38,868:DEBUG:Pool pre-ping on connection > <cx_Oracle.Connection to USER@DATABASE> > 2018-10-25 12:46:26,624:DEBUG:Pool pre-ping on connection > <cx_Oracle.Connection to USER@DATABASE> failed, will invalidate pool > 2018-10-25 12:46:26,626:INFO:Disconnection detected on checkout, invalidating > all pooled connections prior to current timestamp (reason: > InvalidatePoolError()) > > > With pool_recycle: > > 2018-10-25 12:40:51,869:INFO:Connection <cx_Oracle.Connection to > USER@DATABASE> exceeded timeout; recycling > 2018-10-25 12:40:51,871:DEBUG:Closing connection <cx_Oracle.Connection to > USER@DATABASE> > 2018-10-25 12:56:39,493:ERROR:Exception closing connection > <cx_Oracle.Connection to USER@DATABASE> > Traceback (most recent call last): > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool.py", line 314, > in _close_connection > self._dialect.do_close(connection) > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", > line 465, in do_close > dbapi_connection.close() > cx_Oracle.OperationalError: ORA-03113: end-of-file on communication channel > Process ID: 0 > Session ID: 540 Serial number: 46277 > 2018-10-25 12:56:39,674:DEBUG:Created new connection <cx_Oracle.Connection to > USER@DATABASE> > > > As this is an interactive application, I cannot let the user wait for 16 > minutes. While I could implement an application side timeout that creates a > new pool every 59 minutes, it feels like that would be missing the point. Is > there an SQLAlchemy solution to my problem? Decrease the timeout on > ping/close? Recycle connections in a background threads? Am I doing something > fundamentally wrong here? > > -- > 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 post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
