Django Persistent DB connections - A different approach

Question

I have gone similar threads like Django persistent database connection and other stuff on same topic. However Django doesn't officially support persistent connections to MySQL and Mongo(to my limited knowledge).So i tried avoiding a lot of stuff and tried to make it simple.So what i did was in my views.py made global connection variables for both MongoDB and MySQL,something like:

from pymongo import Connection
import MySQLdb
global mongo_connection,mongo_db,collection,mysql_connection,mysql_cursor
mysql_connection = MySQLdb.connect (host = "localhost",
                       user = "root",
                       passwd = "password",
                       db = "demo")
mysql_cursor = mysql_connection.cursor ()
mongo_connection = Connection()
mongo_db = mongo_connection.test_database
collection = mongo_db.test_collection

So after this when the required view is called as per URL requested,i dump the data in the two databases. Like:

mysql_cursor.execute('''INSERT INTO           
table_name(l,n_n,n_id,s_n,s_id,u,r) VALUES
(%s,%s,%s,%s,%s,%s,%s)''',
(l,n_n,n_id,s_name,s_id,u,re)
)

And similarly i did for saving to MongoDB.

Obviously there's this flaw in this approach that i am not closing the connection anywhere.But this approach does seem to work and work well.

Why is this sort of approach not used?

How can i measure the performance improvements i get by using this approach v/s letting Django create a new connection to DB on each call.

Also a batch insert is supposed to make things even better,by reducing calls to DB.How can such a concept be implemented within view definition?

Here is how my application behaved before i had used my method of trying to make a persistent connection and had let Django take care of it

mysql> show status like '%onn%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| Aborted_connects         | 0      |
| Connections              | 164359 |
| Max_used_connections     | 3      |
| Ssl_client_connects      | 0      |
| Ssl_connect_renegotiates | 0      |
| Ssl_finished_connects    | 0      |
| Threads_connected        | 1      |
+--------------------------+--------+
7 rows in set (0.00 sec)

After a few seconds when i ran the same query i got:

mysql> show status like '%onn%';
+--------------------------+--------+  
| Variable_name            | Value  |
+--------------------------+--------+
| Aborted_connects         | 0      |
| Connections              | 175047 |
| Max_used_connections     | 3      |
| Ssl_client_connects      | 0      |
| Ssl_connect_renegotiates | 0      |
| Ssl_finished_connects    | 0      |
| Threads_connected        | 1      |
+--------------------------+--------+
7 rows in set (0.00 sec)

http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Connections states that Connection:The number of connection attempts (successful or not) to the MySQL server.So is it due to some problem in which i handle saving a row using Django ORM for MySQL or is this sort of number of connections expected?

However after using my approach the number of connections didn't increase.

SOLVED

Got confused by reading the definition about Connections.So finally figured it out by doing a test. Inserted 19 records into DB,Connections increased by the same number.So i believe it means the number of times DB has been contacted.So in that case using Django's inbuilt stuff is the best way to go.

http://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html tells that maximum number of connections is 151,so anyways it was a misinterpretation on my part.

Stop reading after third code line with several global vars. — lig, Dec 14 '11 at 12:08

score 2 · Accepted Answer · answered Dec 14 '11 at 10:57

If you want to get into such a technical topic, have a look at the django.db package and review how Django's database backends and QuerySet instances interact with each other before you make any fallacious assumptions, suh as that Django opens a new database connection every time it interacts with a database. You'll see that the database layer performs everything you've mentioned here, without allowing the developer to improperly manage database resources, like obtaining a cursor within a loop and forgetting to close it and creating memory leaks.

So it's not that the approach isn't present, it's that it's managed and abstracted from the work you perform, as it should be, for reasons mentioned above, with the added benefit that you don't need to, say, directly incorporate database management within a view.

Having said that, for unsupported databases, such as MongoDB and any other non-relational database, you'll have to assume that you do not have Django's database layer managing your database resources and how you interact with them and diligently asses what are your obligations as the developer under the conditions of interfacing with their database APIs. When you see obvious patterns emerging you will come up with a solution suitable to be applied within the context of view components.

Filip.Please have a look at the post again ,i have edited it and shown the number of connections made to MySQL when i let Django take care of all the stuff — Nipun Batra, Dec 14 '11 at 11:02
Well, we can discuss the issue of the number of requests created towards the database. I'm sure you would get a varying number of requests between writing the SQL yourself or relying on the ORM. Hopefully though, this does prove to you that you don't need to exert yourself with interfacing with the database directly, for backends Django supports. — Filip Dupanović, Dec 14 '11 at 11:30
In the tests i have done so far,when i write SQL myself,number of connections doesn't increase while with ORM,every reading i save in DB,i have new connection,which amounts to 200 odd connections a second!! — Nipun Batra, Dec 14 '11 at 11:36

Django Persistent DB connections - A different approach

1 Answers1