Persistent DB Connection in Django/WSGI application

Question

I want to keep a persistent connection open to a third party legacy database in a django powered web application.

I want to keep the a connection between the web app and the legacy database open since creating a new connection is very slow for this special DB.

It is not like usual connection pooling since I need to store the connection per web user. User "Foo" needs its own connection between web server and legacy DB.

Up to now I use Apache and wsgi, but I could change if an other solution fits better.

Up to now I use django. Here I could change, too. But the pain would be bigger since there is already a lot of code which needs to be integrated again.

Up to now I use Python. I guess Node.js would fit better here, but the pain to change is too high.

Of course some kind of timeout would be needed. If there is not http-request from user "Foo" for N minutes, then the persistent connection would need to get shut down.

How could this be solved?

Update

I call it DB but it is not a DB which is configured via settings.DATABASES. It is a strange, legacy not wide spread DB-like system I need to integrate.

If I there are 50 people online using the web app in this moment, then I need to have 50 persistent connections. One for each user.

Code to connect to DB

I could execute this line in every request:

strangedb_connection = strangedb.connect(request.user.username) 

But this operation is slow. Using the connection is fast.

Of course the strangedb_connection can't be serialized and can't be stored in a session :-)

Answer 1

worker daemon managing the connection

Your picture currently looks like:

user  ----------->  webserver  <--------[1]-->  3rd party DB  connection [1] is expensive. 

You could solve this with:

user ---->  webserver  <--->  task queue[1]  <--->  worker daemon  <--[2]-> 3rd party DB  [1] task queue can be redis, celery or rabbitmq. [2] worker daemon keeps connection open. 

A worker daemon would do the connection to the 3rd party database and keep the connection open. This would mean that each request would not have to pay the connection costs. The task queue would be the inter-process communication, dispatching work to the daemon and do the queries in the 3rd party db. The webserver should be as light as possible in terms of processing and let workers do expensive tasks.

preloading with apache + modwsgi

You can actually preload and have the expensive connection done before the first request. This is done with the WSGIImportScript configuration directive. I don't remember at the top of my head if having a pre-load + forking configuration means each request will already have the connection opened and share it; but since you have most of the code, this could be an easy experiment.

preloading with uwsgi

uwsgi supports preloading too. This is done with the import directive.