Re: [Sqlalchemy-users] sqlite/singletonthreadpool leak + fix ???

Michael Bayer Sat, 05 Aug 2006 07:14:09 -0700

I think your problem is probably the threading model that your server is using (and yeah, I guess FCGI does it this way...). when you use sqlite specifically, it uses an instance of pool called SingletonThreadPool which isnt pooled at all; it just holds on to one connection per thread identifier. if your server is spawning new threads for every request and then disposing of the thread, that will create the behavior you are seeing.

ill add a note to the docs for this.

so what you want in this case, is this patch (which ive also committed in 1764):

===================================================================

--- lib/sqlalchemy/pool.py (revision 1761)

+++ lib/sqlalchemy/pool.py (working copy)

@@ -197,7 +197,13 @@

# sqlite won't even let you close a conn from a thread that didn't create it

pass

del self._conns[key]

+ def dispose_local(self):

+ try:

+ del self._conns[thread.get_ident()]

+ except KeyError:

+ pass

def status(self):

return "SingletonThreadPool id:%d thread:%d size: %d" % (id(self), thread.get_ident(), len(self._conns))

then you can call sqlite.engine.connection_provider._pool.dispose_local() to remove the connection for the local thread which is about to be disposed.

it looks like I might have to give SingletonThreadPool an overhaul to actually maintain a size, since FastCGI is the typical place its going to be used.

On Aug 5, 2006, at 2:11 AM, [EMAIL PROTECTED] wrote:

We're about ready to go to production with our new website (built using pylons, sqlalchemy 0.2.6/sqlite, and kid), so I thought I would point apachebench at it to see how things performed. I was horrified to see memory consumption going through the roof (i.e. >100MB in less than 10,000 requests!).

I did a bunch of profiling, and eventually found that *every* database connection was being leaked. This was confirmed by adding this line to our base controller's __call__ function, which showed the pool's size growing mercilessly (`sqlite` is a reference to the database engine returned by create_engine):

    sqlite.engine.connection_provider._pool.status()

Well, after tearing my hair out on this one, I finally came across the dispose() function on the connection pool. Adding this line to our base controller (after all of the request processing has completed) results in proper functionality, and the pool's size never gets above 10 (even when it's getting pounded by apachebench):

    sqlite.engine.connection_provider._pool.dispose()

Now, we're creating our database engine simply enough:

    sqlite = create_engine(appconfig.get('sqlite.dburi'), strategy='threadlocal')

And I believe our usage of SA is straightforward enough (modelled after zblog's usage, actually):

    query = create_session(bind_to=sqlite).query(MyMappedClass)
    instances = query.select_by(name='blahblah')

We don't hang onto any ORM-ed instances at all -- indeed, if we don't include the pool.dispose() function call after each request, we can cause the connection leak behaviour within a no-op request handler (one that only runs a query using SA -- no templating or other logic), so I can't imagine that we're somehow actively causing the connections to be retained via loaded mapped class instances.

Given this, I certainly didn't expect that calling the pool.dispose() function would be necessary for the sqlite connections to be cleaned up properly -- indeed, this is either a bug, or we're doing something very wrong.

Thoughts? is the pool.dispose() call at the end of each request a reasonable stopgap, or will things break in unexpected ways if that function is used? Further, does anyone have any suggestions on what we might be doing wrong that our connection pool is growing like a weed?

Thanks!

Charlie
[EMAIL PROTECTED]

Do you Yahoo!?
Next-gen email? Have it all with the all-new Yahoo! Mail Beta.
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users

-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV

_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users

Re: [Sqlalchemy-users] sqlite/singletonthreadpool leak + fix ???

Reply via email to