On Mon, May 28, 2012 at 12:58 AM, Bradley Mclain <bradley.james.mcl...@gmail.com> wrote: > On Saturday, May 26, 2012 9:31:55 AM UTC+10, Michael Bayer wrote: >> >> >> On May 24, 2012, at 11:28 PM, Bradley Mclain wrote: >> >> Hi, >> >> Currently working on a project that use SQL alchemy with mod_wsgi, >> webapp2 and prestans (python REST framework). The backend is Oracle XE >> using cx_Oracle. >> >> What is the appropriate way to manage sessions in a multithreading app >> such as this? Currently I have been following the guide found here to >> implement a contextual/thread-local session. >> >> >> scoped_session produces a session factory/proxy object which maintains a >> single Session per thread, and as the guide indicates you can allow this >> factory to automatically create a Session on first use, then at request end >> time you do an unconditional remove() so that the scoped_session closes the >> Session it may or may not be handling, and disposes of it. >> >> >> >> I have then made a session that is automatically cleaned up by the request >> handler. The code for this looks something like: >> >> def dispatch(self): >> self.db_session = db.Session() >> super(BaseHandler, self).dispatch() >> self.db_session.close() >> db.Session.remove() >> >> >> db.Session.remove() should be enough here, it calls close() for you. >> >> >> However this doesn't seem to clean up all database connections as viewed >> in netstat >> >> >> >> The Session uses an Engine as a source of connections. The Engine is >> introduced at http://docs.sqlalchemy.org/en/rel_0_7/core/engines.html . >> Intrinsic to its behavior is that it uses a small connection pool which by >> default holds onto five connections out of the total accumulated. Full >> information on how this pooling is configured or disabled is >> at http://docs.sqlalchemy.org/en/rel_0_7/core/pooling.html. >> >> and I get a connections exhausted error from Oracle XE after a certain >> time of using the application. The same thing also happens when I use MySQL >> as well. >> >> >> one of the many options here is the "pool_recycle" option which places a >> time limit on the age of connections. >> >> >> >> If I add a call to db.engine.dispose() after the handler has returned this >> appears to fix the problem but the SQL Alchemy documentation indicates that >> this is only for usage with dropped connections or in test suites. >> >> >> I'm glad the documentation made clear that dispose() is not generally >> appropriate, as the Engine is a factory/registry for connections, not a >> connection itself. >> >> > Thanks for confirming my apprehensions about using engine.dispose(). > > So I gather it is just a matter of me tweaking the pool_recycle and > pool_size options to something more appropriate for my application? > > Can you offer any guidance as to some appropriate values for a threaded REST > application? Would pool_size be able to be quite small like 2 and > pool_recycle something like 2 mins? > >
How many connections are alive when you get the "connections exhausted" error? Is it more than "pool_size + max_overflow" (default 15)? Also, are you using multiple processes with mod_wsgi? If so, remember that each process will have its own pool, so if you had 2 processes, you could have up to 30 connections open. Simon -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
