Session management with mod_wsgi and webapp2

[Bradley Mclain]

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.

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()

However this doesn't seem to clean up all database connections as viewed in netstat 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.

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.

What is the best practice for usage of a session in a multithreading application that ensures connections are cleaned up?

Thanks,

Brad

[Michael Bayer]

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.

[Bradley Mclain]

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?

[Simon King]

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

[Bradley Mclain]

Not sure exactly how many but it was certainly a lot (100+).

Currently using a multi process / multi thread setup and changed pool_size to 1 and pool_recycle to 60.

Application seems to be working fine with these new settings.

[Michael Bayer]

On Jun 4, 2012, at 1:42 AM, Bradley Mclain wrote:

Not sure exactly how many but it was certainly a lot (100+).

This indicates that multiple Engine instances are in use.  An Engine, by default, will not allow more than 15 connections to be open at once.   

If this was just one multithreaded process, you should make sure it uses one and only one Engine per database URL - basically a global singleton.

If your mod_wsgi configuration is using an Apache forking model for Python processes, then each individual fork can open up to 15 connections each, so in that case you'd want to decrease pooling down to one like you did, but also check your Apache configuration for the max amount of child processes.  I'd recommend using mod_wsgi WSGIDaemonProcess in any case as applying Python interpreters to many forks uses a lot of memory.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/Df91vx1s8HcJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Bradley Mclain]

I have been using WSGIApplicationGroup %{SERVER} and the apache forking model to share the python interpreter. I think this has the same effect as WSGIDaemonProcess.

The machine is dedicated to only serving the one application.

To unsubscribe from this group, send email to sqlalchemy+unsubscribe@googlegroups.com.

[Michael Bayer]

then make sure you use exactly one and only one Engine per application per database server.  15 is the max. If you see more than that from one process, there's more than one Engine (or something else is calling on the DBAPI).

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/T9Snbbkh2ewJ.

To post to this group, send email to sqlal...@googlegroups.com.

To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.

[Bradley Mclain]

My database connection script looks like this:

engine = create_engine(connect_string, echo_pool=config.getboolean('project-name', 'debug'), pool_size=config.getint('sqlalchemy', 'pool_size'), pool_recycle=config.getint('sqlalchemy', 'pool_recycle'))

# Create a scoped session, refer to http://www.sqlalchemy.org/docs/orm/session.html#contextual-thread-local-sessions

Session = scoped_session(sessionmaker(expire_on_commit=False))

Session.configure(bind=engine)

Would this be only creating one engine?

[Michael Bayer]

yeah that's only one.

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/j9VKUYsHP4cJ.

To post to this group, send email to sqlal...@googlegroups.com.

To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.