Thread: [SQLObject] Severe Database Connection Leak (SQLObject, ConnectionHubs)
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Chas E. <cem...@sn...> - 2006-04-11 17:40:09
|
Hi all. We're moving into heavy testing for our new website and a few accompanying apps (all based on pylons/sqlobject/kid), but we've run into a serious problem with leaking database connections. Briefly put, if we use a sqlobject.postgres.pgconnection.PostgresConnection as the _connection attribute on our SQLObject subclasses, then all is well. However, using any kind of ConnectionHub (ConnectionHub, AutoConnectHub, or PackageHub) yields behaviour where a database connection is leaked per request. This is based on watching the results of 'select count(1) from pg_stat_activity;', and then inevitably getting an error when we max out the database connection limit. Specifically, we've been successful with this line (<3 connections ever show as being open in pg_stat_activity, even under load): postgres = pgconnection.PostgresConnection.connectionFromURI('postgres://web@localhost/web') but either of these lines cause major leakage: # postgres = PackageHub('postgresql') # postgres = AutoConnectHub('postgres://web@localhost/web') For completeness, here's one of our SQLObject classes: class User (SQLObject): _connection = postgres class sqlmeta: table='web_user' username=StringCol(length=64, alternateID=True) [blah blah] I spent a few hours digging around on the web, looking at the source code for the connection hubs, sqlobject.dbconnection.DBAPI, and not seeing anything glaringly obvious. Further investigation turned up the wiki page related to SQLObject connection issues (http://wiki.sqlobject.org/connections.html), but suggestions and links from that weren't effective. There's tons of examples out there that use PackageHub, and we would most definitely like to use its cleaner transaction support. Suggestions? If this can't be worked out before our release, is there any reason why using a PostgresConnection instance as our class' _connection attribute would be inadvisable? Thanks, - Chas |
From: Ian B. <ia...@co...> - 2006-04-13 21:53:25
|
Chas Emerick wrote: > Hi all. We're moving into heavy testing for our new website and a few > accompanying apps (all based on pylons/sqlobject/kid), but we've run > into a serious problem with leaking database connections. > > Briefly put, if we use a > sqlobject.postgres.pgconnection.PostgresConnection as the _connection > attribute on our SQLObject subclasses, then all is well. However, > using any kind of ConnectionHub (ConnectionHub, AutoConnectHub, or > PackageHub) yields behaviour where a database connection is leaked per > request. This is based on watching the results of 'select count(1) > from pg_stat_activity;', and then inevitably getting an error when we > max out the database connection limit. > Specifically, we've been successful with this line (<3 connections ever > show as being open in pg_stat_activity, even under load): > > postgres = > pgconnection.PostgresConnection.connectionFromURI('postgres://web@localhost/web') > > but either of these lines cause major leakage: > > # postgres = PackageHub('postgresql') > # postgres = AutoConnectHub('postgres://web@localhost/web') I'm not familiar with AutoConnectHub? But the key part is how you connect and disconnect your connection from the hub. I usually do it like: try: hub.threadConnection = connectionFromURI(...) do stuff... finally: hub.threadConnection = None Additionally in the trunk there's http://svn.colorstudy.com/SQLObject/trunk/sqlobject/wsgi_middleware.py which should handle this logic in a WSGI context. > For completeness, here's one of our SQLObject classes: > > class User (SQLObject): > _connection = postgres > > class sqlmeta: > table='web_user' > > username=StringCol(length=64, alternateID=True) > [blah blah] > > I spent a few hours digging around on the web, looking at the source > code for the connection hubs, sqlobject.dbconnection.DBAPI, and not > seeing anything glaringly obvious. Further investigation turned up the > wiki page related to SQLObject connection issues > (http://wiki.sqlobject.org/connections.html), but suggestions and links > from that weren't effective. > > There's tons of examples out there that use PackageHub, and we would > most definitely like to use its cleaner transaction support. > Suggestions? > > If this can't be worked out before our release, is there any reason why > using a PostgresConnection instance as our class' _connection attribute > would be inadvisable? As long as you are only accessing one database in a process, that's fine. -- Ian Bicking / ia...@co... / http://blog.ianbicking.org |
From: Chas E. <cem...@sn...> - 2006-04-14 14:09:29
|
Ian Bicking <ianb <at> colorstudy.com> writes: > > Chas Emerick wrote: > > [snip] > > > Specifically, we've been successful with this line (<3 connections ever > > show as being open in pg_stat_activity, even under load): > > > > postgres = > > pgconnection.PostgresConnection.connectionFromURI('postgres://web <at> localhost/web') > > > > but either of these lines cause major leakage: > > > > # postgres = PackageHub('postgresql') > > # postgres = AutoConnectHub('postgres://web <at> localhost/web') > > I'm not familiar with AutoConnectHub? But the key part is how you > connect and disconnect your connection from the hub. I usually do it like: > > try: > hub.threadConnection = connectionFromURI(...) > do stuff... > finally: > hub.threadConnection = None AutoConnectHub is a pylons subclass of ConnectionHub. My understanding is that it does roughly what you outlined above. I've also tried using a regular ConnectionHub and then using connectionFromURI explicitly (again, as you indicate), but the results are the same -- connections are never released, and continue to be created until the database server puts a stop to it. > Additionally in the trunk there's > http://svn.colorstudy.com/SQLObject/trunk/sqlobject/wsgi_middleware.py > which should handle this logic in a WSGI context. Thanks, I'll take a look at that. This is all under pylons though, so I suspect its PackageHub and AutoConnectHub classes do something similar. - Chas |