Warning: Can't synchronize with repository "(default)" (Unsupported version control system "svn": No module named svn). Look in the Trac log for more information.

Ticket #565 (closed defect: invalid)

Opened 13 years ago

Last modified 12 years ago

Using models and turbogears AutoConnectHub with threads

Reported by: jpaulofarias at gmail dot com Owned by: anonymous
Priority: high Milestone: 0.9
Component: TurboGears Version:
Severity: critical Keywords:
Cc:

Description

Hi!

I've noticed that using AutoConnectHub? on a threaded environment will lead to one connection per thread being created, and they never get freed.

If the user starts 20 threads, then wait they finish and then start new 20 threads, it will have 40 connections on the end, all open and idle.

I've tested this with mysql 5.0 and turbogears from svn.

In other words, no connection pool is being used.

Change History

comment:1 Changed 13 years ago by jpaulofarias at gmail dot com

Sample project for testing this bug:

 http://www.info.ufrn.br/~jpfarias/conntest.zip

Hope it helps finding out how to work aroung this.

Loading the index page for this project will create 10 threads each creating one instance of a class TestClass? wich is mapped on model.py

I'm using MySql, but it will probably be the same for other dbs.

comment:2 Changed 13 years ago by kevin

This behavior is actually on purpose (though that doesn't make it good). sqlite, in particular, has an issue with more than one thread accessing the same connection object. sqlite 3.3+ removes this limitation. Further, I believe that SQLObject now ensures that the connection is not shared between threads (at least for sqlite). I'll do a bit more looking into this to see what can be done at this stage.

comment:3 Changed 13 years ago by kevin

  • Status changed from new to closed
  • Resolution set to worksforme

OK, I've taken another look at the code. The sqlite hack I was referring to was only being applied to sqlite. The latest version of SQLObject fixes the problem with sqlite, so I'm removing TG's hack for this.

There is, in fact, pooling going on. Here's how it works:

  • when a DB connection is needed, it tries to pull one from the pool
  • if there isn't one, it creates a new connection to the DB
  • as soon as it's done with the connection, it returns it to the pool

Connections in the pool are never closed. So, when you start up the server there are no connections open to the database. The number of connections that you'll have to the database will increase until you have one per thread (if you're using all of your threads). I just tested this, and this is in fact how it worked.

The description seems to imply that the number of DB connections just keeps going up and up. I just tested with MySQL on my machine, running repeated concurrent connections with ab and it was clearly reusing the same database connections. It's possible that there was some problem earlier, but as of today it's fine for me.

comment:4 Changed 13 years ago by jpaulofarias at gmail dot com

  • Status changed from closed to reopened
  • Resolution worksforme deleted

The problem is still there!

Just try out my conntest.zip project I've posted above.

Load mysql-admin and reload  http://localhost:8080 many times. Watch the connections growing up and up.

comment:5 Changed 13 years ago by kevin

  • Status changed from reopened to closed
  • Resolution set to invalid

Actually, I hadn't tried conntest. What I did was:

1) set server.thread_pool=10 2) ab -c 5 -n 20  http://localhost:8080/FrontPage (on a wiki 20) 3) mysql-admin processlist

Doing this and repeating #2-3 as desired, the mysql connection count stuck at 5, showing a definite use of a connection pool.

I just looked at conntest. It's not a normal use case. The common use case is that TurboGears creates the threads for you and manages that database connections/transactions. In this case, you created your own threads.

The reason those connections were hanging around is that TG provides implicit transactions in 0.9. So, the transactions were being created but never closed. I added one line to conntest's controllers.py and all was well:

def simpleThreadTest():
    obj = TestClass(test='creating an object')
    time.sleep(1)
    hub.end()
Note: See TracTickets for help on using tickets.